Recent

Author Topic: Query for inserting a new record.  (Read 11295 times)

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Query for inserting a new record.
« Reply #15 on: April 25, 2012, 11:46:15 pm »
So the quotedStr can fail for any DB engine that uses \ for escapes. MSSQL seams to be the only engine that does not use \ escapes unless specified in the query.

Looks like the only reliable cross DB engine string escape method is prepared queries. In those rare cases that prepared queries are not the best option you'll have to write your own escape routine.

Been using the two single quote escape method since using MSSQL 2000 many years ago. Made the transition over to prepared queries and other database engines some time ago. So either I didn't actually make this escape error or it never came up. Something for me to look for when reviewing old code I suppose.

Thank you ludob I have learned something today.

KpjComp

  • Hero Member
  • *****
  • Posts: 680
Re: Query for inserting a new record.
« Reply #16 on: April 26, 2012, 03:26:05 am »
Quote
Looks like the only reliable cross DB engine string escape method is prepared queries.

I've not used DB with Lazarus yet, but looking in the source TZAbstractConnection has a public method called EscapeString, so that's it will automatically be the correct one for the connection your using.

I personally like Escaping SQL, rather than parameters.  It makes debugging easer, especially on complex query's.  If you create your Query without params you could then log it & have everything you need to use inside a Database workbench or similar to test/debug with.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Query for inserting a new record.
« Reply #17 on: April 26, 2012, 08:10:04 am »
@TZAbstractConnection: you're probably looking in Zeos database components, right? I'm talking about the FPC/Lazarus built in SQLDB component set...

Re debugging: IIRC, there was an improvement in SQLDB a while ago that lets you dump all the SQL that is being sent to the database... so that might be a nice alternative to use with parametrized queries... In fact, I think I'm going to look it up and add it to the wiki if not already there...

Otherwise, if you're using ODBC, at least on Windows you can log all output and responses to a text file, which can help in debugging as well.

Thanks,
BigChimp
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Query for inserting a new record.
« Reply #18 on: April 26, 2012, 10:58:00 am »
I've not used DB with Lazarus yet, but looking in the source TZAbstractConnection has a public method called EscapeString, so that's it will automatically be the correct one for the connection your using.
This would be indeed a nice addition for sqldb.

TZAbstractConnection defines a virtual EscapeString that does an escaping with '\'. They call it "PostgreSQL escape format". Mysql overrides it with a call to mysql_real_escape_string or mysql_escape_string (MySQL doesn't want implementers to escape literals and only guarantees to work with literals escaped with its own functions) . Mssql and sqlite both override with Result := AnsiQuotedStr(Value, '''');

KpjComp

  • Hero Member
  • *****
  • Posts: 680
Re: Query for inserting a new record.
« Reply #19 on: April 26, 2012, 11:14:08 am »
I soppse it's all down to taste, but for me flat SQL just gives less issues, especially for complex query's.  For simple query's I often use params.

Quote
lets you dump all the SQL that is being sent to the database..

Looking in sqldb.pp, it appears it just logs the SQL & not the params.  And even if it did log the params when I paste the SQL into say a DB Workbench, I'm then going to have to type all params into the parameter dialog box that appears.  Plain SQL I can just paste and execute.

There is also compatibility reasons, some DB's will say allow you to sort on a param, some won't.  Some might allow you do a conditional expression on a param, some won't. etc. unfortunately param's are DB specific.

And like I say, logging is very simple.  eg.  Log(theSQL.Text);  or memo1.Text := theSQL.Text;  pasting the text form these will give you the exact Query & fake Params to reproduce the result.

The chances are also if you developing a product to work with multiple DB backends, your likely dynamically generating your SQL anyway.  As the Standard in (SQL) isn't always standard. :)

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Query for inserting a new record.
« Reply #20 on: April 26, 2012, 12:22:37 pm »
Quote
Looking in sqldb.pp, it appears it just logs the SQL & not the params.  And even if it did log the params when I paste the SQL into say a DB Workbench, I'm then going to have to type all params into the parameter dialog box that appears.  Plain SQL I can just paste and execute.
When the database back end (sqldb) is using data binding, the parameter data stay in their native binary format and aren't converted to sql at all. So there is no way to get the sql equivalent of that query. Currently the MySQL sqldb implementation isn't using data binding and converts internally everything to sql but even in that case the full sql query isn't accessible as the logging is on the TSQLQuery level. 
Parametrized and non parametrized queries are really 2 different things and have their uses. Try fe. inserting large blobs without parameters. I mentioned double values earlier: conversions to sql introduce rounding errors in the client or database and result in comparison problems.
Big, complex queries for reporting purposes are indeed easier to write without parameters (copy and paste from a query tool) and they also tend to fall in the category where parameters can produce sub optimal query planning. Adding an escapestring would be helpful in this case.

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Query for inserting a new record.
« Reply #21 on: April 26, 2012, 03:31:12 pm »
Have had a chance to look into this a little more. It appears as though PostgreSQL has a setting to turn the \ escape method. As of postgreSQL 9.1 the default is to ignore \ escapes.

Have realized that there are common cases where prepared queries are not much use. This happens when search terms are not know at compile time. For example searching an address book. It is unknown if searches will be by name, zip, city, street, ... or any combination of these. In this case the SQLDB filter and serverFilter work well and SQL escaping must be used.
« Last Edit: April 26, 2012, 09:25:22 pm by goodname »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Query for inserting a new record.
« Reply #22 on: April 27, 2012, 04:50:50 pm »
When the database back end (sqldb) is using data binding, the parameter data stay in their native binary format and aren't converted to sql at all. So there is no way to get the sql equivalent of that query. Currently the MySQL sqldb implementation isn't using data binding and converts internally everything to sql but even in that case the full sql query isn't accessible as the logging is on the TSQLQuery level. 
Parametrized and non parametrized queries are really 2 different things and have their uses. Try fe. inserting large blobs without parameters. I mentioned double values earlier: conversions to sql introduce rounding errors in the client or database and result in comparison problems.
Updated the wiki with my experiences with logging SQLDB:
http://wiki.lazarus.freepascal.org/SqlDBHowto
Updates/corrections welcome... as always ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified