SQL. dbExec(AUTO_INCREMENT)

[fairyoggy]

How to fix it?

can't use (AUTO_INCREMENT)

dbSecurityConnection = dbConnect( 'sqlite', '123.db')
dbExec( dbSecurityConnection, ' CREATE TABLE if not exists `housing` (`houseID` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(houseID)) ' )

it's 2 line in code above

[IIYAMA]

@slapz0r

Afaik that is the mysql syntax.

I can't remember what the syntax was for sql, but probably something like this:

https://www.queryexamples.com/sql/table/sql-create-table-with-primary-key-foreign-key-and-autoincrement/

 

Maybe:

`houseID` INT PRIMARY KEY AUTOINCREMENT

Or

`houseID` AUTOINCREMENT PRIMARY KEY

 

Hmmm, probably trying till you get it right. Still confusing all those different syntaxes...

 

And if that doesn't work out, then you can also manually fix it with a viewer:

https://sqlitebrowser.org/

(Stop the resource)

Edited August 16, 2019 by IIYAMA

[fairyoggy]

1 hour ago, IIYAMA said:

@slapz0r

Afaik that is the mysql syntax.

I can't remember what the syntax was for sql, but probably something like this:

https://www.queryexamples.com/sql/table/sql-create-table-with-primary-key-foreign-key-and-autoincrement/

 

Maybe:

`houseID` INT PRIMARY KEY AUTOINCREMENT

Or

`houseID` AUTOINCREMENT PRIMARY KEY

 

Hmmm, probably trying till you get it right. Still confusing all those different syntaxes...

 

And if that doesn't work out, then you can also manually fix it with a viewer:

https://sqlitebrowser.org/

(Stop the resource)

@IIYAMA

dbExec( dbSecurityConnection, ' CREATE TABLE if not exists `housing` (`houseID` INT PRIMARY KEY AUTOINCREMENT)' )

used this option, but got this error:

Why? Indicated that it (INT PRIMARY KEY)

The second option throws an error with syntax.

 

[IIYAMA]

@slapz0r

hmm try without autoincrement

CREATE TABLE IF NOT exists `housing` (`houseID` INT NOT NULL PRIMARY KEY)

 

or maybe this works as well:

 

CREATE TABLE if not exists `housing` (
  `houseID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT
)

 

 

[Master_MTA]

On 16/08/2019 at 09:49, slapztea said:

above

 

On 16/08/2019 at 18:59, IIYAMA said:

well

i know it's old but i was have same problem and wanna share the solution

first create table gonna be

'CREATE TABLE IF NOT EXISTS FullData ( id INTEGER PRIMARY KEY AUTOINCREMENT , dataName TEXT, Data TEXT)'

like this

and inserting gonna be like this

 "INSERT INTO FullData(dataName,Data) VALUES('Group' , 'test' )" 

 

there is another sol for lazy persons like me

 "INSERT INTO FullData VALUES(NULL,'Group' , 'test' )" 

 

hope it helps

Enjoy

Edited June 25, 2020 by Master_MTA