Prepared Statements

[Ionic]

Hello boys and girls,

I've been wondering a lot how to use those prepared statements.

I've found TZConnection.DbcConnection.PrepareStatementWithParams, but do I really have to specify the Statement AND the Parameters in one process? I'd like to just specify the prepared statement once, then collect the parameters and last but not least to execute this statement.

However, that's not the main problem though, the point I really would to know is whether the thing I want to do would work with PrepareStatementWithParams.

And what about a TZQuery object? At least it doesn't seem like I'd need it, a TZConnection object seems to be the only required component/object, is this true? Won't I have to open a query for that?

Example code:

Code: Select all

var
  Statement: IZPreparedStatement;
  Params: TStrings;
begin
  // Let us assume the connection is set up correctly...

  (* We want to execute the following query:             *
   * INSERT INTO table (a,b,c) VALUES('foo','bar','baz') *
   *                                                     *
   * Basically, this could be done via this prep. st.:   *
   * INSERT INTO table (a,b,c) VALUES(?,?,?)             *)

  // Get the three parameters
  Params := TStrings.Create;
  Params.Add (Dosomefancystuff ());
  Params.Add (Justanotherfunction ());
  Params.Add (Morefancystuff ());

  // Now that we have the parameters, build and execute the statement.
  Statement := ZConnection.DbcConnection.PrepareStatementWithParams ('INSERT INTO table (a,b,c) VALUES(?,?,?);', Params);
 
  // OK, but how to execute the statement? I know there is ExecutePrepared and ExecuteQueryPrepared. Due to the fact that I want to use INSERT here, will it just be enough to run...
  if (Statement.ExecutePrepared = TRUE) then
    // ...
  else 
    // ...

  // Or should I use ExecuteQueryPrepared nevertheless?
end;

Also, how can I specify a binary parameter/a blob? I don't like the thought of putting "binary" data (yes, sure, even text is binary, but that's not the point...) into a TString
s object, really.

I hope you can help me with thoughts, hints and rants regarding my little problem.

Btw, I guess this would also be pretty interesting for other ZeosLib users.

Best regards,


Mihai

[mdaems]

Hi Ionic,

To disappoint you : the Parameters in the TStrings are not query data parameters the way you intend to use them. They are more like extra information to define how the statement object is defined. Actually, the dbc part of coding you're trying to use is not intended for use in combination with component-based programming. Use the components or use the dbc interfaces only (which is quite a specialized domain).

If you really want to use the prepare/bind/execute/binde/execute/... kind of stuff you'll have to wait for 7.X.

Current state of trunk already allows to use the prepare/unprepare methods for a TZQuery component. Internally there's not effect yet.

If you really like it a lot and work with the mysql database, you could use Tesing branch for that. It's not complete yet. (You may get issues with data fetched from the server, because of the different protocol used by mysql for sending normal and prepared data, that I didn't sort out yet)

However, if you really like it a lot and work with the another database, you could use Tesing branch for that. But you'll have to dive into the code to split the prepare, bind and execute parts. (I think for Interbase/FB this may be quite simple)

SO : feel free to help me a hand!

Mark

[Ionic]

Hello Mark,

thank you very much for your explanation. I somehow already anticipated that I'd be using the wrong attempt, "IZStatement" just didn't look like it was designed to be used with components unfortunately...

Again unfortunately the program I'm working on is to be used productive and I don't feel like testing things on my "costumers".

I wanted to use those prepared statements to bypass working with mysql_real_escape_string (which sadly can be tricked out and thus SQL injections might indeed occur) basically, but if that's not working I'll use the other approach.

Speaking about 7.x, I've read the news on the front page and was kinda happy to see Prepared Statements are a thing to implement for this version, but sadly again the program has to be finished probably far earlier than the new version will appear to be at least Beta (if not stable.)

Though, personally I'm very interested in helping. Even if not by code, I could at least do testing, if that would help. I lack the knowledge and deeper understanding of databases, as well as ObjPsc/ZeosDBO for really contributing in the code-wise way, I fear. However, I'll fork the programm in it's current state and install ZeosLib Testing dedicated, this way I'd could have both things getting set up just fine.

Please let me know if you like it and/or if that could help you.

Best regards,


Mihai