Sign in to follow this  
Followers 0
faustf

sqlite the last id how get ?

23 posts in this topic

hi guy

but what command i must use for take the last id of table in sqlite ??

Share this post


Link to post
Share on other sites



i must use query with this SELECT LAST(column_name) FROM table_name ? ?? ? ?

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

There are two posibilities but they are not equivalent:

select max(id) from mytable -- will get the ... maximum value of ID unsurprisingly

Use of _SQLite_LastInsertRowID($hDB) will return the last inserted rowid on this connection. Use that right after an insert or replace statement. Here, note that replace is actually "insert or replace" and if the row already exist, it amounts to a delete then an insert, thus changing the rowid if that column is an integer primary key autoincrement.

Anecdotically you can also do:

select id from mytable order by id desc limit 1

but this is the same as the first solution above (using max(id)).

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

If you want to know the ROWID of the most recent insert in the database just use _SQLite_LastInsertRowID. If you want to know the last numerical ID of a specific table just do that:

SELECT MAX(id) FROM table_name

Or other very ungly code:

SELECT LAST (id) AS LastID FROM table_name

OR

SELECT id FROM table_name ORDER BY id DESC LIMIT 1

Hi!


My UDF: NetInfo UDF Play with your network, check your download/upload speed and much more! YTAPI Easy to use YouTube API, now you can easy retrive all needed info from a video. NavInfo Check if a specific browser is installed and retrive other usefull information. YWeather Easy to use Yahoo Weather API, now you can easily retrive details about the weather in a specific region. No-IP UDF Easily update your no-ip hostname(s).

My Script: Wallpaper Changer Change you wallpaper dinamically, you can also download your wallpaper from your website and share it with all!   My Snippet: _ImageSaveToBMPConvert an image to bmp format. _SciteGOTO Open a file in SciTE at specific fileline. _FileToHex Show the hex code of a specified file

Share this post


Link to post
Share on other sites

Note you both that LAST() is not valid for SQLite, unless part of a loaded extension.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Note you both that LAST() is not valid for SQLite, unless part of a loaded extension.

That's true ;)

Hi!


My UDF: NetInfo UDF Play with your network, check your download/upload speed and much more! YTAPI Easy to use YouTube API, now you can easy retrive all needed info from a video. NavInfo Check if a specific browser is installed and retrive other usefull information. YWeather Easy to use Yahoo Weather API, now you can easily retrive details about the weather in a specific region. No-IP UDF Easily update your no-ip hostname(s).

My Script: Wallpaper Changer Change you wallpaper dinamically, you can also download your wallpaper from your website and share it with all!   My Snippet: _ImageSaveToBMPConvert an image to bmp format. _SciteGOTO Open a file in SciTE at specific fileline. _FileToHex Show the hex code of a specified file

Share this post


Link to post
Share on other sites

hi thankz for answer ,

why when i use _SQLite_LastInsertRowID($DB) this return 0 ???

i insert like in example (for understund but nothing )

is probably give 0 because this day dont insert nothing ???

Share this post


Link to post
Share on other sites

so i try the solution in this mode

SELECT id FROM table_name ORDER BY id DESC LIMIT 1

i create this script

Func _lettura_anagrafica()
_SQL_Startup()
  $Db=_SQLite_Open($percorso_Db); Open a permanent disk database
If @error Then
  MsgBox(16+262144, "SQLite Error", "Non posso Aprire il Database!")
  ;Exit -1
EndIf

;_SQLite_Query($Db, "SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;", $hQuery)
;_SQLite_FetchData($hQuery, $last_aRow)
;MsgBox(0,'lultima',$last_aRow[0])

    Local $aRow, $iRows, $iCols
    _SQLite_GetTable2d($Db, "SELECT * FROM anagrafica  ORDER BY ana_id DESC LIMIT 1;", $aRow, $iRows, $iCols)
Local $num_righe= 0,$v=4,$h=1
Do
_GUICtrlListView_AddItem($List1AC, $arow[$h][1],  $num_righe) ;nome ditta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][8], 1, 1) ; mail
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][7], 2, 2) ; telefono
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][9], 3, 3) ; cell
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][2], 4, 4) ; indirizzo
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][3], 5, 5) ; citta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][10], 6, 6) ; fax
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][0], 7, 7) ; coddb
$h=$h+1
$num_righe = $num_righe +1
$v=$v-1
Until $v=0
_SQLite_Close()
_SQLite_Shutdown()

EndFunc

but if insert

ORDER BY id DESC LIMIT 1

give me this error

:\Users\stefano\Documents\programmazione\SANDRO GEST\sandro-gest.au3 (406) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
_GUICtrlListView_AddItem($List1AC, $arow[$h][1],  $num_righe)
_GUICtrlListView_AddItem($List1AC, ^ ERROR
->17:30:49 AutoIT3.exe ended.rc:1

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

faustf,

Returns the ROWID of the most recent insert in the database by this connection

Have you inserted a row during the life of the current connection?

kylomas

edit: response to post #7

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

From the help file:

Returns the ROWID of the most recent insert in the database by this connection

If you for example insert a new item in the database:

_SQLite_Open() 
_SQLite_Exec(-1, "INSERT INTO persons VALUES ('Alice','43');")

When you call _SQLite_LastInsertRowID it will tell to you the id of he last INSERT in the database, in this case the last ID of the table "persons".

P.S. Use the edit button instead of add a new post ;)

Hi!

Edited by Nessie

My UDF: NetInfo UDF Play with your network, check your download/upload speed and much more! YTAPI Easy to use YouTube API, now you can easy retrive all needed info from a video. NavInfo Check if a specific browser is installed and retrive other usefull information. YWeather Easy to use Yahoo Weather API, now you can easily retrive details about the weather in a specific region. No-IP UDF Easily update your no-ip hostname(s).

My Script: Wallpaper Changer Change you wallpaper dinamically, you can also download your wallpaper from your website and share it with all!   My Snippet: _ImageSaveToBMPConvert an image to bmp format. _SciteGOTO Open a file in SciTE at specific fileline. _FileToHex Show the hex code of a specified file

Share this post


Link to post
Share on other sites

faustf,

This is not valid for an insert stmt

ORDER BY id DESC LIMIT 1

Are you checking return codes from your SQLite calls, and/or, running this using the full SCITE editor?

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

no not insert new in this connection therfore not use _SQLite_LastInsertRowID($DB) and use

"SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;

Share this post


Link to post
Share on other sites

a ok kilomas i must do 2 routine ok thankz i try

Share this post


Link to post
Share on other sites

faustf,

If you execute the script from the editor (F5) you will see any SQL errors in the console area.

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

As I said, _SQLite_LastInsertRowID returns the value of the last inserted row ID within the current connection. That value is not stored in the DB so if you close the connection, it's lost forever.

Sorry, I was distracted and my post now sounds retarded.

@faustf,

Again, don't _SQLite_Open and *_Close in every function, only at program startup and termination.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

yea understund therfore dont wanna use _SQLite_LastInsertRowID but use order by etc...... now try to do and soon write :)

Share this post


Link to post
Share on other sites

faustf,

Response to post #13

a ok kilomas i must do 2 routine ok thankz i try

No, you can use a construct like this

if _sqlite_exec(-1,'some SQL stmt...;') <> $sqlite_ok then do your_error_routine()

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

select max(id) from sometable

is more concise and will do as well.

Please note that this does not garanty that you'll get the last [inserted] row id. Autoincrement may exhaust the largest >0 64-bit value and select any unused random value.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

i try in this mode and go

Func _lettura_anagrafica()
_SQL_Startup()
  $Db=_SQLite_Open($percorso_Db); Open a permanent disk database
If @error Then
  MsgBox(16+262144, "SQLite Error", "Non posso Aprire il Database!")
  ;Exit -1
EndIf
_SQLite_Query($Db, "SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;", $hQuery)
While _SQLite_FetchData($hQuery, $1aRow) = $SQLITE_OK
    $sMsg &= $1aRow[0]
WEnd

    Local $aRow, $iRows, $iCols
    _SQLite_GetTable2d($Db, "SELECT * FROM anagrafica;", $aRow, $iRows, $iCols)
Local $num_righe= 0,$v=$sMsg,$h=1
Do
_GUICtrlListView_AddItem($List1AC, $arow[$h][1],  $num_righe) ;nome ditta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][8], 1, 1) ; mail
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][7], 2, 2) ; telefono
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][9], 3, 3) ; cell
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][2], 4, 4) ; indirizzo
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][3], 5, 5) ; citta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][10], 6, 6) ; fax
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][0], 7, 7) ; coddb
$h=$h+1
$num_righe = $num_righe +1
$v=$v-1
Until $v=0
_SQLite_Close()
_SQLite_Shutdown()
EndFunc

How do you would have done

Share this post


Link to post
Share on other sites

It all about you, you can use:

SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;

OR

SELECT MAX(ana_id) FROM anagrafica

P.S. The next time use the [autoit][/autoit] tag to post your code. La prossima volta usa il tag autoit così il tutto è più leggibile. Ciao!


My UDF: NetInfo UDF Play with your network, check your download/upload speed and much more! YTAPI Easy to use YouTube API, now you can easy retrive all needed info from a video. NavInfo Check if a specific browser is installed and retrive other usefull information. YWeather Easy to use Yahoo Weather API, now you can easily retrive details about the weather in a specific region. No-IP UDF Easily update your no-ip hostname(s).

My Script: Wallpaper Changer Change you wallpaper dinamically, you can also download your wallpaper from your website and share it with all!   My Snippet: _ImageSaveToBMPConvert an image to bmp format. _SciteGOTO Open a file in SciTE at specific fileline. _FileToHex Show the hex code of a specified file

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0