[Freeswitch-users] Spidermonkey ODBC
Łukasz Zwierko
lzwierko at gmail.com
Wed Feb 20 09:51:51 EST 2008
Hi,
I think you guys have a wrong approach to this issue, as far as I'm
concerned, a well written application should have clear distinction between
connection layer and sql layer. What I mean is that if application wants to
do a query to DB, than handling issues like dropped connection should not be
mixed with inserts/selects.
So, for example, there should exist a separate layer which is responsible
for generating queries, and separate layer which actually makes the query,
handles errors and optionally, repeats the query or insert (refer to java
spring/hibernate - quite heavy java engines).
This way you could avoid any "select 1" or such like stuff every time you
want to do a transaction to DB.
Still you feel that "select 1" is a better choice, but not all DB support
it, you could just make an assumption, that in each DB there shall exist a
table with just one column and row, and make a query to it (can call it
KeepAliveTable).
Still, in my opinion the application should not test the connection each
time it wants to make a transaction as the overhead will be huge.
Łukasz
2008/2/20, Anthony Minessale <anthmct at yahoo.com>:
>
> 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
>
> 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 <anthmct at yahoo.com>
> *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 <jonas.gauffin at gmail.com>
> *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 <jonas.gauffin at gmail.com>
> > *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 <davidrevill at datarun.co.uk>
> > > *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 <steven.brown at justfone.com>
> > > *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:5060Execute 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.<http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ%20>
>
> _______________________________________________
> 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://us.rd.yahoo.com/evt=51734/*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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20080220/e5b7b37c/attachment-0001.html
More information about the Freeswitch-users
mailing list