Query for inserting a new record.

[perhen]

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

[Leledumbo]

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+''''+')');

[BigChimp]

Also, try inserting this text in edit1:

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...

[goldenfox]

Try using params

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;

[mch_01]

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.

[goodname]

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

[BigChimp]

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

[ludob]

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.

[goodname]

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

[ludob]

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.

[perhen]

Thank you, all of you. My problem is solved  , and I have got good hints reading the replies.

[ludob]

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.

[goodname]

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.

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.

[BigChimp]

@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

[ludob]

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.