[AOLSERVER] aolserver and Pgtcl
Hi, I know aolserver comes with postgres support, but I would like to use the Pgtcl library for a project. Has anybody managed to use Pgtcl and keep a persistent connection to the database that can be re-used by the requests coming in, or do you have to keep opening a connection to the db, query the db then close the connection for every single request? Thank you -- Xavier Bourguignon -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
> Hi, > > I know aolserver comes with postgres support, but I would like to use > the Pgtcl library for a project. > Has anybody managed to use Pgtcl and keep a persistent connection to > the database that can be re-used by the requests coming in, or do you > have to keep opening a connection to the db, query the db then close > the connection for every single request? > Just curious, why use pgtcl instead of the underlining db api in aolserver? Note that I am one of the maintainers of pgtcl (at least one of theversions), and I don't use it within aolserver. I don't think it hasbeen proven that pgtcl is thread safe...so I don't know what kind oftrouble you will get in (although it's on my list to make it so). Given that, I don't think you want to share connection/resulthandles across connections, so you would want to connect/disconnectevery time, just to be safe...although you could give it a try to seewhat happens... I know people who use pgtcl from Apache + mod_tcl, but Apache is a different environment. HTH, --brett Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
I think the ns_odbc driver can survive from one connection to another. When I use it, I have to put in a trace filter to close/return the handle. I can't imagine that it is a good idea to just randomly reuse an open handle. You risk running out of handles for one, but the state of the connection would be more or less random. BTW, this is one of the advantages of ns_db: a persistent shared pool of db connections, but they need to be cleaned up between requests (by ns_db releasehandle). tom jackson On Wednesday 16 April 2008 11:48, Brett Schwarz wrote: > > Hi, > > > > I know aolserver comes with postgres support, but I would like to use > > the Pgtcl library for a project. > > Has anybody managed to use Pgtcl and keep a persistent connection to > > the database that can be re-used by the requests coming in, or do you > > have to keep opening a connection to the db, query the db then close > > the connection for every single request? > > Just curious, why use pgtcl instead of the underlining db api in aolserver? > > Note that I am one of the maintainers of pgtcl (at least one of > theversions), and I don't use it within aolserver. I don't think it hasbeen > proven that pgtcl is thread safe...so I don't know what kind oftrouble you > will get in (although it's on my list to make it so). > > Given that, I don't think you want to share connection/resulthandles across > connections, so you would want to connect/disconnectevery time, just to be > safe...although you could give it a try to seewhat happens... > > I know people who use pgtcl from Apache + mod_tcl, but Apache is a > different environment. > > HTH, > --brett > > > > > ___ >_ Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > > > -- > AOLserver - http://www.aolserver.com/ > > To Remove yourself from this list, simply send an email to > <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the > email message. You can leave the Subject: field of your email blank. -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
Hi Brett,
Thanks for the message.
I just wanted to try it with pgtcl.
My problem with the underlying db api in aolserver is that there is no
mechanism for binding variables to an SQL statement, e.g:
set sql {
select
*
from
table
where
status = ?
}
There is no mean to bind a variable to the placeholder. May be the db
api is not matured yet, but this would certainly be a worth while
addition.
On 16/04/2008, Brett Schwarz <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I know aolserver comes with postgres support, but I would like to use
> > the Pgtcl library for a project.
> > Has anybody managed to use Pgtcl and keep a persistent connection to
> > the database that can be re-used by the requests coming in, or do you
> > have to keep opening a connection to the db, query the db then close
> > the connection for every single request?
> >
>
>
>
> Just curious, why use pgtcl instead of the underlining db api in aolserver?
>
> Note that I am one of the maintainers of pgtcl (at least one of
> theversions), and I don't use it within aolserver. I don't think it hasbeen
> proven that pgtcl is thread safe...so I don't know what kind oftrouble you
> will get in (although it's on my list to make it so).
>
> Given that, I don't think you want to share connection/resulthandles across
> connections, so you would want to connect/disconnectevery time, just to be
> safe...although you could give it a try to seewhat happens...
>
> I know people who use pgtcl from Apache + mod_tcl, but Apache is a different
> environment.
>
> HTH,
> --brett
>
>
>
>
>
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now.
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email to <[EMAIL
> PROTECTED]> with the
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
> field of your email blank.
>
--
Xavier Bourguignon
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
You should look at the pg driver, I think it can emulate bind variables, or
maybe it is part of the db_* API of OpenACS.
I also have a wrapper API for ns_db which has more of a stored procedure type
API.
Here is a link to an example application:
http://junom.com/gitweb/gitweb.perl?p=twt.git;a=tree;f=packages/dbo
Most of the API are used on this tcl page:
http://junom.com/gitweb/gitweb.perl?p=twt.git;a=blob;f=packages/dbo/www/select-relations.tcl
The queries are defined in individual files:
http://junom.com/gitweb/gitweb.perl?p=twt.git;a=tree;f=packages/dbo/queries
In general, bind variables is something which is handled by the driver,
because there is no standard on how to do it. Some use ?, some use :varname.
tom jackson
On Wednesday 16 April 2008 13:04, Xavier Bourguignon wrote:
> Hi Brett,
>
> Thanks for the message.
>
> I just wanted to try it with pgtcl.
> My problem with the underlying db api in aolserver is that there is no
> mechanism for binding variables to an SQL statement, e.g:
>
> set sql {
> select
> *
> from
> table
> where
> status = ?
> }
>
> There is no mean to bind a variable to the placeholder. May be the db
> api is not matured yet, but this would certainly be a worth while
> addition.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Apr 16, 2008, at 2:33 PM, Tom Jackson wrote: You should look at the pg driver, I think it can emulate bind variables, or maybe it is part of the db_* API of OpenACS. You have to compile nspostgres.c for OpenACS use to make the command available. Then use ns_pg_bind rather than ns_db. :foo will reference the tcl var foo in the caller. Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On 2008.04.16, Tom Jackson <[EMAIL PROTECTED]> wrote:
> In general, bind variables is something which is handled by the
> driver, because there is no standard on how to do it. Some use ?, some
> use :varname.
Can someone please help by doing the necessary research and put together
a matrix of supported databases, and list what version of each supports
bind variables _at all_ (older MySQL didn't), and which ones support
named (":varname") vs. unnamed ("?") bind variables?
I have wanted to add bind variable support to nsdb for a _long_ time,
but never got around to computing this support matrix that I describe
above.
Would anyone care to take up the task?
--
Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Wednesday 16 April 2008 15:11, Don Baccus wrote:
> On Apr 16, 2008, at 2:33 PM, Tom Jackson wrote:
> > You should look at the pg driver, I think it can emulate bind
> > variables, or
> > maybe it is part of the db_* API of OpenACS.
>
> You have to compile nspostgres.c for OpenACS use to make the command
> available.
>
> Then use ns_pg_bind rather than ns_db. :foo will reference the tcl
> var foo in the caller.
So Xavier, your query would/could look something like this:
set status 123
set sql {
select
*
from
table
where
status = :status
}
ns_pg_bind execute $db $sql
(There are other options, check out the -bind switch, which takes an ns_set
id.)
tom jackson
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Apr 16, 2008, at 3:44 PM, Dossy Shiobara wrote: I have wanted to add bind variable support to nsdb for a _long_ time, but never got around to computing this support matrix that I describe above. If you do, it needs to be a config option, because with Oracle you do *not* want to do this in ns_db. Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote: I have wanted to add bind variable support to nsdb for a _long_ time, but never got around to computing this support matrix that I describe above. I don't really like bind variables, I would much rather see it implemented like: ns_db select $db "select * from people where country = $1 and age > $2" [list "au" 25] The api could even fall back to emulating this when the database itself does not support it. This would be in a database independent way, instead of the current way with Postgres and Oracle implementing their own commands. Bas. -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Apr 16, 2008, at 4:20 PM, Bas Scheffers wrote: On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote: I have wanted to add bind variable support to nsdb for a _long_ time, but never got around to computing this support matrix that I describe above. I don't really like bind variables, I would much rather see it implemented like: ns_db select $db "select * from people where country = $1 and age > $2" [list "au" 25] Geez, names are more readable than numbers in any sizable query. nspostgres supports the optional passing of an ns_set to define the bind vars, and the openacs db api will convert an array get list to the ns_set for you. And the default bind var syntax is just damned convenient, why force more work than is necessary on the part of the person writing queries? And before answering "well, they can always use the variable directly" remember that both emulated and real bind vars give protect against sql smuggling. Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Wednesday 16 April 2008 16:20, Bas Scheffers wrote: > I don't really like bind variables, I would much rather see it > implemented like: > ns_db select $db "select * from people where country = $1 and age > > $2" [list "au" 25] You can look at the examples in my recent post. It works with plain 'ol AOLserver, doesn't use bind vars, but is safe from SQL smuggling attacks (which is most of the reason for bind vars). Your script/page level code can remain unchanged even when you switch databases. I look at ns_db as a starting point, but it is too vulnerable to the database you are using. The stored procedure model is more robust and easy to emulate, as my example code demonstrates. Also, if anyone is interested on working on a database driver, please choose and odbc driver. The unixodbc library code is well maintained and the newer SQL standards follow it. Both Oracle and postgreSQL point to their ODBC interface as their approximation to the newer standards. One interesting enhancement to ns_db might be to exploit the data dictionary which is now standard for SQL. Using this, you can get tables, columns, etc. The queries don't change from db to db, so you can query capabilities, etc. I'm slowly working on it, very slowly. tom jackson -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On 17/04/2008, at 9:25 AM, Tom Jackson wrote: Your script/page level code can remain unchanged even when you switch databases. That looks more like an OR mapping framework. I think that is a good thing to have also but to me it is separate from having bind variables in the core nsdb api. I would expect any OR mapping tool to build on it, rather than emulate it. Bas. -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On 17/04/2008, at 9:10 AM, Don Baccus wrote: ns_db select $db "select * from people where country = $1 and age > $2" [list "au" 25] Geez, names are more readable than numbers in any sizable query. The numbers/names isn't that important to me. What is important is that I would like to see variables explicitly attached to the query rather than simply saying: "use this existing Tcl variable." nspostgres supports the optional passing of an ns_set to define the bind vars How does this work, do you have an example? And before answering "well, they can always use the variable directly" remember that both emulated and real bind vars give protect against sql smuggling. I would never say that; not having to worry about quoting is one of the main advantages of using bind variables/parameters. Bas. -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
This is how pgtcl actually does bind variables... - Original Message From: Bas Scheffers <[EMAIL PROTECTED]> To: AOLSERVER@LISTSERV.AOL.COM Sent: Wednesday, April 16, 2008 4:20:46 PM Subject: Re: [AOLSERVER] aolserver and Pgtcl On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote: > I have wanted to add bind variable support to nsdb for a _long_ time, > but never got around to computing this support matrix that I describe > above. I don't really like bind variables, I would much rather see it implemented like: ns_db select $db "select * from people where country = $1 and age > $2" [list "au" 25] The api could even fall back to emulating this when the database itself does not support it. This would be in a database independent way, instead of the current way with Postgres and Oracle implementing their own commands. Bas. -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
The other is performance. Jade On Wed, Apr 16, 2008 at 5:40 PM, Bas Scheffers <[EMAIL PROTECTED]> wrote: > I would never say that; not having to worry about quoting is one of the > main advantages of using bind variables/parameters. > > Bas. > > > > -- > AOLserver - http://www.aolserver.com/ > > To Remove yourself from this list, simply send an email to < > [EMAIL PROTECTED]> with the > body of "SIGNOFF AOLSERVER" in the email message. You can leave the > Subject: field of your email blank. > -- Jade Rubick Senior Architect United eWay [EMAIL PROTECTED] tel (503)285-4963 fax (707)671-1333 www.UNITEDeWAY.org -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Wed, Apr 16, 2008 at 5:20 PM, Bas Scheffers <[EMAIL PROTECTED]> wrote:
> On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote:
>
> I don't really like bind variables, I would much rather see it implemented
> like:
>
> ns_db select $db "select * from people where country = $1 and age > $2"
> [list "au" 25]
You mean:
ns_db select $db {select * from people where country = $1 and age
> $2} [list "au" 25]
I presume.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
It is the obvious way to do it. I would suspect that the OpenACS team decided to mimic Oracle behavior simply to lower the amount of re- writing of existing queries to be done. Which is perfectly valid for their goal and how I would likely have done it. However, to implement this in ns_db, there is no good reason to make it work the same way as Oracle does unless it is decided we want it to work the same for every database and this is the chosen syntax. Bas. On 17/04/2008, at 12:32 PM, Brett Schwarz wrote: This is how pgtcl actually does bind variables... - Original Message From: Bas Scheffers <[EMAIL PROTECTED]> To: AOLSERVER@LISTSERV.AOL.COM Sent: Wednesday, April 16, 2008 4:20:46 PM Subject: Re: [AOLSERVER] aolserver and Pgtcl On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote: I have wanted to add bind variable support to nsdb for a _long_ time, but never got around to computing this support matrix that I describe above. I don't really like bind variables, I would much rather see it implemented like: ns_db select $db "select * from people where country = $1 and age > $2" [list "au" 25] The api could even fall back to emulating this when the database itself does not support it. This would be in a database independent way, instead of the current way with Postgres and Oracle implementing their own commands. Bas. -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED] > with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED] > with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank. -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
Uhm, yes, possible. :)
On 17/04/2008, at 12:54 PM, Michael A. Cleverly wrote:
On Wed, Apr 16, 2008 at 5:20 PM, Bas Scheffers <[EMAIL PROTECTED]>
wrote:
On 17/04/2008, at 8:14 AM, Dossy Shiobara wrote:
I don't really like bind variables, I would much rather see it
implemented
like:
ns_db select $db "select * from people where country = $1 and age >
$2"
[list "au" 25]
You mean:
ns_db select $db {select * from people where country = $1 and age
$2} [list "au" 25]
I presume.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]
> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the
Subject: field of your email blank.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Apr 16, 2008, at 8:37 PM, Bas Scheffers wrote: It is the obvious way to do it. I would suspect that the OpenACS team decided to mimic Oracle behavior simply to lower the amount of re-writing of existing queries to be done. Which is perfectly valid for their goal and how I would likely have done it. However, to implement this in ns_db, there is no good reason to make it work the same way as Oracle does unless it is decided we want it to work the same for every database and this is the chosen syntax. Well, yes, of course compatibility with Oracle bind variables was a goal, that's why we called it bind variable emulation. However, in no imaginable world would we have built a mechanism that *forced* you to append a list of value to the query, outside the query. At times it's convenient, mostly, though, it just noise. a bit like saying set a $1 [list foo] when set a foo works just fine. And, yes, I have had 25 years of experience as a bona-fide compiler writer and have a background in language design. Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
Re: [AOLSERVER] aolserver and Pgtcl
On Wednesday 16 April 2008 17:35, Bas Scheffers wrote: > On 17/04/2008, at 9:25 AM, Tom Jackson wrote: > > Your script/page level code can remain unchanged even when you switch > > databases. > > That looks more like an OR mapping framework. I think that is a good > thing to have also but to me it is separate from having bind variables > in the core nsdb api. I would expect any OR mapping tool to build on > it, rather than emulate it. You are right this is not bind variables, but is isn't an OR mapping. It is more similar to a stored procedure, but at the Tcl level. Notice that the OpenACS solution is very compact, as it follows the Oracle type of bind variables, that is, the bind variables are 'named'. Because they are named, there is no need to bind the variables in order. Besides requiring more user level code, you would also have to be careful every time you change the query text: if the order of the parameters changes, you have to re-order other parts of your code. Also, if the placeholders are anonymous tags like '?' or '$1', you could actually have trouble figuring out the relationship between placeholder and a local variable. My particular code uses named queries (just like OpenACS, but grouped into namespaces) and explicit parameters. My code requires more user level code, but is slightly more flexible, for instance, you could switch from a static data source to an SQL data source. But, it is also easier to manage transactions. Most important to me is that adding a new database type doesn't require editing the code, you just add a new implementation. tom jackson -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
