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

[Peter Lewis]

Hi All

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

I have tried

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

But this does not seem to work

Any Ideas ?

Thank you

 

[drgottjr]

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.

 

[Peter Lewis]

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

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

 

[drgottjr]

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:

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?:

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?

 

[Peter Lewis]

per the sqlite docs:


are you saying you can't get this sql statement:

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?:

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

 

[drgottjr]

so, i just did it, and it works. i don't know what the problem is on your end.

   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))")

 

[Peter Lewis]

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

 

[drgottjr]

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.)

 

[Peter Lewis]

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

 

[drgottjr]

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).

 

[Peter Lewis]

I upgraded the driver to #AdditionalJar: sqlite-jdbc-3.30.1 and it WORKED

Thank you

 

[drgottjr]

3.30.1? i think i may be using an older version. thanks for the heads-up