Working with ROWID

  1. last year

    Michael E

    13 May 2019 Europe (Deutschland, Heggen)
    Edited last year

    I want to write a memory block into a blob with a statement like this

    DB.CreateBlob(<tablename>,<column name>,<rowid>,<MemoryBlock>.Size)

    The affected tables has a primary key of type integer. The primary key has no gaps in the table (that is 1, 2, 3, 4, 5, 6 and so on).

    Does the primary key match the <rowid>?

    dbRowID is a user created index of some kind, not the same as rowid, which is used internally in SQLite.

  2. Emile S

    14 May 2019 Europe (France, Strasbourg)

    @Michael E primary key match the <rowid>?

    They exist to access an already created Record. At Write a Record time, SQLite create them and store them in the Record into the TABLE (more complex, but you get the idea).

    In short: I do not understand the question.

  3. Edited last year

    Assuming you are working with SQLite, there is a good explanation about rowid and primary key, and the potential alias, in the Xojo docs. Look just below the list of result codes:

    I always create my own primary key since the rowid can not be relied upon.

  4. Michael E

    14 May 2019 Europe (Deutschland, Heggen)
    Edited last year

    I am working with SQLite.
    The documentation of SQLiteBlob.Write has an example which contains the use of the rowid:
    Dim dbRowID As Integer = 1 blob = db.CreateBlob("Team", "Logo", dbRowID, file.Length)

    I've never worked before with a rowid and I want to update a column in an existing database.

  5. dbRowID is a user created index of some kind, not the same as rowid, which is used internally in SQLite.

  6. In answer to your original question, primary key does NOT always match rowid.

    Primary key always remains the same.

    rowid can change.

  7. Dave S

    14 May 2019 San Diego, California USA

    The correct answer... is

    "They match until they don't"

    Since dbRowID is user defined... the user/app can do whatever it wants to that value, causing it to not match the corresponding Primary Key value. When records are deleted, the Primary key values WILL have gaps. And if dbRowID is a simple incremented value. they may or may not continue to match from the point of a deletion forward. And finally, if the intent if for dbRowID and Primary Key TO match... then why bother having both to begin with? (FYI... SQLite creates a ROWID regardless of if you ask it to or not, and USES it as the PK if you indicate an INTEGER PK datatype)

  8. Bob K

    14 May 2019 Testers, Xojo Pro Kansas City

    Work with a primary key as it's the only thing guaranteed to stay the same. RowID is not guaranteed.

    Let the database define the value for that primary key since they're really good at it (and designed to to it). Don't ever think you can do a better job at creating a primary key value than the database (and the millions of people that use them every day can attest to). If you think you can - you are wrong.

  9. Tim H

    14 May 2019 Portland, OR USA In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID

    So, yes, they are the same, because the refer to the same column.

or Sign Up to reply!