[Freeswitch-users] Spidermonkey ODBC

freeswitch at dalethatcher.com freeswitch at dalethatcher.com
Wed Feb 20 06:40:17 PST 2008


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





More information about the FreeSWITCH-users mailing list