Lazarus

Programming => Databases => Topic started by: perhen on April 25, 2012, 12:08:21 am

Title: Query for inserting a new record.
Post by: perhen on April 25, 2012, 12:08:21 am
Hi
I am trying to save values from editboxes, and Ihave written:

SqlQuery.close;
SqlQuery.sql.Clear;
SqlQuery.SQL.text:='insert into mytable (col1, col2)';
SqlQuery.sql.text:='values ('+''''+edit1.text+''''+','+''''+edit2.text+''''+')';
SqlQuery.ExecSQL;

It doesn't work! Can anybody tell me why, please.

Per
Title: Re: Query for inserting a new record.
Post by: Leledumbo on April 25, 2012, 01:56:50 am
Quote
SqlQuery.SQL.text:='insert into mytable (col1, col2)';
SqlQuery.sql.text:='values ('+''''+edit1.text+''''+','+''''+edit2.text+''''+')';
You overwrite the SQLQuery.SQL.Text property, if you want to cut it because of long line, use Add method of the SQL property:
Code: [Select]
SqlQuery.SQL.Add('insert into mytable (col1, col2)');
SqlQuery.SQL.Add('values ('+''''+edit1.text+''''+','+''''+edit2.text+''''+')');
Title: Re: Query for inserting a new record.
Post by: BigChimp on April 25, 2012, 07:27:28 am
Also, try inserting this text in edit1:
Quote
Jim's 12" Steak Knife
... and see whether your program works.

If not, another case for parametrized queries ;) See the wiki article on TSQLQuery for info on that...
Title: Re: Query for inserting a new record.
Post by: goldenfox on April 25, 2012, 07:48:43 am
Try using params

Quote
SqlQuery.close;
SqlQuery.sql.Clear;
SqlQuery.SQL.text:='insert into mytable (col1, col2) values (:value1,:value2)';
SqlQuery.Params.ParamByName('value1').AsString := edit1.text;
SqlQuery.Params.ParamByName('value2').AsString := edit2.text;
SqlQuery.ExecSQL;
Title: Re: Query for inserting a new record.
Post by: mch_01 on April 25, 2012, 08:34:57 am
First of all, if you are trying to use full SQL queries, don't forget the space after ')'.
is: 'insert into mytable (col1, col2)'
sholud be: 'insert into mytable (col1, col2) '

After your marge you reciving the string:
'insert into mytable (col1, col2)values (col1_value, col2_value);'

In much cases this is incorrect syntax. The proper SQL string is:
'insert into mytable (col1, col2) values (col1_value, col2_value);'

Please consider the BigChimp attension.
Title: Re: Query for inserting a new record.
Post by: goodname on April 25, 2012, 03:58:40 pm
While parametrized queries take care of escaping quotes in strings it is not always the best way to go. Because there are unknowns in parameters query planners have to make assumptions that are not as good as having all the available information.

If building without parameters then use QuotedStr to escape quotes.
http://lazarus-ccr.sourceforge.net/docs/rtl/sysutils/quotedstr.html

Code: [Select]
SqlQuery.SQL.text:='insert into mytable(col1,col2)values('+quotedStr(edit1.text)+','+quotedStr(edit2.text)+');';

Update: QuotedStr will fail for DB engines that use \ to escape characters and strings that contain \ followed by '.
eg will\'fail
Title: Re: Query for inserting a new record.
Post by: BigChimp on April 25, 2012, 04:44:37 pm
Goodname,

Would you have any documentation for that claim? What kind of situations are you talking about (e.g. using certain indexes, certain databases)?
Parametrized queries can allow the database query planner/optimizer to preplan the query, so it runs faster when repeated.
I don't know if it's faster or slower when run only once, but the practice of using parametrized queries is quite standard advice these days.
Anyway, if you have more details on this, perhaps a note on the Using SQLQuery wiki page might be nice, so we keep as much relevant info in one place as possible.

Note that you may also have to deal with formatting for date/time fields that can depend on database backend.

Still, it's a free world so if you don't want to use parametrized queries... be my guest. I'd just be careful in advising this to database newcomers...

Thanks,
BigChimp
Title: Re: Query for inserting a new record.
Post by: ludob on April 25, 2012, 05:18:18 pm
Quote
While parametrized queries take care of escaping quotes in strings it is not always the best way to go. Because there are unknowns in parameters query planners have to make assumptions that are not as good as having all the available information.
That is a true statement but not really useful since the cases are not that obvious.
Yes preparing a statement and binding do have an overhead. But binding parameters can have a positive side also because no parsing is needed and less conversions. Take floating point values. When they are bound as floating point values , no string to float conversions need to be made and there are less rounding errors. Another example is binary data. Without data binding, a lot of escaping and un-escaping has to be done. In these cases it is not clear at all what the fastest route is going to be, even for one single query.
Query planners making suboptimal assumptions is an even more complex thing to forecast. It'll depend on the actual query and how your data are distributed. It also requires a very good knowledge of the query planner internals (which is not disclosed for closed source databases) and they can change from one database release to another.
So yes you are right but it has very very limited practical use.
Title: Re: Query for inserting a new record.
Post by: goodname on April 25, 2012, 05:34:12 pm
In the case of the insert query in the initial post a prepared query would likely be better if running more then once. Think the planning on inserting a single record is straight forward and as lubod says there is no need for conversion to string which can cause it's own issues.

The time it takes to run the planner can be very brief in comparison to the time it takes to run the actual query. If this is the case running the planner each time can be an advantage. The following link refers to PostgreSQL specifically but think it should be similar on other database engines.

http://wiki.postgresql.org/wiki/FAQ#Why_is_my_query_much_slower_when_run_as_a_prepared_query.3F
Title: Re: Query for inserting a new record.
Post by: ludob on April 25, 2012, 05:37:58 pm
If building without parameters then use QuotedStr to escape quotes.
http://lazarus-ccr.sourceforge.net/docs/rtl/sysutils/quotedstr.html

Code: [Select]
SqlQuery.SQL.text:='insert into mytable(col1,col2)values'+quotedStr(edit1.text)+','+quotedStr(edit2.text)+');';
Overlooked this statement but this is wrong advise (other than the missing left bracket after values). quotedstr is doubling single quotes inside the string while fe. MySQL and Oracle want them escaped. When using parameters, the database back-end will do the correct escaping for you.
Title: Re: Query for inserting a new record.
Post by: perhen on April 25, 2012, 05:44:45 pm
Thank you, all of you. My problem is solved  :D, and I have got good hints reading the replies.
Title: Re: Query for inserting a new record.
Post by: ludob on April 25, 2012, 05:50:56 pm
The following link refers to PostgreSQL specifically but think it should be similar on other database engines.

http://wiki.postgresql.org/wiki/FAQ#Why_is_my_query_much_slower_when_run_as_a_prepared_query.3F
That explains in detail what I meant with data distribution. It also confirms that this is a case by case exercise comparing the different execution plans. It is certainly useful for optimising certain complex queries on large data sets but of limited practical use in most other cases.
Title: Re: Query for inserting a new record.
Post by: goodname on April 25, 2012, 06:02:02 pm
Added the missing bracket in earlier post. Didn't test the query to see if the syntax was correct.

About half way down the page the mySQL docs say.
Quote
A “'” inside a string quoted with “'” may be written as “''”.
http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

Oracle documentation says the same thing about escaping quotes.
http://www.orafaq.com/faq/how_does_one_escape_special_characters_when_writing_sql_queries

Using two quotes to escape to a single quote is a sql standard afaik.
Title: Re: Query for inserting a new record.
Post by: BigChimp on April 25, 2012, 06:51:53 pm
@Goodname: thanks for the PostgreSQL link... I concur with ludo that it's probably for isolated cases only, but good of you to give us the links.

@perhen: glad the problem was solved

BigChimp
Title: Re: Query for inserting a new record.
Post by: ludob on April 25, 2012, 07:05:19 pm
Using two quotes to escape to a single quote is a sql standard afaik.
You are right that double single quotes are a way to escape a single quote on these databases, but try sending the string
Code: [Select]
Bam\'bam to mysql. It'll translate to 'Bam\''bam' and the database will come back with a syntax error at "bam" because \' equals the ' character.
Title: Re: Query for inserting a new record.
Post by: goodname 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.
Title: Re: Query for inserting a new record.
Post by: KpjComp 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.
Title: Re: Query for inserting a new record.
Post by: BigChimp 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
Title: Re: Query for inserting a new record.
Post by: ludob 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, '''');
Title: Re: Query for inserting a new record.
Post by: KpjComp 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. :)
Title: Re: Query for inserting a new record.
Post by: ludob 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.
Title: Re: Query for inserting a new record.
Post by: goodname 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.
Title: Re: Query for inserting a new record.
Post by: BigChimp 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 (http://wiki.lazarus.freepascal.org/SqlDBHowto)
Updates/corrections welcome... as always ;)
TinyPortal © 2005-2018