How do you use DBUtils to create an INTEGER PRIMARY KEY?

[rleiman]

Hi Everyone,

Can you tell me how to use DBUtils to create an INTEGER PRIMARY KEY as the primary key so it will auto-increment?

If that is not included with DBUtils can you tell me what additional coding I need to add along with the existing DBUtils table create code?

Thanks.

 

[netchicken]

This isn't elegant, but I use it to generate the next number for the ID field

Cursor1 = SQL1.ExecQuery("SELECT ID FROM database")   
   If Cursor1.RowCount > 0 Then
   
   For i = 0 To Cursor1.RowCount - 1
      Cursor1.Position = i
      Dim NewID As Int
      newID = cursor1.GetInt("ID")
   Next
   End If
   
   newID = newID +1

then pop newID into your DB

SQL1.ExecNonQuery("INSERT INTO database VALUES('" & newID & "', etc ...

 

[eps]

Or something like singleexec, select max id, no need for a loop then add 1 to the result. It may be possible to put a trigger on the table but that is on the DB side.. Certainly possible in Oracle.


I can post code later if needed for singleexecsql code, but it should be possible to work out.

 

[klaus]

@netchicken

You don't need a loop to do it, use simply:

newID = SQL1.ExecQuerySingleResult(SELECT max(col) FROM database) + 1

Beginner's Guide page 145.

Best regards.

 

[rleiman]

Hi Everyone,

Thanks for all the replies.

I come from an Oracle background and used to use a sequence for doing auto-incrementing.

I found out SQLite has auto-incrementing. Could that be added to the DBUtils CreateTable sub routine?

 

[vb1992]

12.4 Database example program 144 Basic4Android Beginner's Guide
Table creation:

CREATE TABLE TableName ( ID INTEGER PRIMARY KEY, Col1 TEXT, Col2 FLOAT )
Creates a table with the name 'TableName' and three columns:
Column Index Name Variable Type
1 ID INTEGER PRIMARY KEY
2 Col1 TEXT
3 Col2 FLOAT
Different available data types:
[B]INTEGER PRIMARY KEY is a special variable type used for identifiers ID's. It is a long integer 
value beginning with 1 and it is incremented by one each time a new data set, with a NULL 
parameter for this column, is added to the database.[/B]
INTEGER is a 64-bit signed integer number.
FLOAT is a 64-bit IEEE floating point number.
TEXT  is a string.
BLOB Binary Large OBject, the value is stored exactly as it was input.
NULL
SQL1.ExecuteNonQuery("CREATE TABLE TableName(ID INTERNAL PRIMARY KEY, Col1 TEXT, 
Col2 FLOAT")

Looks like DBUTILS is (not) doing that already

If field = PrimaryKey Then sb.Append(" PRIMARY KEY")

you would have to change that line to

If field = PrimaryKey Then sb.Append(" PRIMARY KEY AUTOINCREMENT")

 

[BPak]

I use - last_insert_rowid() which is SQLite

Sub FillSimpleData
   Dim RowID As Long
   SQL1.ExecNonQuery("INSERT INTO table1 VALUES('abc', 1, 2)")
   RowID = SQL1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM table1")
   Log("RowID: " & RowID)
   SQL1.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, ?)", Array As Object("def", 3, 4))
   RowID = SQL1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM table1")
   Log("RowID: " & RowID)
End Sub