Handling Exclusion of "rowid" Column in RichClient Database Iterations

[tmighty2]

Hello!

I am facing a persistent challenge where the "rowid" column is consistently excluded from the column iterations in RichClient. This exclusion forces me to use less straightforward methods to handle it, which complicates my code unnecessarily. Here’s how I currently iterate through the columns:

Public Sub TellCols(ByRef uCn As cConnection)
    Dim nDB As cDataBase
    Dim nTbl As cTable
    Dim nCol As cColumn
    
    For Each nDB In uCn.DataBases
        For Each nTbl In nDB.Tables
            Debug.Print "Table: " & quote(nTbl.Name)
            Debug.Print "Cols: "
            
            For Each nCol In nTbl.Columns
                Debug.Print vbTab & quote(nCol.Name)
                If IsEqual(nCol.Name, "rowid") Then
                    Debug.Assert False'This line is never hit
                End If
            Next
        Next
    Next
End Sub

This setup omits "rowid" from the listing, making it difficult for me to handle database structure and field types as seamlessly as I'd like. To work around this, I have to "smuggle" "rowid" back into the mix, which overcomplicates what should be a straightforward task.

Does anyone have insights on why "rowid" might be omitted or advice on how to include it in the iteration without resorting to cumbersome workarounds? Any suggestions for a more elegant solution would be greatly appreciated.

Thank you!

[wqweto]

Is rowid part of your CREATE TABLE statement? I thought it was automagically created i.e. one can safely assume every table has rowid as first column which is not hard to account for in any DDL too I guess.

[Arnoutdv]

The rowid is maybe physically a column of each table and can be used as one in queries.
When you create a new table then it’s automatically there

https://www.sqlite.org/autoinc.html

https://www.sqlite.org/c3ref/last_insert_rowid.html

[Phill.W]

Does anyone have insights on why "rowid" might be omitted or advice on how to include it in the iteration without resorting to cumbersome workarounds? Any suggestions for a more elegant solution would be greatly appreciated.

You don't mention which DBMS you're using, but I suspect it's Oracle Database.

If so, then Our Friends in Redwood City probably don't want to messing with it!

ROWID doesn't show up as a "column" in your table.
It's more like "metadata" about the table (and, actually, the address of the row in the data block in the data file in the tablespace).

ROWID can change (with ROW MOVEMENT ENABLED) so you probably shouldn't be trying to [store and] use them in your application code.
Don't rely on updating rows based on their retrieved ROWID values. You might get away with it inside a single transaction, but you're sailing mighty close to wind even doing that.

Regards, Phill W.

[Arnoutdv]

TS is using vbRichClient which comes with SQLite

[tmighty2]

No, rowid is not part of my CREATE TABLE statement.
It is automatically added when I create a table using RC6.
It is just not included in the for each...
I have been using rowid as an identifier for UPDATE and DELETE statements ever since.

[Arnoutdv]

No it’s an implicitly available column/field.
What is you want to achieve?

[Zvoni]

If you don't have an explicit Integer-PrimaryKey, the only way to access the Value of "rowid" is to explicitely SELECT it in a SELECT statement.

rowid doesn't turn up in any calls to Metadata like sqlite_master or PRAGMA table_info('SomeTable')

This begs the question: Why do you want to read it out?
In that sense, rowid is NOT a column. Its values are stored in a BTree, with the rowid as Key (and probably the tablename).
It's more like an Index

Create an Integer Primary Key and be done with it.
Because in that case, THAT Integer-PK becomes an ALIAS for rowid

CREATE TABLE t(ID INTEGER PRIMARY KEY, MyField TEXT);
/*Add some Rows*/
SELECT rowid, ID, MyField FROM t
/*returns - NOTE THE COLUMN-NAMES!! */
ID, ID, MyField

EDIT: Forgot: This only works if you DON'T use AutoIncrement for the Integer-PK (and you don't need AutoIncrement in any case)