B4J Question DBUtils create sqlite file with a 2 field primary Key [SOLVED]

Peter Lewis

Well-Known Member
Licensed User
Longtime User
Hi All

I am trying to create a sqlite file with the primary key being unique from 2 fields

I have tried

B4X: Copy
DBUTILS.CreateTable(sql1, "Joined", S, "game_id,user_ID")

But this does not seem to work

Any Ideas ?

Thank you
 
Last edited:
using multiple columns as a primary key doesn't appear to be addressed in dbutils. i scrolled through the class a little. sqlite requires a special syntax: PRIMARY KEY(column_1,column_2,...). i didn't see it.

dbutils appears to assume a single column. you might have to do it via a direct sql1.exec ("create table ...."). you could try passing array as string ("game_id", "user_ID") as your 4th parameter, but i'm not hopeful. you might try fooling around with putting "game_id,user_ID" in parentheses since that's what sqlite wants to see, but i'm not holding my breath on that one either.

you could have a primary and an index, but it looks like dbutils doesn't create indexes. i've always used sql exec calls, so i can only relate what i saw looking at the dbutils class.
 
Upvote 0
using multiple columns as a primary key doesn't appear to be addressed in dbutils. i scrolled through the class a little. sqlite requires a special syntax: PRIMARY KEY(column_1,column_2,...). i didn't see it.

dbutils appears to assume a single column. you might have to do it via a direct sql1.exec ("create table ...."). you could try passing array as string ("game_id", "user_ID") as your 4th parameter, but i'm not hopeful. you might try fooling around with putting "game_id,user_ID" in parentheses since that's what sqlite wants to see, but i'm not holding my breath on that one either.

you could have a primary and an index, but it looks like dbutils doesn't create indexes. i've always used sql exec calls, so i can only relate what i saw looking at the dbutils class.

I found that constraints work in sql LIte
If seems like in the docs of SQLite you cannot put more than one column in a primary key.
But running this code in SQLiteStudio it did work
Now I just need a way to run it in B4J

B4X: Copy
CREATE TABLE Employee_Master

(first_name TEXT NOT NULL,

last_name TEXT NOT NULL,

CONSTRAINT con_primary_name PRIMARY KEY(first_name,last_name));

I will keep searching
 
Upvote 0
per the sqlite docs:
Optionally, a PRIMARY KEY for the table. Both single column and composite (multiple column) primary keys are supported

are you saying you can't get this sql statement:
Code: Copy
CREATE TABLE table_name(
   column_1 INTEGER NOT NULL,
   column_2 INTEGER NOT NULL,
   ...
   PRIMARY KEY(column_1,column_2,...)
);

to work when you do it b4a-style?:
B4X: Copy
SQL.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")    [B] '[SIZE=4] <-------- only here's where you put the muliple column constraint[/SIZE][/B]

don't make me have to see for myself. you're saying you did sql.ExecNonQuery() and it didn't work?
 
Upvote 0
per the sqlite docs:


are you saying you can't get this sql statement:
Code: Copy
CREATE TABLE table_name(
   column_1 INTEGER NOT NULL,
   column_2 INTEGER NOT NULL,
   ...
   PRIMARY KEY(column_1,column_2,...)
);

to work when you do it b4a-style?:
B4X: Copy
SQL.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")    [B] '[SIZE=4] <-------- only here's where you put the muliple column constraint[/SIZE][/B]

don't make me have to see for myself. you're saying you did sql.ExecNonQuery() and it didn't work?

Yes it did not work but I have other options instead of constraining on the DB I can do it in code. No need to waste more time on this
 
Upvote 0
so, i just did it, and it works. i don't know what the problem is on your end.
B4X: Copy
   If sql.IsInitialized Then
        Log("db created")
    Else
        Log("create failed")
        ExitApplication2( -1 )
    End If
    
    sql.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, PRIMARY KEY(col1,col2))")
 

Attachments

  • primary.png
    primary.png
    16 KB · Views: 528
Upvote 0
When I ran that, it does not come back with any error. The problem is if you then go into sqlitestudio to look at the structure, next to the fields there is NO key symbol and if you go to add a key symbol in sqlitestudio it will reflect
 
Upvote 0
i initialized and created the table (as shown above) using b4x. to view the db, i copied over to my desktop and ran sqlite's utility (sqlite.exe) and asked to see how the db was set up. i attached a screen capture showing me how the db is structured: multiple-column primary keys. am attaching the db. it has to be in a .zip archive for posting purposes. un-zip it and see what you get. i don't know what sqlitestudio is. it may not be compatible with the version of sqlite meant to be used on android. sqlite.exe is 3.7 or 3.9, and i have no problems working with it (for convenience) and then copying db's back and forth between my desktop and devices. (actually, i did just google sqlitestudio to see what it was.)
 

Attachments

Upvote 0
I have just checked your file with SQLite Studio and it shows both keys.

I am going to upgrade all of my SQLite definitions, Thank you
 
Upvote 0
so, i didn't get the impression sqlitestudio was done by members of the sqlite "team" (sqlite being open source). it did claim to be compliant with sqlite3, so i don't why you were having an issue. but sounds like you're off and running (soon).
 
Upvote 0
3.30.1? i think i may be using an older version. thanks for the heads-up
 
Upvote 0
Back
Top