RE: [sqlite] Re: Insert chinese characters in SQLite database
Yes , It worked.
Could you pls tell me whats the difference between adLongVarWChar and
adLongVarChar? Does SQLite support Unicode encoding(UTF-8)?
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 13, 2007 4:13 PM
To: SQLite
Subject: [sqlite] Re: Insert chinese characters in SQLite database
Kalyani Phadke
wrote:
> The following query inserts into the database
>
> Cmd1 .Parameters.Append(Cmd1 .CreateParameter("ipaddress",
> adLongVarChar, adParamInput, 50, ipaddress))
Try adLongVarWChar for parameter type.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Insert chinese characters in SQLite database
Kalyani Phadke
wrote:
The following query inserts into the database
Cmd1 .Parameters.Append(Cmd1 .CreateParameter("ipaddress",
adLongVarChar, adParamInput, 50, ipaddress))
Try adLongVarWChar for parameter type.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: INSERT: how to include CR & LF symbols in a string constant?
It runs fine as long as it is small...
If the text in the field is longer, it seems to crash.
Any idea how to drop that stupid depth limit? Where do I specify that
option?
The official site (http://www.sqlite.org/limits.html) does not explain
this...
On Nov 14, 2007 3:53 PM, Dan Kennedy <[EMAIL PROTECTED]> wrote:
> On Wed, 2007-11-14 at 15:38 +0200, Jevgenijs Rogovs wrote:
> > Thanks, but how do I use those?
> > See, I have a huge file of INSERT statements, which look like this:
> >
> > INSERT INTO sometable VALUES ('blablabla\r\nyadayadayada');
> >
> > What I need is to import this data into SQLite database. If I change all
> > \r\n occurances into the following:
> >
> > INSERT INTO sometable VALUES ('blablabla'||x'0A'||'yadayadayada');
>
> Is it possible that you have mismatched quotes in the
> preprocessed script? The statement above runs fine here.
>
> Dan.
>
>
> > I'm getting "Expression tree is too large (maximum depth 1000)" error...
> >
> > Any ideas on how to cope with this?
> >
> > Thanks again!
> >
> > With best regards,
> > J.R.
> >
> > On Nov 14, 2007 3:19 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> >
> > > Jevgenijs Rogovs <[EMAIL PROTECTED]>
> > > wrote:
> > > > Could someone please assist me with the following: how do I insert a
> > > > string into an SQLite database that contains a CR or LF character?
> > > > C-style escapes (like \r and \n) are not working with SQLite, so how
> > > > can I do this?
> > >
> > > Use parameterized statements - see sqlite3_prepare[_v2],
> sqlite3_bind_*,
> > > sqlite3_step et al.
> > >
> > > Igor Tandetnik
> > >
> > >
> > >
> > >
> -
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > >
> > >
> -
> > >
> > >
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
Re: [sqlite] Re: INSERT: how to include CR & LF symbols in a string constant?
On Wed, 2007-11-14 at 15:38 +0200, Jevgenijs Rogovs wrote:
> Thanks, but how do I use those?
> See, I have a huge file of INSERT statements, which look like this:
>
> INSERT INTO sometable VALUES ('blablabla\r\nyadayadayada');
>
> What I need is to import this data into SQLite database. If I change all
> \r\n occurances into the following:
>
> INSERT INTO sometable VALUES ('blablabla'||x'0A'||'yadayadayada');
Is it possible that you have mismatched quotes in the
preprocessed script? The statement above runs fine here.
Dan.
> I'm getting "Expression tree is too large (maximum depth 1000)" error...
>
> Any ideas on how to cope with this?
>
> Thanks again!
>
> With best regards,
> J.R.
>
> On Nov 14, 2007 3:19 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> > Jevgenijs Rogovs <[EMAIL PROTECTED]>
> > wrote:
> > > Could someone please assist me with the following: how do I insert a
> > > string into an SQLite database that contains a CR or LF character?
> > > C-style escapes (like \r and \n) are not working with SQLite, so how
> > > can I do this?
> >
> > Use parameterized statements - see sqlite3_prepare[_v2], sqlite3_bind_*,
> > sqlite3_step et al.
> >
> > Igor Tandetnik
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: INSERT: how to include CR & LF symbols in a string constant?
I suppose I could try raising the limit of tree depth, or removing it
whatsoever, but how do I do that, provided that I'm doing my import like
this:
sqlite3 mydatabase.db < myhugescript.sql
Tried the -DSQLITE_MAX_EXPR_DEPTH=0 option on sqlite3 command line, but it
doesn't recognize it!
What am I doing wrong?
With best regards,
J.R.
On Nov 14, 2007 3:38 PM, Jevgenijs Rogovs <[EMAIL PROTECTED]> wrote:
> Thanks, but how do I use those?
> See, I have a huge file of INSERT statements, which look like this:
>
> INSERT INTO sometable VALUES ('blablabla\r\nyadayadayada');
>
> What I need is to import this data into SQLite database. If I change all
> \r\n occurances into the following:
>
> INSERT INTO sometable VALUES ('blablabla'||x'0A'||'yadayadayada');
>
> I'm getting "Expression tree is too large (maximum depth 1000)" error...
>
> Any ideas on how to cope with this?
>
> Thanks again!
>
> With best regards,
> J.R.
>
> On Nov 14, 2007 3:19 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> > Jevgenijs Rogovs <[EMAIL PROTECTED]>
> > wrote:
> > > Could someone please assist me with the following: how do I insert a
> > > string into an SQLite database that contains a CR or LF character?
> > > C-style escapes (like \r and \n) are not working with SQLite, so how
> > > can I do this?
> >
> > Use parameterized statements - see sqlite3_prepare[_v2], sqlite3_bind_*,
> >
> > sqlite3_step et al.
> >
> > Igor Tandetnik
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
>
Re: [sqlite] Re: INSERT: how to include CR & LF symbols in a string constant?
Thanks, but how do I use those?
See, I have a huge file of INSERT statements, which look like this:
INSERT INTO sometable VALUES ('blablabla\r\nyadayadayada');
What I need is to import this data into SQLite database. If I change all
\r\n occurances into the following:
INSERT INTO sometable VALUES ('blablabla'||x'0A'||'yadayadayada');
I'm getting "Expression tree is too large (maximum depth 1000)" error...
Any ideas on how to cope with this?
Thanks again!
With best regards,
J.R.
On Nov 14, 2007 3:19 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Jevgenijs Rogovs <[EMAIL PROTECTED]>
> wrote:
> > Could someone please assist me with the following: how do I insert a
> > string into an SQLite database that contains a CR or LF character?
> > C-style escapes (like \r and \n) are not working with SQLite, so how
> > can I do this?
>
> Use parameterized statements - see sqlite3_prepare[_v2], sqlite3_bind_*,
> sqlite3_step et al.
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
[sqlite] Re: INSERT: how to include CR & LF symbols in a string constant?
Jevgenijs Rogovs <[EMAIL PROTECTED]> wrote: Could someone please assist me with the following: how do I insert a string into an SQLite database that contains a CR or LF character? C-style escapes (like \r and \n) are not working with SQLite, so how can I do this? Use parameterized statements - see sqlite3_prepare[_v2], sqlite3_bind_*, sqlite3_step et al. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()
Ok, thanks I haven't seen this function. I'll try it. Mike -Ursprüngliche Nachricht- Von: Igor Tandetnik [mailto:[EMAIL PROTECTED] Gesendet: Montag, 29. Oktober 2007 17:01 An: SQLite Betreff: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid() Michael Ruck wrote: > I don't get an error code. So how should I decide if I should call > sqlite3_last_insert_rowid() or not? :) That's the problem - I don't > have any > indication if an insert > was actually performed or if it was simply ignored sqlite3_changes Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()
Michael Ruck wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored sqlite3_changes Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: INSERT after creating an INDEX
Hi Igor, All prepared statements become invalid when the database schema changes. You need to finalize your statement and prepare it again. Alternatively, if you are using new enough version of SQLite, use sqlite3_prepare_v2 to prepare your statements: it stores the original query string and automatically re-prepares the statement when schema changes. You are absolutely right, thanks for the hint! This solved indeed the problem. Have a nice day, Frank. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: INSERT after creating an INDEX
Frank Fiedler <[EMAIL PROTECTED]> wrote: I have a problem inserting elements into a data base (from process1 accessing the data base) after creating an INDEX on a table (from another process accessing the data base). The error I get is the following: "SQLITE_ERROR : SQL logic error or missing database" All prepared statements become invalid when the database schema changes. You need to finalize your statement and prepare it again. Alternatively, if you are using new enough version of SQLite, use sqlite3_prepare_v2 to prepare your statements: it stores the original query string and automatically re-prepares the statement when schema changes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: INSERT OR REPLACE without new rowid
On 24/04/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Trey Mack <[EMAIL PROTECTED]> wrote: > I'd like to perform an update to a row if it exists (uniquely > identified by 3 text columns), otherwise insert a new row with the > right data. INSERT OR REPLACE looks good, but it generates a new > primary key each time > there is a conflict. If the row exists, I need to keep the original > primary key > (rowid). > > Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ? You can do UPDATE ... WHERE keyfield='xxx'; then use sqlite3_changes to see whether any update has in fact taken place, and run INSERT if not. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - This is a good solution! Thanks for your idea man! ;) -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
[sqlite] Re: INSERT OR REPLACE without new rowid
Trey Mack <[EMAIL PROTECTED]> wrote: I'd like to perform an update to a row if it exists (uniquely identified by 3 text columns), otherwise insert a new row with the right data. INSERT OR REPLACE looks good, but it generates a new primary key each time there is a conflict. If the row exists, I need to keep the original primary key (rowid). Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ? You can do UPDATE ... WHERE keyfield='xxx'; then use sqlite3_changes to see whether any update has in fact taken place, and run INSERT if not. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Insert
But what is the good syntax ? -Message d'origine- De : Igor Tandetnik [mailto:[EMAIL PROTECTED] Envoyé : mercredi 28 février 2007 13:37 À : SQLite Objet : [sqlite] Re: Insert Christian POMPIER <[EMAIL PROTECTED]> wrote: > Could i make to insert 10 000 row in my table with a loop ? Yes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Insert
Christian POMPIER <[EMAIL PROTECTED]> wrote: Could i make to insert 10 000 row in my table with a loop ? Yes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: insert default values - supporting it?
just to show that it does not crash: sqlite> create table test2 (k integer primary key autoincrement); sqlite> insert into test2 default values; sqlite> insert into test2 () values (); sqlite> select * from test2; 1 2 sqlite>
Re: [sqlite] Re: insert default values - supporting it?
I refined the patch. it constructs a list of values with one NULL, but
I don't see how to construct an idList with just the primary key.
also added the grammar rule to recognize both:
insert into default values;
insert into () values ();
anybody completing/correcting the work?
sqlite> insert into test () values ();
SQL error: table test has 3 columns but 1 values were supplied
MF.
cvs diff: Diffing src
Index: src/insert.c
===
RCS file: /sqlite/sqlite/src/insert.c,v
retrieving revision 1.170
diff -u -r1.170 insert.c
--- src/insert.c 19 Jun 2006 03:05:10 - 1.170
+++ src/insert.c 1 Aug 2006 08:26:28 -
@@ -123,6 +123,7 @@
**
**insert into TABLE (IDLIST) values(EXPRLIST)
**insert into TABLE (IDLIST) select
+**insert into TABLE default values
**
** The IDLIST following the table name is always optional. If omitted,
** then a list of all columns for the table is substituted. The IDLIST
@@ -380,7 +381,22 @@
}else{
sqlite3VdbeJumpHere(v, iInitCode);
}
- }else{
+ }else if (pList == 0){
+assert( pColumn == 0 );
+/* This is the case if no data has been supplied and DEFAULT VALUES are
+** to be inserted. a minimal impact approach would be to create here a
+** temporary list of columns containing just the primary key and a
+** temporary list of values containing just a NULL. the rest of the
+** function would remain untouched.
+*/
+Expr *A;
+Token the_null = { (u8*)"NULL", 0, 4 };
+
+A = sqlite3Expr(TK_NULL, 0, 0, _null);
+pList = sqlite3ExprListAppend(0,A,0);
+nColumn = 1;
+
+ } else{
/* This is the case if the data for the INSERT is coming from a VALUES
** clause
*/
Index: src/parse.y
===
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.206
diff -u -r1.206 parse.y
--- src/parse.y 11 Jul 2006 10:42:36 - 1.206
+++ src/parse.y 1 Aug 2006 08:26:28 -
@@ -598,6 +598,10 @@
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F)
VALUES LP itemlist(Y) RP.
{sqlite3Insert(pParse, X, Y, 0, F, R);}
+cmd ::= insert_cmd(R) INTO fullname(X) DEFAULT VALUES.
+{ sqlite3Insert(pParse, X, 0, 0, 0, R);}
+cmd ::= insert_cmd(R) INTO fullname(X) LP RP VALUES LP RP.
+{ sqlite3Insert(pParse, X, 0, 0, 0, R);}
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
{sqlite3Insert(pParse, X, 0, S, F, R);}
cvs diff: Diffing src/ex
[sqlite] Re: insert default values - supporting it?
I'm throwing this here, I assume that it would not be too much work to
complete this patch. the aim is to support the sql92 syntax insert into
default values;
any comments? hints?
thanks in advance,
Mario Frasca.
cvs diff: Diffing src
Index: src/insert.c
===
RCS file: /sqlite/sqlite/src/insert.c,v
retrieving revision 1.170
diff -u -r1.170 insert.c
--- src/insert.c19 Jun 2006 03:05:10 - 1.170
+++ src/insert.c31 Jul 2006 13:04:19 -
@@ -123,6 +123,7 @@
**
**insert into TABLE (IDLIST) values(EXPRLIST)
**insert into TABLE (IDLIST) select
+**insert into TABLE default values
**
** The IDLIST following the table name is always optional. If omitted,
** then a list of all columns for the table is substituted. The IDLIST
@@ -380,7 +381,16 @@
}else{
sqlite3VdbeJumpHere(v, iInitCode);
}
- }else{
+ }else if (pList == 0){
+assert( pColumns == 0 );
+/* This is the case if no data has been supplied and DEFAULT VALUES are
+** to be inserted. a minimal impact approach would be to create here a
+** temporary list of columns containing just the primary key and a
+** temporary list of values containing just a NULL. the rest of the
+** function would remain untouched.
+*/
+nColumn = 0;
+ } else{
/* This is the case if the data for the INSERT is coming from a VALUES
** clause
*/
Index: src/parse.y
===
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.206
diff -u -r1.206 parse.y
--- src/parse.y 11 Jul 2006 10:42:36 - 1.206
+++ src/parse.y 31 Jul 2006 13:04:19 -
@@ -598,6 +598,8 @@
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F)
VALUES LP itemlist(Y) RP.
{sqlite3Insert(pParse, X, Y, 0, F, R);}
+cmd ::= insert_cmd(R) INTO fullname(X) DEFAULT VALUES.
+{sqlite3Insert(pParse, X, 0, 0, 0, R);}
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
{sqlite3Insert(pParse, X, 0, S, F, R);}
Re: [sqlite] Re: Insert triggers
Igor Tandetnik wrote:
nbiggs wrote:
Can an insert trigger cause an update trigger to be fired also? My
triggers are listed below. I want tgr_on_insert to fire
tgr_on_update.
SQLite does not support cascading triggers. Operations performed by a
trigger never cause other triggers to run.
Igor,
This is not true. SQLite supports multiple triggers on the same event
(insert, update, or delete) on a single table and it fires all triggers
associated with any inserts, updates, or deletes that are done by a trigger.
The following sample demonstrates an insert trigger firing an update
trigger on the same table. The ti trigger fires on insert and updates
the f1 field. The second trigger, tu, fires on update and sets the f2
field. The explain output shows that both triggers will fire on an
insert (i.e. they are cascaded), and the select demonstrates that the
date function in the update trigger is executed as expected.
SQLite version 3.3.2
Enter ".help" for instructions
sqlite> create table t(a, f1, f2);
sqlite> create trigger ti after insert on t begin
...> update t set f1 = 1 where a = new.a;
...> end;
sqlite> create trigger tu after update on t begin
...> update t set f2 = date('now') where a = new.a;
...> end;
sqlite> .explain on
sqlite> explain insert into t values(1, NULL, NULL);
addr opcode p1 p2 p3
-- -- --
-
0 Goto0 98
1 Noop0 0
2 OpenPseudo 0 0
3 SetNumColumns 0 3
4 Integer 0 0
5 OpenWrite 1 2
6 SetNumColumns 1 3
7 NewRowid1 0
8 Integer 1 0
9 Null0 0
10Null0 0
11MakeRecord 3 0 bbb
12Dup 1 0
13Dup 1 0
14Insert 0 0
15Insert 1 3 t
16Close 1 0
17ContextPush 0 0
18ResetCount 0 0
19Noop0 0
20Integer 0 0
21OpenRead4 2
22SetNumColumns 4 1
23Rewind 4 30
24Column 4 0
25Column 0 0
26Ne 354 29 collseq(BINARY)
27Rowid 4 0
28FifoWrite 0 0
29Next4 24
30Close 4 0
31OpenPseudo 3 0
32SetNumColumns 3 3
33OpenPseudo 2 0
34SetNumColumns 2 3
35FifoRead0 93
36Dup 0 0
37Dup 0 0
38Integer 0 0
39OpenRead4 2
40SetNumColumns 4 3
41MoveGe 4 0
42Rowid 4 0
43RowData 4 0
44Insert 3 0
45Rowid 4 0
46Column 4 0
47Integer 1 0
48Column 4 2
49MakeRecord 3 0 bbb
50Insert 2 0
51Close 4 0
52Integer 0 0
53OpenWrite 4 2
54SetNumColumns 4 3
55NotExists 4 35
56Column 4 0
57Integer 1 0
58Column 4 2
59MakeRecord 3 0 bbb
60Insert 4 5 t
61Close 4 0
62ContextPush 0 0
63ResetCount 0 0
64Noop0 0
65Integer 0 0
66OpenRead5 2
67SetNumColumns 5 1
68Rewind 5 75
69Column 5 0
70Column 2 0
71Ne 354 74 collseq(BINARY)
72Rowid 5 0
73FifoWrite 0 0
74Next5 69
75Close 5 0
76Integer 0 0
77OpenWrite 5 2
78SetNumColumns 5 3
79FifoRead0 89
80Dup 0 0
81NotExists 5 79
82Column 5 0
83Column 5 1
84String8 0 0 now
85Function1 1 date(-1)
86MakeRecord 3 0 bbb
87Insert 5 5 t
88Goto0 79
89Close 5 0
90ResetCount 1 0
91ContextPop 0 0
92Goto0 35
93
[sqlite] Re: Insert performance metrics
Hi, OK, I am impressed that you can insert 2000 records/second on indexed columns. I have an application that inserts CSV data into an SQLite database, and inserting 15 million records is taking about 2 hours with no indices (this is on a PowerMac Dual G5 2.0 GHz, 1.5 GB RAM), though two fields each require a simple sub-select on to populate. My attempts to improve performance include: * performing all the inserts inside one transaction * using prepared statements * dropping all the indices prior to running * setting sync mode to Normal * setting cache_size to huge values, like 300,000 * creating temp tables in memory Do my times sound reasonable or should I continue to look for optimizations? Any other suggestions? Thanks, Aaron On Sep 2, 2005, at 3:05 PM, D. Richard Hipp wrote: On Fri, 2005-09-02 at 11:59 +0200, Michael Schoen wrote: (1) Multiple Insert Statements We need to insert around 300-500 datasets/sec constantly (24/7) with 8 till 16 fields indexed. So far we are using mysql, not only due to the general dbms speed, but mainly because it has a csv import interface. We figured out, that we can not insert that many data in row, when using SQL statements, 'cause the SQL-parser just takes to much time... If we use the CSV import methods, we gain at least a 30% performance boost. While reading through the sqlite wiki, I found no evidence about any multiple insert statement to decrease the SQL-Parsing time... What would you guys propose? 1. Call sqlite3_prepare() to parse a generic INSERT statement. 2. Call sqlite3_bind() to bind values to the generic statement. 3. Call sqlite3_step() to do the INSERT 4. Call sqlite3_reset() to reset the statement so that it can run again. 5. Go back and repeat steps 2-4 as many times as you like. This allows you to insert many different rows while only running the parser onces. You do get about a 30% speed boost doing this. Out of curiosity, I created a table with 17 columns and 8 indices just to see how fast I could insert into it using SQLite. On my laptop, I could easily do 2000 inserts/second (all in a single transaction) even when parsing each INSERT separately. I would expect better performance on a workstation and a 30% or better performance improvement using the technique described above. On the other hand, performance will fall off logarithmically as the size of the database increases. (2) Indexing: What kind of index implementations are you guys supporting/using? B-Tree, R-Tree, Full-Text? What about Multiple-Column Indexes? Supported? B-tree indices. You can have as many columns in an index as you want. But only the first 31 columns will be used to help speed searches. (On the other hand, who ever creates an index with 32 or more columns?)
