Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

I am already doing the prepare_v2/reset/bind_int/step/finalize set.  Before I 
do more intensive testing to confirm my earlier findings about not much time 
difference between using the separate statement compared with using exec, I 
want to confirm that it is true that I can't do bind_int on integer primary 
key.  

If I do "insert into functions values(?,?)" and use bind_int on the primary 
key, nothing is added to the table.  But if I do "insert into functions 
values(NULL,?)", then the primary key got autoincremented even though I didn't 
declare it as autoincrement and data got added properly:

const char *tablesCreateCmd =
// Create table for functions
"CREATE TABLE functions ("
"function_id integer PRIMARY KEY,"
"function_name varchar2(1000) not null"
");\n"

I am already using the latest sqlite3 source.

Thanks,
Bella

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marian Cascaval
Sent: Wednesday, January 26, 2011 7:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with 
sqlite3_bind_value()?

I'm no expert either in C or in SQLite but what Igor is pointing out is 
extremely important, because I have noticed myself the benefit of using 
sqlite3_prepare_v2() and sqlite3_reset() instead of just using sqlite3_exec() 
within iterations. And this is what SQLite documentation is talking about.

The speed optimization in my C++ program was incredible and the code structure 
was like this (just like Igor's recommendations):

sqlite3_exec(... "BEGIN TRANSACTION" ...);
sqlite3_prepare_v2();
for (int i=0; i<100; i++){
sqlite3_reset();
sqlite3_bind_int();
sqlite3_step();
}
sqlite3_exec(... "COMMIT TRANSACTION" ...);


Sorry if this is too basic and you already knew it, but I felt like sharing my 
basic knowledge :P




Marian Cascaval






From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Wed, January 26, 2011 2:51:38 PM
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with 
sqlite3_bind_value()?

Bella Chan  wrote:
> I am surprised to see that C is slower than Perl when inserting lots of data 
>into DB sequentially as I have 100 columns in a row
> so I have been making 100 bind_int calls while Perl I only need to do execute 
>once.

You are doing something wrong. Are you re-preparing the statement for each row, 
by any chance? Are you grouping your inserts within a transaction? Show some 
code.

> Trying to see if I can use bind_value()
> instead but no clue ho to create the sqlite3_value structure.

sqlite3_bind_value is only useful inside custom functions. In any case, your 
problem lies elsewhere.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

FYI, I finally figured out why it is slow with C because I need to turn off 
autocommit, it is now lightning speed :)
Thanks,
Bella
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

> Parameters are numbered from 1, not from 0. Your first sqlite3_bind_int 
> call fails; the second binds a value for function_id column; the 
> parameter for function_name retains its initial value of NULL. The 
> insert then runs afoul of NOT NULL constraint.

Ah, that fixed it.  Thanks much.
Bella
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Igor Tandetnik]

On 1/26/2011 7:48 PM, Bella Chan wrote:
> I got "19 constraint failed" error from this code:
>
> "CREATE TABLE functions ("
> "function_id integer PRIMARY KEY,"
> "function_name integer not null"
> ");\n"
>
>
>  sqlite3_bind_int(statement,0,i);
>  sqlite3_bind_int(statement,1,i+10);

Parameters are numbered from 1, not from 0. Your first sqlite3_bind_int 
call fails; the second binds a value for function_id column; the 
parameter for function_name retains its initial value of NULL. The 
insert then runs afoul of NOT NULL constraint.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

> Incorrect.  You can bind_int or bind_int64 on an integer primary key column
> just like any other column.
> What does sqlite3_errcode(db) and sqlite3_errmsg(db) say after you run
> sqlite3_step(pStmt) on the above?

I got "19 constraint failed" error from this code:

"CREATE TABLE functions ("
"function_id integer PRIMARY KEY,"
"function_name integer not null"
");\n"

...
  const char *sqlcmd;
  string strcmd, entryname;
  sqlite3_stmt *statement;

  strcmd = "INSERT INTO " + tablename + " VALUES(?,?)";
  sqlcmd = strcmd.c_str();
  if (sqlite3_prepare_v2(db, sqlcmd, -1, , 0) != SQLITE_OK) {
cout << "SQL error on " << sqlcmd << ": " << sqlite3_errmsg(db);
sqlite3_close(db);
exit(1);
  }

  for (int i=start_index; i

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Richard Hipp]

On Wed, Jan 26, 2011 at 6:53 PM, Bella Chan  wrote:

> I want to confirm that it is true that I can't do bind_int on integer
> primary key.
>

Incorrect.  You can bind_int or bind_int64 on an integer primary key column
just like any other column.


>
> If I do "insert into functions values(?,?)" and use bind_int on the primary
> key, nothing is added to the table.


What does sqlite3_errcode(db) and sqlite3_errmsg(db) say after you run
sqlite3_step(pStmt) on the above?


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

I am already doing the prepare_v2/reset/bind_int/step/finalize set.  Before I 
do more intensive testing to confirm my earlier findings about not much time 
difference between using the separate statement compared with using exec, I 
want to confirm that it is true that I can't do bind_int on integer primary 
key.  

If I do "insert into functions values(?,?)" and use bind_int on the primary 
key, nothing is added to the table.  But if I do "insert into functions 
values(NULL,?)", then the primary key got autoincremented even though I didn't 
declare it as autoincrement and data got added properly:

const char *tablesCreateCmd =
// Create table for functions
"CREATE TABLE functions ("
"function_id integer PRIMARY KEY,"
"function_name varchar2(1000) not null"
");\n"

I am already using the latest sqlite3 source.

Thanks,
Bella

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marian Cascaval
Sent: Wednesday, January 26, 2011 7:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with 
sqlite3_bind_value()?

I'm no expert either in C or in SQLite but what Igor is pointing out is 
extremely important, because I have noticed myself the benefit of using 
sqlite3_prepare_v2() and sqlite3_reset() instead of just using sqlite3_exec() 
within iterations. And this is what SQLite documentation is talking about.

The speed optimization in my C++ program was incredible and the code structure 
was like this (just like Igor's recommendations):

sqlite3_exec(... "BEGIN TRANSACTION" ...);
sqlite3_prepare_v2();
for (int i=0; i<100; i++){
sqlite3_reset();
sqlite3_bind_int();
sqlite3_step();
}
sqlite3_exec(... "COMMIT TRANSACTION" ...);


Sorry if this is too basic and you already knew it, but I felt like sharing my 
basic knowledge :P




Marian Cascaval






From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Wed, January 26, 2011 2:51:38 PM
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with 
sqlite3_bind_value()?

Bella Chan  wrote:
> I am surprised to see that C is slower than Perl when inserting lots of data 
>into DB sequentially as I have 100 columns in a row
> so I have been making 100 bind_int calls while Perl I only need to do execute 
>once.

You are doing something wrong. Are you re-preparing the statement for each row, 
by any chance? Are you grouping your inserts within a transaction? Show some 
code.

> Trying to see if I can use bind_value()
> instead but no clue ho to create the sqlite3_value structure.

sqlite3_bind_value is only useful inside custom functions. In any case, your 
problem lies elsewhere.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Marian Cascaval]

I'm no expert either in C or in SQLite but what Igor is pointing out is 
extremely important, because I have noticed myself the benefit of using 
sqlite3_prepare_v2() and sqlite3_reset() instead of just using sqlite3_exec() 
within iterations. And this is what SQLite documentation is talking about.

The speed optimization in my C++ program was incredible and the code structure 
was like this (just like Igor's recommendations):

sqlite3_exec(... "BEGIN TRANSACTION" ...);
sqlite3_prepare_v2();
for (int i=0; i<100; i++){
sqlite3_reset();
sqlite3_bind_int();
sqlite3_step();
}
sqlite3_exec(... "COMMIT TRANSACTION" ...);


Sorry if this is too basic and you already knew it, but I felt like sharing my 
basic knowledge :P




Marian Cascaval






From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Wed, January 26, 2011 2:51:38 PM
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with 
sqlite3_bind_value()?

Bella Chan  wrote:
> I am surprised to see that C is slower than Perl when inserting lots of data 
>into DB sequentially as I have 100 columns in a row
> so I have been making 100 bind_int calls while Perl I only need to do execute 
>once.

You are doing something wrong. Are you re-preparing the statement for each row, 
by any chance? Are you grouping your inserts within a transaction? Show some 
code.

> Trying to see if I can use bind_value()
> instead but no clue ho to create the sqlite3_value structure.

sqlite3_bind_value is only useful inside custom functions. In any case, your 
problem lies elsewhere.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Igor Tandetnik]

Bella Chan  wrote:
> I am surprised to see that C is slower than Perl when inserting lots of data 
> into DB sequentially as I have 100 columns in a row
> so I have been making 100 bind_int calls while Perl I only need to do execute 
> once.

You are doing something wrong. Are you re-preparing the statement for each row, 
by any chance? Are you grouping your inserts within a transaction? Show some 
code.

> Trying to see if I can use bind_value()
> instead but no clue ho to create the sqlite3_value structure.

sqlite3_bind_value is only useful inside custom functions. In any case, your 
problem lies elsewhere.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

I am surprised to see that C is slower than Perl when inserting lots of data 
into DB sequentially as I have 100 columns in a row so I have been making 100 
bind_int calls while Perl I only need to do execute once.  Trying to see if I 
can use bind_value() instead but no clue ho to create the sqlite3_value 
structure.
Thanks,
Bella
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users