Re: [sqlite] SQLite & JDBC & generated key

[gwenn]

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

[Kees Nuyt]

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

[Kees Nuyt]

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

[Simon Slavin]

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