Re: [sqlite] SQLite & JDBC & generated key
Thanks for your replies. I will add a tweak to ignore column access by name when running "SELECT last_insert_rowid();" On Thu, Apr 5, 2012 at 2:17 AM, Kees Nuyt wrote: > On Wed, 4 Apr 2012 21:08:24 +0200, gwenn wrote: > >> 2) Do you know if there are other bindings that implement/support >> this kind of feature ? > > I almost forgot to mention: > > SELECT last_insert_rowid(); > > http://www.sqlite.org/lang_corefunc.html > > -- > Regards, > > Kees Nuyt > > ___ > 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] SQLite & JDBC & generated key
On Wed, 4 Apr 2012 21:08:24 +0200, gwenn wrote: > 2) Do you know if there are other bindings that implement/support > this kind of feature ? I almost forgot to mention: SELECT last_insert_rowid(); http://www.sqlite.org/lang_corefunc.html -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & JDBC & generated key
On Wed, 4 Apr 2012 21:08:24 +0200, gwenn wrote: > * In JDBC API, there is a method to retreive the generated key during an > insert: >http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() > * With SQLite API, there is: sqlite3_last_insert_rowid. > Let suppose that: > - the primary key is correctly declared to make it an alias for the rowid, > - and the connection is not shared. > > 1) Do you know how to retreive the column name of the primary key (the > table name is not known) ? The table name is know, it is in sqlite_master. Besides, the program should know against which table it just performed an INSERT statement. With that table name, you can introspect the database to retrieve the column name of the primary key: PRAGMA table_info(TABLENAME); In a shell script: for tbl in $( \ printf "SELECT name FROM sqlite_master WHERE type='table';\n" \ | sqlite3 dbfile \ ) do printf "PRAGMA table_info(%s);" "$tbl" \ | sqlite3 dbfile | gawk -v t="$tbl" '{printf "%s|%s\n",t,$0}' done I leave it as an exercise to the reader to convert that to java. Actually, you may not need the name, as that given name for the INTEGER PRIMARY KEY is just an alias for ROWID, and the name ROWID would still work. > 2) Do you know if there are other bindings that implement/support this > kind of feature ? As far as I can tell all required primitives are available, it's just a matter of programming. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & JDBC & generated key
On 4 Apr 2012, at 8:08pm, gwenn wrote: > * In JDBC API, there is a method to retreive the generated key during an > insert: > http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() > * With SQLite API, there is: sqlite3_last_insert_rowid. > Let suppose that: > - the primary key is correctly declared to make it an alias for the rowid, > - and the connection is not shared. > 1) Do you know how to retreive the column name of the primary key (the > table name is not known) ? You can pluck the primary key from the CREATE TABLE command you can find in sqlite_master, though you'll have to parse it out from there. However, note that the primary key may be more than one column: CREATE TABLE something (column1, column2, column3, PRIMARY KEY (column1, column2)) though this shouldn't happen under the constraints you gave above. > 2) Do you know if there are other bindings that implement/support this > kind of feature ? Can't think of any advantage to it. Unless the programmer defined a column called _rowid_ I can always use that, and there's too much likelihood of someone else using a compound key as above. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite & JDBC & generated key
* In JDBC API, there is a method to retreive the generated key during an insert: http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() * With SQLite API, there is: sqlite3_last_insert_rowid. Let suppose that: - the primary key is correctly declared to make it an alias for the rowid, - and the connection is not shared. 1) Do you know how to retreive the column name of the primary key (the table name is not known) ? 2) Do you know if there are other bindings that implement/support this kind of feature ? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users