Re: [sqlite] primary key autoincrement updation problemincaserefereced
Fixed it. It allows duplicates when stmt reset(sqlite3_reset) missed after sqlite3_step(..) in transaction. Thank you. On Thu, Jan 12, 2012 at 4:27 PM, Simon Slavin wrote: > > On 12 Jan 2012, at 10:52am, Durga D wrote: > > > I have attached sample db. I tried to simulate with test application. > > But, I could not. > > > >Please find the attached db, which has only one table. > > You can't attach things to messages to this list, because we don't want > the list flooded with everyone attaching their problem files rather than > doing their own investigations. However, you could try something like > > SELECT * FROM sqlite_master WHERE name='t0' > > and see which CREATE command you get back. > > Simon. > > ___ > 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] primary key autoincrement updation problemincaserefereced
On 12 Jan 2012, at 10:52am, Durga D wrote: > I have attached sample db. I tried to simulate with test application. > But, I could not. > >Please find the attached db, which has only one table. You can't attach things to messages to this list, because we don't want the list flooded with everyone attaching their problem files rather than doing their own investigations. However, you could try something like SELECT * FROM sqlite_master WHERE name='t0' and see which CREATE command you get back. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key autoincrement updation problemincaserefereced
Simon, Thank you for your quick responses. I have attached sample db. I tried to simulate with test application. But, I could not. Please find the attached db, which has only one table. Based on schema and data, you can identify the duplicate issue. columne 2 and 3 are duplicated. Thanks in advance, Durga. On Thu, Jan 12, 2012 at 3:57 PM, Simon Slavin wrote: > > On 12 Jan 2012, at 10:14am, Durga D wrote: > > > create table if not exists t0 ( > > h integer primary key autoincrement, > > t0info text, unique(t0info)); > > > > t0 table allows duplicate t0info column, when in transaction. > > > > how to avoid duplicates for t0info column. > > If your table was created with that command, then it should not allow > duplicate t0info columns. Did you perhaps create it first without the > 'unique' requirement, and now your 'CREATE TABLE IF NOT EXISTS' command is > doing nothing ? > > Simon. > ___ > 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] primary key autoincrement updation problemincaserefereced
On 12 Jan 2012, at 10:14am, Durga D wrote: > create table if not exists t0 ( > h integer primary key autoincrement, > t0info text, unique(t0info)); > > t0 table allows duplicate t0info column, when in transaction. > > how to avoid duplicates for t0info column. If your table was created with that command, then it should not allow duplicate t0info columns. Did you perhaps create it first without the 'unique' requirement, and now your 'CREATE TABLE IF NOT EXISTS' command is doing nothing ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key autoincrement updation problemincaserefereced
Thank you. create table if not exists t0 ( h integer primary key autoincrement, t0info text, unique(t0info)); t0 table allows duplicate t0info column, when in transaction. how to avoid duplicates for t0info column. Thanks in advance. Durga. On Thu, Jan 12, 2012 at 3:37 PM, Simon Slavin wrote: > > On 12 Jan 2012, at 6:05am, Durga D wrote: > > >Insertion time, I am able to get the rowid from > > sqlite3_last_insert_rowid(); > > > > It's working fine. > > > > Now, t0info record already exists, that time , I should not update > > with latest rowid. I should fetch the corresponding h value, update in t1 > > table. Here, fetching is the problem: > > Yes, this is a problem. If the way your program works may cause it to try > to insert a t0 row which already exists, you do need at least two SQLite > commands. The two common structures are ... > > create table if not exists t0 ( >h integer primary key autoincrement, >t0info text); > > First option: > > INSERT OR IGNORE INTO t0 ... > SELECT h FROM t0 WHERE ... > > Second option: > > SELECT h FROM t0 WHERE ... > > Then see whether you got 0 or 1 row back. If you got 1 for back, the row > already exists and you can use that 'h' value. If not, you do the INSERT, > then use sqlite3_last_insert_rowid(). > > > > > One option is good if your application structure makes 'IF' commands > difficult. The other is good if executing SQLite calls slows it down > unacceptably. > > Simon. > ___ > 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] primary key autoincrement updation problemincaserefereced
On 12 Jan 2012, at 6:05am, Durga D wrote: >Insertion time, I am able to get the rowid from > sqlite3_last_insert_rowid(); > > It's working fine. > > Now, t0info record already exists, that time , I should not update > with latest rowid. I should fetch the corresponding h value, update in t1 > table. Here, fetching is the problem: Yes, this is a problem. If the way your program works may cause it to try to insert a t0 row which already exists, you do need at least two SQLite commands. The two common structures are ... create table if not exists t0 ( h integer primary key autoincrement, t0info text); First option: INSERT OR IGNORE INTO t0 ... SELECT h FROM t0 WHERE ... Second option: SELECT h FROM t0 WHERE ... Then see whether you got 0 or 1 row back. If you got 1 for back, the row already exists and you can use that 'h' value. If not, you do the INSERT, then use sqlite3_last_insert_rowid(). One option is good if your application structure makes 'IF' commands difficult. The other is good if executing SQLite calls slows it down unacceptably. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key autoincrement updation problemincaserefereced
Corrected it. Thank you. insert into t1( hh, t1info) values ((select h from t0 where t0info = ?), ?) On Thu, Jan 12, 2012 at 11:35 AM, Durga D wrote: > Dear Igor, > > Insertion time, I am able to get the rowid from > sqlite3_last_insert_rowid(); > >It's working fine. > > Now, t0info record already exists, that time , I should not update > with latest rowid. I should fetch the corresponding h value, update in t1 > table. Here, fetching is the problem: > > I tried with below code: not getting succ. > > sqlite3_stmt* stmtt1_hhfrom_t0; > > nres = sqlite3_prepare( dbconn, "insert into t1 values (?, ?, ?) > where hh = (select h from t0 where t0info = ?)" , -1, &stmtt1_hhfrom_t0, 0); > > if ( nres != SQLITE_OK ) return 0; > > Thanks in advance, > Durga. > > > On Thu, Jan 12, 2012 at 11:03 AM, Igor Tandetnik wrote: > >> Durga D wrote: >> > Dear Igor, >> > >> >If already toinfo record exists, that time how to get the h >> > (primary key) (instead of rowid)? >> >> An INTEGER PRIMARY KEY column is in fact an alias for rowid. They are one >> and the same. >> -- >> 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] primary key autoincrement updation problemincaserefereced
Dear Igor, Insertion time, I am able to get the rowid from sqlite3_last_insert_rowid(); It's working fine. Now, t0info record already exists, that time , I should not update with latest rowid. I should fetch the corresponding h value, update in t1 table. Here, fetching is the problem: I tried with below code: not getting succ. sqlite3_stmt* stmtt1_hhfrom_t0; nres = sqlite3_prepare( dbconn, "insert into t1 values (?, ?, ?) where hh = (select h from t0 where t0info = ?)" , -1, &stmtt1_hhfrom_t0, 0); if ( nres != SQLITE_OK ) return 0; Thanks in advance, Durga. On Thu, Jan 12, 2012 at 11:03 AM, Igor Tandetnik wrote: > Durga D wrote: > > Dear Igor, > > > >If already toinfo record exists, that time how to get the h > > (primary key) (instead of rowid)? > > An INTEGER PRIMARY KEY column is in fact an alias for rowid. They are one > and the same. > -- > 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] primary key autoincrement updation problemincaserefereced
Durga D wrote: > Dear Igor, > >If already toinfo record exists, that time how to get the h > (primary key) (instead of rowid)? An INTEGER PRIMARY KEY column is in fact an alias for rowid. They are one and the same. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
