On 13 Nov 2012, at 5:41pm, LMHmedchem <lmh_users-gro...@molconn.com> wrote:
> Thanks for the clarification. So my proper syntax for inserts with
> AUTOINCREMENT is one of,
>
> INSERT INTO Structure
> VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
>
> or
>
> INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw)
> VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
>
> or
>
> INSERT INTO Structure(name, filePath, iH1, iH2, iH3, iH4, formula, fw)
> VALUES('phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
Yes, all three will work (unless my old eyes missed something) as will several
other formulations. See the diagram on this page:
<http://www.sqlite.org/lang_insert.html>
> As it happens, some of the text strings that will be added to the database
> have single quotes,
>
> N,N'-dimethylethylenediamine
>
> do I need to do anything different for these, such as to escape the single
> single-quote in some way? There will never be double quotes, but there could
> be any number of single quotes.
>
> N,N',N''-trimethylbis(hexamethylene)triamine
Yes, if you are passing entire SQL commands in as text, you absolutely do need
to worry about this. You can write your own code which will double the quote:
<http://www.sqlite.org/faq.html#q14>
As an example, the following is a valid way to insert the above formulation:
INSERT INTO Structure(name)
VALUES('N,N'',N''''-trimethylbis(hexamethylene)triamine');
Doubling single quotes is the only thing you should need to do to a
conventional C string. You could write a standard routine to do it and call it
for all string values you are concatenating into a SQL command. However, if
instead of passing entire commands you are using binding to bind a string to a
parameter of the INSERT command, then you do not need to escape the string
you're passing.
By the way, can I ask what iH2, iH3, iH4 are ? I think I've figured out iH1.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
