[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
That's not an SQLitespeed feature but indeed a backwards-compatible SQLite feature. (I had this wrong too at some point) You probably already know, but to be clear: In SQL standard, double-quotes indicate identifiers and single quotes indicate string values. While the single quotes are used more or less universally, identifiers often get quoted differently. In Postgres and SQLite the standard is adhered to with Double quotes. MySQL uses a back-tick like this ` (which, by the way, will also work in SQLite due the same backward compatibility) MSSQL likes the square brackets [ and ] around identifiers (which, again, also works in SQLite for the same reason) Further to this, in earlier MySQL and in SQLite you may also use double quotes to denote strings, and it will regard a double-quoted value to be a string if A - it isn't an identifier, or B - used in a place where you can't use an identifier. (You can just imagine the bugs in your SQL that can arise from this!) While all of the above works, you are strongly encouraged to simply do it correctly and use double-quotes for identifiers and single quotes for strings. We have been lobbying for a strict-mode in SQLite where none of these shenanigans are allowed - but that is far easier said than done. On 2016/02/09 5:10 PM, Chris Prakoso wrote: > Actually I've just done it now, in SQLiteSpeed, and it allowed me to use > double-quote as delimiter successfully. > > Regards, > Chris > > On Tue, Feb 9, 2016 at 3:03 PM, Simon Slavin wrote: > >> On 9 Feb 2016, at 12:10pm, Chris Prakoso wrote: >> >>> *insert into test (field1,field2) values (1,"two"),(2,"three")* >> As well as the comments about your software being out of date, you need to >> know that the text delimiter in SQLite is the non-directional single quote >> character normally seen as an apostrophe. You cannot successfully use the >> double quote character or any directional quotes. >> >> Should be >> >> insert into test (field1,field2) values (1,'two'),(2,'three') >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Thanks for the detailed explanation. Regards, Chris On Tue, Feb 9, 2016 at 4:05 PM, R Smith wrote: > That's not an SQLitespeed feature but indeed a backwards-compatible SQLite > feature. (I had this wrong too at some point) > > You probably already know, but to be clear: In SQL standard, double-quotes > indicate identifiers and single quotes indicate string values. > While the single quotes are used more or less universally, identifiers > often get quoted differently. > In Postgres and SQLite the standard is adhered to with Double quotes. > MySQL uses a back-tick like this ` (which, by the way, will also work in > SQLite due the same backward compatibility) > MSSQL likes the square brackets [ and ] around identifiers (which, again, > also works in SQLite for the same reason) > > Further to this, in earlier MySQL and in SQLite you may also use double > quotes to denote strings, and it will regard a double-quoted value to be a > string if A - it isn't an identifier, or B - used in a place where you > can't use an identifier. (You can just imagine the bugs in your SQL that > can arise from this!) > > While all of the above works, you are strongly encouraged to simply do it > correctly and use double-quotes for identifiers and single quotes for > strings. > > We have been lobbying for a strict-mode in SQLite where none of these > shenanigans are allowed - but that is far easier said than done. > > > > On 2016/02/09 5:10 PM, Chris Prakoso wrote: > >> Actually I've just done it now, in SQLiteSpeed, and it allowed me to use >> double-quote as delimiter successfully. >> >> Regards, >> Chris >> >> On Tue, Feb 9, 2016 at 3:03 PM, Simon Slavin >> wrote: >> >> On 9 Feb 2016, at 12:10pm, Chris Prakoso wrote: >>> >>> *insert into test (field1,field2) values (1,"two"),(2,"three")* >>> As well as the comments about your software being out of date, you need >>> to >>> know that the text delimiter in SQLite is the non-directional single >>> quote >>> character normally seen as an apostrophe. You cannot successfully use >>> the >>> double quote character or any directional quotes. >>> >>> Should be >>> >>> insert into test (field1,field2) values (1,'two'),(2,'three') >>> >>> Simon. >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >>> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Hi, I have windowns phone project but one problem . Sqli te select result data type text column turkish characters encoding problem But Android operation system no problem turkish characters Salih Y?cel Mobil Grup Lideri /Mobile Group Leader Univera Bilgisayar Sistemleri San. ve Tic. A.?. Bu elektronik posta ve onunla iletilen b?t?n dosyalar sadece g?ndericisi taraf?ndan almas? ama?lanan yetkili ger?ek ya da t?zel ki?inin kullan?m? i?indir.E?er s?z konusu yetkili al?c? de?ilseniz bu elektronik postan?n i?eri?ini a??klaman?z, kopyalaman?z, y?nlendirmeniz ve kullanman?z kesinlikle yasakt?r ve bu elektronik postay? derhal silmeniz gerekmektedir. UN?VERA A.?. bu mesaj?n i?erdi?i bilgilerin do?rulu?u veya eksiksiz oldu?u konusunda herhangi bir garanti vermemektedir. Bu nedenle bu bilgilerin ne ?ekilde olursa olsun i?eri?inden, iletilmesinden, al?nmasndan ve saklanmas?ndan sorumlu de?ildir. Bu mesajdaki g?r??ler yalnzca g?nderen ki?iye aittir ve UN?VERA A.?.'nin g?r??lerini yans?tmayabilir. Bu e-posta bilinen b?t?n bilgisayar vir?slerine kar?? taranm??t?r. This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any dissemination, forwarding, copying or use of any of the information is strictly prohibited, and the e-mail should immediately be deleted. UNIVERA A.S. makes no warranty as to the accuracy or completeness of any information contained in this message and hereby excludes any liability of any kind for the information contained therein or for the information transmission, reception, storage or use of such in any way whatsoever. The opinions expressed in this message belong to sender alone and may not necessarily reflect the opinions of UNIVERA A.S. This e-mail has been scanned for all known computer viruses. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Prakoso Sent: Tuesday, February 9, 2016 5:09 PM To: SQLite mailing list Subject: Re: [sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates? Yes thank you. My SQLite is the latest, it's the front-end that is outdated, which I have just swiftly corrected. Regards, Chris On Tue, Feb 9, 2016 at 2:52 PM, Richard Hipp wrote: > On 2/9/16, Clemens Ladisch wrote: > > Chris Prakoso wrote: > >> insert into test (field1,field2) values (1,"two"),(2,"three") > >> > >> SQL Error: near ",": syntax error > > > > You might want to update to a tool that is not years out of date. > > > > What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20) > the INSERT statement would only take a single row in the VALUES > clause. He is suggesting that you are using a version of SQLite that > is 4 year old or older and therefore lacks this feature. > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Chris Prakoso wrote: > insert into test (field1,field2) values (1,"two"),(2,"three") > > SQL Error: near ",": syntax error You might want to update to a tool that is not years out of date. Regards, Clemens
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Ok. Thanks for the reminder. Regards, Chris On Tue, Feb 9, 2016 at 3:18 PM, Richard Hipp wrote: > On 2/9/16, Chris Prakoso wrote: > > Actually I've just done it now, in SQLiteSpeed, and it allowed me to use > > double-quote as delimiter successfully. > > > > That is supported for backwards compatibility. I originally put in > support for double-quoted string literals to be compatible with MySQL > 3.5. I have long since regretted that decision. You are encouraged > to use single-quotes for string literals, as double-quotes have a very > different meaning that can lead to subtle bugs. > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Actually I've just done it now, in SQLiteSpeed, and it allowed me to use double-quote as delimiter successfully. Regards, Chris On Tue, Feb 9, 2016 at 3:03 PM, Simon Slavin wrote: > > On 9 Feb 2016, at 12:10pm, Chris Prakoso wrote: > > > *insert into test (field1,field2) values (1,"two"),(2,"three")* > > As well as the comments about your software being out of date, you need to > know that the text delimiter in SQLite is the non-directional single quote > character normally seen as an apostrophe. You cannot successfully use the > double quote character or any directional quotes. > > Should be > > insert into test (field1,field2) values (1,'two'),(2,'three') > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Yes thank you. My SQLite is the latest, it's the front-end that is outdated, which I have just swiftly corrected. Regards, Chris On Tue, Feb 9, 2016 at 2:52 PM, Richard Hipp wrote: > On 2/9/16, Clemens Ladisch wrote: > > Chris Prakoso wrote: > >> insert into test (field1,field2) values (1,"two"),(2,"three") > >> > >> SQL Error: near ",": syntax error > > > > You might want to update to a tool that is not years out of date. > > > > What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20) > the INSERT statement would only take a single row in the VALUES > clause. He is suggesting that you are using a version of SQLite that > is 4 year old or older and therefore lacks this feature. > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
On 9 Feb 2016, at 12:10pm, Chris Prakoso wrote: > *insert into test (field1,field2) values (1,"two"),(2,"three")* As well as the comments about your software being out of date, you need to know that the text delimiter in SQLite is the non-directional single quote character normally seen as an apostrophe. You cannot successfully use the double quote character or any directional quotes. Should be insert into test (field1,field2) values (1,'two'),(2,'three') Simon.
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
On 2016/02/09 1:30 PM, Chris Prakoso wrote: > Hi Clemens, > > Thanks for your reply. I've tried to use raw SQL but it didn't work > either. Do you have any SQLite front-end that you use? If I may suggest, try SQLitespeed (http://sqlc.rifin.co.za/) and add your DB file, open it and then use the buttons on the far right (such as "Insert", "Update", "Delete", etc.) to produce the SQL that will insert to or update the selected table. You can then see the correct syntax and expand on it to add your own values etc. You could also right-click on the results list and select from the menu "Row Actions"-->"Show UPDATE SQL for changing this row" or "Show INSERT SQL for copying this row" etc. There are a myriad more auto-SQL things in there, perhaps a good option if you are learning SQL still. You could also right-click in a query and then select "Copy as Code"-->"Java"/"C#"/ whatever you like, or add your own codify settings for your preferred language to take the pain out of adapting queries to code. The INSERT and UPDATE functions you described should really work in SQL, so perhaps it's a small syntax error or such preventing success. (PS: This list is not a support group for SQLitespeed or any other gui, you may use its own bug/query reporter from the menu to get help there) Cheers, Ryan
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Thanks Ryan, I will definitely try it. I'm ok with raw SQL, just not familiar with the odd ones like this multiple rows update. Thanks a lot, Chris On Tue, Feb 9, 2016 at 12:42 PM, R Smith wrote: > > > On 2016/02/09 1:30 PM, Chris Prakoso wrote: > >> Hi Clemens, >> >> Thanks for your reply. I've tried to use raw SQL but it didn't work >> either. Do you have any SQLite front-end that you use? >> > > If I may suggest, try SQLitespeed (http://sqlc.rifin.co.za/) and add your > DB file, open it and then use the buttons on the far right (such as > "Insert", "Update", "Delete", etc.) to produce the SQL that will insert to > or update the selected table. You can then see the correct syntax and > expand on it to add your own values etc. > > You could also right-click on the results list and select from the menu > "Row Actions"-->"Show UPDATE SQL for changing this row" or "Show INSERT SQL > for copying this row" etc. > There are a myriad more auto-SQL things in there, perhaps a good option if > you are learning SQL still. > > You could also right-click in a query and then select "Copy as > Code"-->"Java"/"C#"/ whatever you like, or add your own codify settings for > your preferred language to take the pain out of adapting queries to code. > > The INSERT and UPDATE functions you described should really work in SQL, > so perhaps it's a small syntax error or such preventing success. > > > (PS: This list is not a support group for SQLitespeed or any other gui, > you may use its own bug/query reporter from the menu to get help there) > > Cheers, > Ryan > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Chris Prakoso wrote: > My question is whether anybody had successfully implemented multiple > rows Insert/Update. This is possible in SQL: INSERT INTO MyTable(ID, Value) VALUES (1, 'hello'), (2, 'world'); UPDATE MyTable SET Value = 'the same value' WHERE ID IN (1, 2); -- rather verbose; better use two simple UPDATEs: UPDATE MyTable SET Value = CASE ID WHEN 1 THEN 'new A' WHEN 2 THEN 'new B' END WHERE ID IN (1, 2); Regards, Clemens
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
I did a test using simple table, and entering the sql directly using SQLite Administrator: *insert into test (field1,field2) values (1,"two"),(2,"three")* The error I got from the SQLite Administrator is: *2/9/2016 11:29:40 AM: SQL Error: near ",": syntax error * Thanks, Chris On Tue, Feb 9, 2016 at 11:55 AM, Richard Hipp wrote: > On 2/9/16, Chris Prakoso wrote: > > Hi Clemens, > > > > Thanks for your reply. I've tried to use raw SQL but it didn't work > > either. > > Please show us the SQL that you did you. > > > Do you have any SQLite front-end that you use? > > > > The only supported "front-end" (if I correctly understand your > meaning) is the command-line tool "sqlite3.exe" available from the > https://www.sqlite.org/download.html page. There are many other > third-party tools, most of which are GUI-based. You can use them if > you want, and they usually work quite well, but occasionally have > bugs. > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Hi Clemens, Thanks for your reply. I've tried to use raw SQL but it didn't work either. Do you have any SQLite front-end that you use? Regards, Chris On Tue, Feb 9, 2016 at 11:12 AM, Clemens Ladisch wrote: > Chris Prakoso wrote: >> My question is whether anybody had successfully implemented multiple >> rows Insert/Update. > > This is possible in SQL: > > INSERT INTO MyTable(ID, Value) > VALUES (1, 'hello'), >(2, 'world'); > > UPDATE MyTable > SET Value = 'the same value' > WHERE ID IN (1, 2); > > -- rather verbose; better use two simple UPDATEs: > UPDATE MyTable > SET Value = CASE ID > WHEN 1 THEN 'new A' > WHEN 2 THEN 'new B' > END > WHERE ID IN (1, 2); > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
Hi all, I just joined the Mailing List yesterday, so apologise for any mistake I am doing. I'm a .NET (C#) Developer, and at the moment I'm coding a small app with SQLite as the backend database. My question is whether anybody had successfully implemented multiple rows Insert/Update. I've tried to use INSERT OR REPLACE and passing multiple VALUES, but it didn't seem to work. Any pointers would be appreciated. Thanks very much, Chris
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
On 2/9/16, Chris Prakoso wrote: > Actually I've just done it now, in SQLiteSpeed, and it allowed me to use > double-quote as delimiter successfully. > That is supported for backwards compatibility. I originally put in support for double-quoted string literals to be compatible with MySQL 3.5. I have long since regretted that decision. You are encouraged to use single-quotes for string literals, as double-quotes have a very different meaning that can lead to subtle bugs. -- D. Richard Hipp drh at sqlite.org
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
On 2/9/16, Clemens Ladisch wrote: > Chris Prakoso wrote: >> insert into test (field1,field2) values (1,"two"),(2,"three") >> >> SQL Error: near ",": syntax error > > You might want to update to a tool that is not years out of date. > What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20) the INSERT statement would only take a single row in the VALUES clause. He is suggesting that you are using a version of SQLite that is 4 year old or older and therefore lacks this feature. -- D. Richard Hipp drh at sqlite.org
[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?
On 2/9/16, Chris Prakoso wrote: > Hi Clemens, > > Thanks for your reply. I've tried to use raw SQL but it didn't work > either. Please show us the SQL that you did you. > Do you have any SQLite front-end that you use? > The only supported "front-end" (if I correctly understand your meaning) is the command-line tool "sqlite3.exe" available from the https://www.sqlite.org/download.html page. There are many other third-party tools, most of which are GUI-based. You can use them if you want, and they usually work quite well, but occasionally have bugs. -- D. Richard Hipp drh at sqlite.org
