[Freeswitch-users] Spidermonkey ODBC
Michael Jerris
mike at jerris.com
Wed Feb 20 10:54:30 EST 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-0001.html
More information about the Freeswitch-users
mailing list