Hi Carlos,
I have no (or very small) experience with Postgres, but I guess it
works the same as for Mssql Server and Oracle.
The way it works with Mssql MsSql (and Oracle) is:
Prepared statements are not shared between connections from the client
side, the gain is on the server side.
Use case of prepared statement is tipically reusability: a statement
you will execute thousand times with different parameters, looping in
you execute call.
The server keep the same compiled plan for each execution (note it can
have some tricky drawbacks too: parameter sniffing, bad cardinality
estimations hence bad plans ).
The server will detect that you are using nearly same statements, based
on query plans in query plan cache and will not recompile it.
The query plan cache being global to the server, it can share plans
between different connections, there is a gain here when you ahave a lot
of connections executing same statements too.
It means that you have to prepare the statement once in each connection,
then you can loop in execute.
This is by design.
HTH.
Regards,
Alain
Le 10/01/2015 12:09, David Carlos Manuelda a écrit :
p...@highoctane.be wrote:
This was asked a while ago, here is what I could find:
On 29/10/2013 9:32 PM, Yanni Chiu wrote:
Looking at the postgres docs, I see PREPARE and EXECUTE. I'll try to
start up a postgres server, and see if I can get it to work.
Here's an example:
TestPGConnection new executeAll: #(
'PREPARE test1 (text,text,integer,text) AS
INSERT INTO films (code, title, did, kind)
VALUES ($1,$2,$3,$4)'
'EXECUTE test1(''T_601'', ''Yojimbo'', 106, ''Drama'')'
).
TestPGConnection new executeAll: #(
'PREPARE test2 (text) AS SELECT * FROM films WHERE code LIKE $1'
'EXECUTE test2(''%0%'')'
).
TestPGConnection new executeAll: #(
'EXECUTE test2(''%1%'')'
).
But, the third example returned:
ERROR: prepared statement "test2" does not exist
So it seems to not remember the prepared statements across connection
cycles. Re-sending the prepared statement isn't good for performance, but
it does solve your security issue.
HTH
Phil
Thanks for the info, yes it can be done via execute manually, just that I
was suggesting to have proper code for handling it.
Furthermore, as per the way prepared statements are designed, they are not
saved across database connections, so on every new connection you have to
send them, or at least the ones you will be using (calling).
On Sat, Jan 10, 2015 at 2:06 AM, David Carlos Manuelda
<stormb...@gmail.com> wrote:
I've installed and tested PostgresV2 under pharo with the following doits
Gofer new
smalltalkhubUser: 'PharoExtras' project: 'PostgresV2';
configuration;
load.
(#ConfigurationOfPostgresV2 asClass project version: '2.4') load
While it works good, it is missing a very important feature from both
security and performance point of view: The prepared statements.
As a brief, prepared statements are parameterized SQL statements that are
loaded ONCE per connection instead of sending the whole query to DB every
time, and also, they are parameterized, so it completelly prevents SQL
injection, as the parameters are automatically 'detected' and scaped
and/or handled accordingly without allowing in any case a parameter to
alter the SQL meaning, which can happen by using regular SQL queries made
by string concatenation.
I browsed the class and did not find any prepare: method nor anything
similar.
Also, you can give a name to a SQL sentence, which makes the code much
more readable without messing too much logic with SQL commands and string
concatenation.
An example is as follows: (supposing we have an instance variable
connection, already initialized and connected via PGConnection class)
Instead of:
self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser
userName, ''';'.
Would be something like this:
self connection executePrepared: 'getUserData' with: anUser userName.
And another suggestion could be something like:
self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable
WHERE name=$1'
Any plan for this to be implemented or any hint to other PostgreSQL class
that already has it?
David.
P.S. I've written in my blog about this some time ago:
http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.html
if someone finds it useful.
