[Freeswitch-users] Spidermonkey ODBC

Michael Jerris mike at jerris.com
Wed Feb 20 07:54:30 PST 2008


This check would have to be specific to each odbc connection, not  
global to the switch, as you could be using different types of  
databases for different connections.  I still lean towards a better  
way of auto-detecting (probably in addition to a configurable option)  
if it is at all possible.


Mike

On Feb 20, 2008, at 9:57 AM, Jonas Gauffin wrote:

> Ok. I've done a patch and will upload it if the following config is  
> ok by Anthony.
>
> (Either create odbc.conf.xml in autoload_configs or add it directly  
> to freeswitch.xml)
>
>   <section name="odbc" description="odbc general options">
>     <connection_check>SELECT 1</connection_check>
>   </section>
>
> My patch will load that string when a new connection is created.
>
> On Wed, Feb 20, 2008 at 3:40 PM, <freeswitch at dalethatcher.com> wrote:
> Could I suggest the 'db check' query be configurable?  Oracle is  
> another
> database which doesn't work with the 'select 1' and needs 'select 1  
> from
> dual'.  Then you can have the default as 'select 1' and let the end  
> user
> fiddle with the config for their own particular broken DB.
>
> Adding in special code to handle every database out there is going  
> to be
> a big headache for you.  If it isn't already.
>
> thanks,
>
> - Dale
>
> On Wed, Feb 20, 2008 at 06:08:08AM -0800, Anthony Minessale wrote:
> > Many databases break silently or the connection drops
> > without any way to know for sure w/o trying to use it.
> >
> > I choose to ensure in the core that the data is connected.
> > executing "select 1" is a small price to pay to make sure
> > that the database is connected considering the importance
> > of persistent connectivity in the sip, jingle and call
> > limit databases as well as the javascript that rely on it.
> >
> > All of those applications to make the safe
> > assumption that the core will keep the db connected and
> > to change that would be drastic behavior change and
> > destabilize the code on the eve of our release.
> >
> > Therefore i guess we should focus on better heuristics
> > to detect firebird, preferably based on the driver name
> > and not by doing a 2nd sql stmt every time.
> >
> >
> >
> > Anthony Minessale II
> >
> > FreeSWITCH http://www.freeswitch.org/
> > ClueCon http://www.cluecon.com/
> >
> > AIM: anthm
> > MSN:anthony_minessale at hotmail.com
> > GTALK/JABBER/PAYPAL:anthony.minessale at gmail.com
> > IRC: irc.freenode.net #freeswitch
> >
> > FreeSWITCH Developer Conference
> > sip:888 at conference.freeswitch.org
> > iax:guest at conference.freeswitch.org/888
> > googletalk:conf+888 at conference.freeswitch.org
> > pstn:213-799-1400
> >
> >
> > ----- Original Message ----
> > From: David Revill <davidrevill at datarun.co.uk>
> > To: freeswitch-users at lists.freeswitch.org
> > Sent: Tuesday, February 19, 2008 4:37:28 PM
> > Subject: Re: [Freeswitch-users] Spidermonkey ODBC
> >
> >
> >
> >
> >
> > DIV {
> > MARGIN:0px;}
> >
> >
> >
> >
> > Unfortunately, there isn't a universal ansi sql
> > stmt that will do it as the only statements which can be  
> guaranteed to follow
> > the ansi standard are the simple select, update and delete  
> statements which rely
> > on knowing some table name .
> >
> >
> >
> >
> > I think we are trying to solve a problem which need
> > not exist. The Javascript application should handle the idle  
> timeout. It
> > should close the connection if it will not be using it for a while,
> > and,if it needs to, can do what db_is_up is attempting to do in the
> > application code. Testing for connection before every query seems an
> > unneccessary overhead.
> >
> >
> >
> >
> >   ----- Original Message -----
> >
> >   From:
> >   Anthony
> >   Minessale
> >
> >   To: freeswitch-users at lists.freeswitch.org
> >
> >
> >   Sent: Tuesday, February 19, 2008 6:38
> >   PM
> >
> >   Subject: Re: [Freeswitch-users]
> >   Spidermonkey ODBC
> >
> >
> >
> >
> >   is
> >   there some universal ansi sql stmt that is small and harmless
> > but is
> >   supported by everything? to replace "select 1"
> >
> >
> >
> >
> >
> >   Anthony Minessale II
> >
> > FreeSWITCH http://www.freeswitch.org/
> > ClueCon http://www.cluecon.com/
> >
> > AIM:
> >   anthm
> > MSN:anthony_minessale at hotmail.com
> > GTALK/JABBER/PAYPAL:anthony.minessale at gmail.com
> > IRC:
> >   irc.freenode.net #freeswitch
> >
> >
> > FreeSWITCH Developer
> >   Conference
> > sip:888 at conference.freeswitch.org
> > iax:guest at conference.freeswitch.org/888
> > googletalk:conf+888 at conference.freeswitch.org
> > pstn:213-799-1400
> >
> >
> >
> >
> >   -----
> >   Original Message ----
> > From: David Revill
> >   <davidrevill at datarun.co.uk>
> > To:
> >   freeswitch-users at lists.freeswitch.org
> > Sent: Tuesday, February 19, 2008
> >   6:56:31 AM
> > Subject: Re: [Freeswitch-users] Spidermonkey ODBC
> >
> >
> >
> >
> >   One way to detect firebird would be to put the
> >   'Select first 1  * from RDB$relations' into  
> switch_odbc_handle_connect.
> >   If it succeeds then it is Firebird. A better query might be  
> 'select * from
> >   rdb$database' as the system table rdb$database only ever has 1
> >   record.
> >
> >
> >     -----
> >     Original Message -----
> >
> >     From:
> >     Jonas
> >     Gauffin
> >
> >     To:
> >     freeswitch-users at lists.freeswitch.org
> >
> >
> >     Sent:
> >     Tuesday, February 19, 2008 12:18 PM
> >
> >     Subject:
> >     Re: [Freeswitch-users] Spidermonkey ODBC
> >
> >
> >
> > I''ve only tested it with firebird on windows. As you said,
> >     your driver name doesnt match any of the names I used to  
> detect if it's
> >     firebird.
> >
> > The problem is that most databases supports "SELECT 1" as a
> >     query. It will always be successful if the the connection is  
> up. However,
> >     firebird did not support "SELECT 1" and therefore i did query  
> the system
> >     database instead.
> >
> > If the firebird check fails, it will try to invoke
> >     "SELECT 1", and on firebird that query will fail. And the  
> failure is
> >     interpreted as the connection is down, and therefore it tries to
> >     reconnect.
> >
> > That's probably why you get that error. If you have any
> >     other idea on how I can detect if it's a firebird db, please  
> let me know and
> >     i'll fix the odbc code.
> >
> >
> >     On Feb 19, 2008 1:05 PM, David Revill  
> <davidrevill at datarun.co.uk>
> >     wrote:
> >
> >
> >
> >       That's a fair point, but db_is_up is only
> >       called in 2 places and the attempted re-connect could be  
> done if the
> >       exec'd query fails. That way it would not be dependant on  
> the name of the
> >       driver.
> >
> >
> >
> >       In my application, the database was queried
> >       at the beginning of the script and the connection closed. It  
> was
> >       explicitly re-connected at the end to post data, so the  
> connection timeout
> >       was not an issue.
> >
> >
> >
> >         -----
> >         Original Message -----
> >
> >         From:
> >         Jonas Gauffin
> >
> >         To:
> >         freeswitch-users at lists.freeswitch.org
> >
> >
> >
> >
> >
> >
> >
> >         Sent:
> >         Tuesday, February 19, 2008 10:35 AM
> >
> >         Subject:
> >         Re: [Freeswitch-users] Spidermonkey ODBC
> >
> >
> >
> > I did that code a couple of months ago.
> > The function
> >         was added since connections are closed if they are idle  
> too long, and
> >         they were not reconnected again if that happened.
> >
> >
> >
> >         On Feb 19, 2008 11:22 AM, David Revill <davidrevill at datarun.co.uk 
> >
> >         wrote:
> >
> >
> >
> >           The problem is in switch_odbc.c function
> >           db_is_up. It assumes that the variable is_firebird is  
> true, but the
> >           setting of this variable assumes that the Firebird  
> driver name has
> >           'FIREBIRD', 'FB64' or 'FB32' in it.
> >
> >
> >
> >           I commented out  the code in
> >           db_is_up and just returned a true value, as on closer  
> inspection
> >           could not see the point of this function.
> >
> >
> >
> >           David Revill
> >
> >
> >
> >             -----
> >             Original Message -----
> >
> >             From:
> >             David Revill
> >
> >             To:
> >             freeswitch-users at lists.freeswitch.org
> >
> >
> >
> >
> >
> >
> >
> >             Sent:
> >             Tuesday, February 19, 2008 8:58 AM
> >
> >             Subject:
> >             Re: [Freeswitch-users] Spidermonkey ODBC
> >
> >
> >
> >
> >             I spotted this error a few months ago,
> >             and to my shame did not let everyone know.  I have since
> >             archived the project so cannot point you to the actual  
> code.
> >             However, the problem is  that it assumes the name of the
> >             Firebird Driver (FB64?)  to set a boolean, so that it  
> handle
> >             the connection test differently.
> >
> >
> >
> >             When looking at the actual code, I
> >             actually felt that the connection test was redundant,  
> as all it did
> >             was a select from a system table. This meant it was  
> doing two
> >             queries for everyone required. (One to see if it could  
> do a query,
> >             and then one to do it). I therefore commented the  
> connection test
> >             function out.
> >
> >
> >
> >             regards
> >
> >
> >
> >             David Revill
> >
> >
> >               -----
> >               Original Message -----
> >
> >               From:
> >               Steven Brown
> >
> >               To:
> >               freeswitch-users at lists.freeswitch.org
> >
> >
> >               Sent:
> >               Monday, February 18, 2008 11:34 PM
> >
> >               Subject:
> >               [Freeswitch-users] Spidermonkey ODBC
> >
> >
> >
> >
> >               Hi,
> >
> >
> >
> >               I'm experimenting with
> >               spidermonkey for JavaScript call control, the basics  
> seem
> >               fine and I'm now trying to connect to an existing  
> Firebird
> >               database, the Firebird odbc lib is installed ok as  
> is unixODBC and
> >               I can confirm this and access the db no problem with  
> isql,
> >               something strange happens though when I connect in  
> spidermonkey,
> >               basically the odbc connection is made successfully,  
> but then as
> >               soon as I call either exec or query the odbc  
> connection starts to
> >               drop and re-connect continuously,
> >
> >
> >
> >               the isql output is below
> >
> >
> >
> >
> >               isql -v
> >               test
> > +---------------------------------------+
> > |
> >               Connected!
> >               |
> > |
> >               |
> > |
> >               sql-statement
> >               |
> > | help
> >               [tablename]
> >               |
> > |
> >               quit
> >               |
> > |
> >               |
> > +---------------------------------------+
> > SQL> select
> >               first 1 * from
> >               pool_phones
> > +---------------------+------------+
> > |
> >               GSMNO
> >               | ORGNO
> >               |
> > +---------------------+------------+
> > |
> >               0712345678         |
> >               1
> >               |
> > +---------------------+------------+
> > SQLRowCount returns
> >               1
> > 1 rows fetched
> > SQL>
> >
> >
> >               however the following test code
> >               calling the same query
> >
> >
> >
> >               use("ODBC");
> >
> >               var db = new
> >               ODBC("test","SYSDBA","masterkey");
> > db.connect();
> > db.query("select
> >               first 1 *  from pool_phones");
> >
> >               db.nextRow();
> >
> >               row =
> >               db.getData();
> > console_log("INFO","HELLO " +  row["GSMNO"]
> >                + "\n");
> >
> >               exit();
> >
> >
> >
> >               gives the following output and then
> >               just loops disconnecting and reconnecting until I  
> shutdown
> >               freeswitch
> >
> >
> >               2008-02-18 23:26:03 [DEBUG]
> >               switch_core_state_machine.c:144  
> switch_core_standard_on_execute()
> >               sofia/default/1000 at 192.168.0.7:5060 Execute
> >               javascript(/usr/scripts/test1.js)
> > 2008-02-18 23:26:03 [DEBUG]
> >               mod_spidermonkey.c:3150 js_api_use() Loading ODBC
> > 2008-02-18
> >               23:26:03 [DEBUG] switch_odbc.c:145  
> switch_odbc_handle_connect()
> >               Connecting test
> > 2008-02-18 23:26:03 [DEBUG] switch_odbc.c:174
> >               switch_odbc_handle_connect() Connected to [test]
> > 2008-02-18
> >               23:26:03 [DEBUG] switch_odbc.c:95  
> switch_odbc_handle_disconnect()
> >               Disconnected 0 from [test]
> > 2008-02-18 23:26:03 [DEBUG]
> >               switch_odbc.c:142 switch_odbc_handle_connect() Re- 
> connecting
> >               test
> > 2008-02-18 23:26:03 [DEBUG] switch_odbc.c:145
> >               switch_odbc_handle_connect() Connecting test
> > 2008-02-18
> >               23:26:03 [DEBUG] switch_odbc.c:174  
> switch_odbc_handle_connect()
> >               Connected to [test]
> > 2008-02-18 23:26:03 [CRIT]
> >               switch_odbc.c:234 db_is_up() The sql server is not  
> responding for
> >               DSN test []
> > 2008-02-18 23:26:03 [INFO] switch_odbc.c:239
> >               db_is_up() The connection has been re-established
> >
> >               ...
> >
> >               Any ideas much appreciated
> >
> >               Thanks
> >
> >               Steve
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> > Freeswitch-users
> >               mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> >
> > _______________________________________________
> > Freeswitch-users
> >             mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> >
> > _______________________________________________
> > Freeswitch-users
> >           mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> > Freeswitch-users
> >         mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> > _______________________________________________
> > Freeswitch-users
> >       mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> > Freeswitch-users
> >     mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> > -----Inline Attachment
> >   Follows-----
> >
> > _______________________________________________
> > Freeswitch-users
> >   mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> >
> >
> >
> >
> >
> >   Be a better friend, newshound, and know-it-all with Yahoo!  
> Mobile. Try
> >   it now.
> >
> >
> >
> >
> >
> > _______________________________________________
> > Freeswitch-users
> >   mailing
> >   list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> > -----Inline Attachment Follows-----
> >
> > _______________________________________________
> > Freeswitch-users
> > mailing
> > list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
> >
> >
> >
> >
> >
> >
> >
> >        
> ____________________________________________________________________________________
> > Looking for last minute shopping deals?
> > Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping
> > _______________________________________________
> > Freeswitch-users mailing list
> > Freeswitch-users at lists.freeswitch.org
> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> > http://www.freeswitch.org
>
>
> _______________________________________________
> Freeswitch-users mailing list
> Freeswitch-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
> _______________________________________________
> Freeswitch-users mailing list
> Freeswitch-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20080220/a2e8360f/attachment-0002.html 


More information about the FreeSWITCH-users mailing list