Creating a dynamic database table in sqlite3

Solar2D General Questions/Discussion
#1

Hello everyone!

I’m starting to use sqlite3.

I was looking for a basic function module but did not find anything so I was trying to make it one mine.

But I’m in trouble, if I want to give a dynamic name to the table how can I use a variable in the creation instructions?

something like this:

function M.createTable( myVariableName ) db:exec[[CREATE TABLE "tableName" ( id INTEGER PRIMARY KEY AUTOINCREMENT, level INTEGER UNIQUE, data TEXT NOT NULL );]] end

I would like to replace tableName in myVariableName…

#2

You can simply concatenate strings:

db:exec[[CREATE TABLE]] .. tableName .. [[( id INTEGER PRIMARY KEY AUTOINCREMENT, level INTEGER UNIQUE, data TEXT NOT NULL );]]
#3

Thanks so much!

Cheaper than expected! I was trying the rules on the strings. 

However, I get the following error:

"  unxperted symbol near ‘…’  "

#4

Hi,

You should also be able to do:

local query = string.format("CREATE TABLE %s ( id INTEGER PRIMARY KEY AUTOINCREMENT, level INTEGER UNIQUE, data TEXT NOT NULL );", "tableName") db:exec(query)

-dev

#5

This works well!

Thanks so much

#6

Forgive me if I use this thread but I think you can help me.

I’m trying to encode my data before putting it in the database, like this:

 local json = require("json") local sqlite3 = require("sqlite3") local openssl = require( "plugin.openssl" ) local cipher = openssl.get\_cipher ( "aes-256-cbc" ) local path = system.pathForFile( "game.db", system.DocumentsDirectory ) local db = sqlite3.open( path ) local myPassword = ""--my password local myTable = { --myData } --table to json local data = json.encode(myTable) --cripto i dati prima di memorizzarli local encryptedData = cipher:encrypt ( data, myPassword ) local query = string.format("INSERT INTO %s VALUES (NULL, %d, '%s');", tableName, num, encryptedData)

The fact is that some tables are saved and others do not.

The query fails.

Apparently the new encrypted string contains “[” or “]” characters that create problems…

Is there a way to prevent encrypted data from having problems with the query? One way to make this smooth…

#7

Please do not hijack threads.  This isn’t a generic sqlite3 thread. Please start a new one if your question is not specific to the original question being asked here.

Thanks

Rob

#8

Sorry I thought it was better this way.

I put this on a new threads as required:

https://forums.coronalabs.com/topic/71117-openssl-and-sqlite3/

#9

You can simply concatenate strings:

db:exec[[CREATE TABLE]] .. tableName .. [[( id INTEGER PRIMARY KEY AUTOINCREMENT, level INTEGER UNIQUE, data TEXT NOT NULL );]]
#10

Thanks so much!

Cheaper than expected! I was trying the rules on the strings. 

However, I get the following error:

"  unxperted symbol near ‘…’  "

#11

Hi,

You should also be able to do:

local query = string.format("CREATE TABLE %s ( id INTEGER PRIMARY KEY AUTOINCREMENT, level INTEGER UNIQUE, data TEXT NOT NULL );", "tableName") db:exec(query)

-dev

#12

This works well!

Thanks so much

#13

Forgive me if I use this thread but I think you can help me.

I’m trying to encode my data before putting it in the database, like this:

 local json = require("json") local sqlite3 = require("sqlite3") local openssl = require( "plugin.openssl" ) local cipher = openssl.get\_cipher ( "aes-256-cbc" ) local path = system.pathForFile( "game.db", system.DocumentsDirectory ) local db = sqlite3.open( path ) local myPassword = ""--my password local myTable = { --myData } --table to json local data = json.encode(myTable) --cripto i dati prima di memorizzarli local encryptedData = cipher:encrypt ( data, myPassword ) local query = string.format("INSERT INTO %s VALUES (NULL, %d, '%s');", tableName, num, encryptedData)

The fact is that some tables are saved and others do not.

The query fails.

Apparently the new encrypted string contains “[” or “]” characters that create problems…

Is there a way to prevent encrypted data from having problems with the query? One way to make this smooth…

#14

Please do not hijack threads.  This isn’t a generic sqlite3 thread. Please start a new one if your question is not specific to the original question being asked here.

Thanks

Rob

#15

Sorry I thought it was better this way.

I put this on a new threads as required:

https://forums.coronalabs.com/topic/71117-openssl-and-sqlite3/