[Freeswitch-users] Spidermonkey ODBC
Jonas Gauffin
jonas.gauffin at gmail.com
Thu Feb 21 07:09:51 PST 2008
I just uploaded a patch http://jira.freeswitch.org/browse/FSCORE-102
Brian, can you be kind and fix/add the config when/if the patch is accepted?
On Wed, Feb 20, 2008 at 7:04 PM, Brian West <brian.west at mac.com> wrote:
> I would wrap the query in a CDATA just to be safe.
>
> /b
>
> On Feb 20, 2008, at 10:23 AM, Jonas Gauffin wrote:
>
> > ohh. yes ofcourse. didnt think of that.
> >
> > How about adding driver mapping into the odbcfile.
> >
> > for example:
> >
> > <section name="odbc">
> > <connection_check driver="default">SELECT 1</connection_check>
> > <connection_check driver="fb64">SELECT FIRST 1 * FROM RDB
> > $RELATIONS</connection_check>
> > <connection_check driver="firebird">SELECT FIRST 1 * FROM RDB
> > $RELATIONS</connection_check>
> > </section>
> >
> > On Wed, Feb 20, 2008 at 4:54 PM, Michael Jerris <mike at jerris.com>
> > wrote:
> > 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
> >
> >
> > _______________________________________________
> > 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/20080221/c6d4add3/attachment-0002.html
More information about the FreeSWITCH-users
mailing list