Re: [sqlite] Fine-grainy error report needed
Jay: You are abolutely rigth. All my problem would be avoided if I do a error check in each individual bind. In fact the problem was a SQLITE_RANGE error. Sure that some days is best go to the cinema when the mind is closed. A.J. Millan - Original Message - From: "Jay A. Kreibich" To: "General Discussion of SQLite Database" Sent: Monday, November 16, 2009 4:54 PM Subject: Re: [sqlite] Fine-grainy error report needed > On Mon, Nov 16, 2009 at 11:32:59AM +0100, A.J.Millan scratched on the > wall: >> Martin: >> >> Thanks for the imput. In fact, afther some years using SQLite I'v spent >> almos a weekend because don't remember that the index in the >> sqlite_column_xxx(sqlite3_stmt*, iCol ) where I read the data from the >> source table, "the leftmost column of the result set has the index 0", >> and >> in the bind sentence, "The leftmost SQL parameter has an index of 1" -I >> do >> both in the same loop-. > >> Any way, the question remains, and the method proposed by Owen perhaps is >> good for debug, but not as a standard method. > > Try checking the return codes from sqlite3_bind_xxx(). When you > attempted to bind something to parameter zero, the bind should have > returned SQLITE_RANGE. Error codes only work if you check them. > > Otherwise, the other answers are correct. A quick scan of the code > makes me thing the only column capable of returning SQLITE_MISMATCH > is a ROWID or INTEGER PRIMARY KEY alias, and there can only be one of > those per table, and there can only be one table per INSERT. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > 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] Fine-grainy error report needed
Igor: As usual, you're right. The only problem is that I think that generally, the type of SQL that you use is not too common, at least among those of us who do not have much practice with the language. At least I tend to get that type of expressions away of the SQL query. And I suppose that in my case, the C++ compiler is faster that the SQL engine. Any way, again from a theoretical point of view you are right. If you could inspect my code I'm sure that you become horrified with the convolutions I need because my lack of an in deep SQL knowledge. A.J.Millan - Original Message - From: "Igor Tandetnik" To: Sent: Monday, November 16, 2009 3:29 PM Subject: Re: [sqlite] Fine-grainy error report needed > A.J.Millan wrote: >> Supposing a dBase without constraints (some like this but not limited to >> it): >> >> CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc >> REAL, >> Dm REAL, St INTEGER); >> >> A query to poblate: >> >> "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)" >> >> Then six binds (i = 0 to 5): >> >> sqlite3_bind_xxx (pStmt, i, ...); >> >> and a "step": resp = sqlite3_step(pStmt);. >> >> Then, if resp == SQLITE_MISMATCH (Data type mismatch) >> >> Do is there some way to know the index (i) of the offending bind? > > It's not quite clear what you expect SQLite to report, in general. > Consider: > > insert into tbl(id) values (? + ?); > insert into tbl(id) values (case when ? then ? else ? end); > > Which bind parameter is the offending one? > > Igor Tandetnik > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine-grainy error report needed
On Mon, Nov 16, 2009 at 11:32:59AM +0100, A.J.Millan scratched on the wall: > Martin: > > Thanks for the imput. In fact, afther some years using SQLite I'v spent > almos a weekend because don't remember that the index in the > sqlite_column_xxx(sqlite3_stmt*, iCol ) where I read the data from the > source table, "the leftmost column of the result set has the index 0", and > in the bind sentence, "The leftmost SQL parameter has an index of 1" -I do > both in the same loop-. > Any way, the question remains, and the method proposed by Owen perhaps is > good for debug, but not as a standard method. Try checking the return codes from sqlite3_bind_xxx(). When you attempted to bind something to parameter zero, the bind should have returned SQLITE_RANGE. Error codes only work if you check them. Otherwise, the other answers are correct. A quick scan of the code makes me thing the only column capable of returning SQLITE_MISMATCH is a ROWID or INTEGER PRIMARY KEY alias, and there can only be one of those per table, and there can only be one table per INSERT. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine-grainy error report needed
A.J.Millan wrote: > Supposing a dBase without constraints (some like this but not limited to > it): > > CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc REAL, > Dm REAL, St INTEGER); > > A query to poblate: > > "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)" > > Then six binds (i = 0 to 5): > > sqlite3_bind_xxx (pStmt, i, ...); > > and a "step": resp = sqlite3_step(pStmt);. > > Then, if resp == SQLITE_MISMATCH (Data type mismatch) > > Do is there some way to know the index (i) of the offending bind? It's not quite clear what you expect SQLite to report, in general. Consider: insert into tbl(id) values (? + ?); insert into tbl(id) values (case when ? then ? else ? end); Which bind parameter is the offending one? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine-grainy error report needed
He list: My apology for respond to myself: Perhaps can be the matter a good candidate to be included in the Extended Error Codes? Greetings A.J. Millan - Original Message - From: "A.J.Millan" To: "General Discussion of SQLite Database" Sent: Monday, November 16, 2009 11:32 AM Subject: Re: [sqlite] Fine-grainy error report needed > > Any way, the question remains, and the method proposed by Owen perhaps is > good for debug, but not as a standard method. > > A.J. Millan > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine-grainy error report needed
Martin: Thanks for the imput. In fact, afther some years using SQLite I'v spent almos a weekend because don't remember that the index in the sqlite_column_xxx(sqlite3_stmt*, iCol ) where I read the data from the source table, "the leftmost column of the result set has the index 0", and in the bind sentence, "The leftmost SQL parameter has an index of 1" -I do both in the same loop-. Any way, the question remains, and the method proposed by Owen perhaps is good for debug, but not as a standard method. A.J. Millan - Original Message - From: "Martin Engelschalk" To: "General Discussion of SQLite Database" Sent: Monday, November 16, 2009 10:52 AM Subject: Re: [sqlite] Fine-grainy error report needed > Hi, > > First, the index of the bind variable (second parameter to > sqlite3_bind_, your 'i') must begin with 1, and not 0, see > http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob > > Sqlite does not use strong typing, which means that you can put any data > into any column. > However INTERGER PRIMARY KEY is an exception and must be an integer. > Therefore, the error most probably comes from your 'Id' - column. > > Martin > > A.J.Millan wrote: >> Hi list: >> >> Supposing a dBase without constraints (some like this but not limited to >> it): >> >> CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc >> REAL, >> Dm REAL, St INTEGER); >> >> A query to poblate: >> >> "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)" >> >> Then six binds (i = 0 to 5): >> >> sqlite3_bind_xxx (pStmt, i, ...); >> >> and a "step": resp = sqlite3_step(pStmt);. >> >> Then, if resp == SQLITE_MISMATCH (Data type mismatch) >> >> Do is there some way to know the index (i) of the offending bind? >> >> By the way: I'm trying to make a general function to import (duplicate) >> data >> from an attached table. >> >> Thanks in advance. >> >> A.J.Millan >> >> ___ >> 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] Fine-grainy error report needed
What I usually do is work my way through binding each value to null one at a time until it works, when it does I know I've found the column that was previously the offending one. - and when I say null I should really say "some known value that is within the constraints range on the column". -Since you're in a 'error' condition and likely to abort processing then the fact that this is comparatively slow compared to doing them all at once really doesn't matter. Owen. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of A.J.Millan Sent: Monday, November 16, 2009 9:41 AM To: sqlite-users@sqlite.org Subject: [sqlite] Fine-grainy error report needed Hi list: Supposing a dBase without constraints (some like this but not limited to it): CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc REAL, Dm REAL, St INTEGER); A query to poblate: "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)" Then six binds (i = 0 to 5): sqlite3_bind_xxx (pStmt, i, ...); and a "step": resp = sqlite3_step(pStmt);. Then, if resp == SQLITE_MISMATCH (Data type mismatch) Do is there some way to know the index (i) of the offending bind? By the way: I'm trying to make a general function to import (duplicate) data from an attached table. Thanks in advance. A.J.Millan ___ 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] Fine-grainy error report needed
Hi, First, the index of the bind variable (second parameter to sqlite3_bind_, your 'i') must begin with 1, and not 0, see http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob Sqlite does not use strong typing, which means that you can put any data into any column. However INTERGER PRIMARY KEY is an exception and must be an integer. Therefore, the error most probably comes from your 'Id' - column. Martin A.J.Millan wrote: > Hi list: > > Supposing a dBase without constraints (some like this but not limited to > it): > > CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc REAL, > Dm REAL, St INTEGER); > > A query to poblate: > > "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)" > > Then six binds (i = 0 to 5): > > sqlite3_bind_xxx (pStmt, i, ...); > > and a "step": resp = sqlite3_step(pStmt);. > > Then, if resp == SQLITE_MISMATCH (Data type mismatch) > > Do is there some way to know the index (i) of the offending bind? > > By the way: I'm trying to make a general function to import (duplicate) data > from an attached table. > > Thanks in advance. > > A.J.Millan > > ___ > 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] Fine-grainy error report needed
Hi list: Supposing a dBase without constraints (some like this but not limited to it): CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc REAL, Dm REAL, St INTEGER); A query to poblate: "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)" Then six binds (i = 0 to 5): sqlite3_bind_xxx (pStmt, i, ...); and a "step": resp = sqlite3_step(pStmt);. Then, if resp == SQLITE_MISMATCH (Data type mismatch) Do is there some way to know the index (i) of the offending bind? By the way: I'm trying to make a general function to import (duplicate) data from an attached table. Thanks in advance. A.J.Millan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
