Sqlite RecNo

[Champak]

'RecNo' is an automatic column inserted as the first column in the databases. I believe it is an index. Why can't I select it? Everytime I try, I get a message that the column doesnt exist, but when I input another column there is no issue. Is there a way to do work based on the 'RecNo'.

 

[JohnOne]

If there are no columns to work on, I doubt it, the column is most likely created when you add an actual content column, else the db would be instantly of maximum size.

[JohnOne]

But I believe you can access it by querying rowid.

[kylomas]

Champak,

You can use "rowid", "oid" or "_rowid_".  See the SQLite doc here for a discussion of this.  See also the doc for "Vacuum".  A quick and dirty example...

expandcollapse popup

#include <ListViewConstants.au3>
#include <GUIConstantsEx.au3>
#include <sqlite.au3>
#include <array.au3>

#AutoIt3Wrapper_Add_Constants=n

_SQLite_Startup()
_SQLite_Open()

Local $sql = 'create table if not exists T1 (Fname, Lname);'
$sql &= ' insert into T1 values("Bob", "Bumbleburger"),("Bubba", "Baddass"),("Barbara","Buster"),("Boogie Man","Boozenhammer");'

_SQLite_Exec(-1, $sql)

Local $aRows, $iRows, $iCols

_SQLite_GetTable2d(-1, 'Select rowid,* from T1', $aRows, $iRows, $iCols)

Local $gui010 = GUICreate('Listview with Row Id''s')
Local $lst010 = GUICtrlCreateListView('Rowid   | First Name   | Last Name', 10, 10, 380, 380, Default, BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES))
For $i = 1 To UBound($aRows) - 1
    GUICtrlCreateListViewItem($aRows[$i][0] & '|' & $aRows[$i][1] & '|' & $aRows[$i][2], $lst010)
Next
GUISetState()

While 1
    Switch GUIGetMsg()
        Case $Gui_Event_Close
            _SQLite_Close()
            _SQLite_Shutdown()
            Exit
    EndSwitch

WEnd



Func ShowSomeDBStuff()

    _SQLite_GetTable2d(-1, 'Select rowid,* from T1', $aRows, $iRows, $iCols)
    _ArrayDisplay($aRows, "DB As Loaded")

    _SQLite_Exec(-1, 'delete from T1 where Fname = "Barbara";')
    _SQLite_GetTable2d(-1, 'Select rowid,* from T1', $aRows, $iRows, $iCols)
    _ArrayDisplay($aRows, "Entry 3 (Barabara) Deleted")

    _SQLite_Exec(-1, 'insert into t1 values("Boomerang","Boom");')
    _SQLite_GetTable2d(-1, 'Select rowid,* from T1', $aRows, $iRows, $iCols)
    _ArrayDisplay($aRows, "New Entry Added...Recno = 5")

    _SQLite_Exec(-1, 'vacuum;')
    _SQLite_GetTable2d(-1, 'Select rowid,* from T1', $aRows, $iRows, $iCols)
    _ArrayDisplay($aRows, "Keys renumbered by vacuum")

EndFunc   ;==>ShowSomeDBStuff

kylomas

Edited December 11, 2015 by kylomas
spelling

[jchd]

Without seeing the table DDL (the schema), it's pretty hard to tell you something really useful. Is it an SQLite table you designed? In this case post the table declaration. Is it produced by someone else? Then run the following statement and post its result: select * from sqlite_master;

You'll find that using a 3rd-party SQLite manager will save 90% of the time you spend on most DB operations. I warmly recommend using SQLite Expert (the freeware version will do).

[Champak]

"Rowid' worked like a charm. Initially it wasn't working when selecting the same row number, then I remember you VAGUELY...lol...mentioning "vacuum" and everything was smooth from there, thanks.

One more thing, I have a slight issue with the following:

_SQLite_Query($DB_GasDriving_Log, "SELECT sum(replace(Cost, '$', '')) FROM Log_Fuel_Travel", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        $TOTALFUELCOST &= $aRow[0]
    WEnd

It works fine, but I was wondering if I can do this without the While loop? Or if I can do any select query that has a specific WHERE condition and get the result directly without doing the While loop. Just trying to reduce code.

[jchd]

*_Query + *_FetchData is a pedestrian solution. Try using _SQLite_GetTable[2d] or *_GetSingleRow.
Feel free to use a WHERE clause in your SQL, of course.

[Champak]

Thanks.