So using @Develephant code I was able to get it working.
NOTE: If you are downloading a .db file then trying to add to it with another downloaded .db file it seems to cause problems. Best solution is to create a .db file from scratch and then download what you need to add to it.
Not that anyone will ever need to use this method again but figured i’d post an update just in case.
Thanks!
Do you mean with hard-coded column names, or does it need to be dynamic so you can load any table, analyse the structure and output accordingly?
remote or local (on device)
If local, the file must be in documents or another write-able folder, not resource.
If remote, I have no insights.
Its going to be a local file that is in the documents directory. All I need to do is read the 19 columns I have copy that information and then write that information to basically the same database but one that already has information filled in. so it would look like this;
Database 1:
|test 1| test 2| test 3|
| textA| textB | textC |
| textA| textB | textC |
Database 2:
|test 1| test 2| test 3|
| textD| textE | textF |
| textD| textE | textF |
Then after Database 1 is read and written into Database 2.
Updated Database 2:
|test 1| test 2| test 3|
| textD| textE | textF |
| textD| textE | textF |
| textA| textB | textC |
| textA| textB | textC |
And that’s what I’m going for.
Hi,
Not tested, but something like this should work. I would highly recommend naming your columns without spaces though. Then you can just access the key directly without wrapping it in braces.
local sqlite3 = require( "sqlite3" ) local sf = string.format local path = system.pathForFile( "data\_one.db", system.DocumentsDirectory ) local db\_1 = sqlite3.open( path ) local db\_1\_data = {} for row in db\_1:nrows("SELECT \* FROM my\_table") do table.insert(db\_1\_data, { col1 = row["test 1"], col2 = row["test 2"], col3 = row["test 3"], }) end db\_1:close() local path = system.pathForFile( "data\_two.db", system.DocumentsDirectory ) local db\_2 = sqlite3.open( path ) for i=1, #db\_1\_data do local values = db\_1\_data[i] db\_2:exec(sf("INSERT INTO my\_table VALUES ('%s', '%s', '%s');", values.col1, values.col2, values.col3 )) end db\_2:close()
Again, not tested.
-dev
This might help - https://stackoverflow.com/questions/2359205/copying-data-from-one-sqlite-database-to-another
Hi,
This might work just as well:
local sqlite3 = require( "sqlite3" ) local sf = string.format local db\_1 = sqlite3.open( system.pathForFile( "data\_one.db", system.DocumentsDirectory ) ) local db\_2 = sqlite3.open( system.pathForFile( "data\_two.db", system.DocumentsDirectory ) ) for row in db\_1:nrows("SELECT \* FROM my\_table") do db\_2:exec(sf("INSERT INTO my\_table VALUES ('%s', '%s', '%s')", row["test 1"], row["test 2"], row["test 3"] )) end db\_1:close() db\_2:close()
-dev
Unfortunately that does not seem to be working. I don’t get any errors but nothing happens. My actual database columns dont have spaces btw that was just an example. also I have 19 rows… The following is how I implemented the code. Let me know if I’m doing something wrong… P.S. I did try both methods you listed.
local sqlite3 = require( "sqlite3" ) local sf = string.format local path = system.pathForFile( "AllData.db", system.TemporaryDirectory ) local db\_1 = sqlite3.open( path ) local db\_1\_data = {} for row in db\_1:nrows("SELECT \* FROM loadout") do table.insert(db\_1\_data, { col1 = row["id"], col2 = row["BillingName"], col3 = row["BillingEmail"], col4 = row["BillingAddress"], col5 = row["BillingCity"], col6 = row["BillingState"], col7 = row["BillingZip"], col8 = row["CardType"], col9 = row["CardNumber"], col10 = row["CardName"], col11 = row["CardExp"], col12 = row["CardCVV"], col13 = row["Tax"], col14 = row["ShippingName"], col15 = row["ShippingEmail"], col16 = row["ShippingAddress"], col17 = row["ShippingCity"], col18 = row["ShippingState"], col19 = row["ShippingZip"] }) end db\_1:close() local path = system.pathForFile( "AllData.db", system.DocumentsDirectory ) local db\_2 = sqlite3.open( path ) for i=1, #db\_1\_data do local values = db\_1\_data[i] db\_2:exec(sf("INSERT INTO loadout VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');", values.col1, values.col2, values.col3, values.col4, values.col5, values.col6, values.col7, values.col8, values.col9, values.col10, values.col11, values.col12, values.col13, values.col14, values.col15, values.col16, values.col17, values.col18, values.col19 )) end db\_2:close()Hi,
I’ll try to put together a real-world test. FYI, if any of your columns are number values, you need to use %d in the string format function.
-dev
Hi,
Are your row ids being auto-generated? because if so, don’t collect the id, and replace the first ‘%s’ with null as the value so that the second database handles the new ids.
-dev
Yes they are, I replaced it with null. Still nothing though.
This is a blank version of the database I’m using. https://www.dropbox.com/s/od7o35nda438val/AllData.db?dl=1
Hi,
How are you shipping the database? Is it copied from the resources directory already populated with data to the documents directory and then you create an empty db in the temporary directory to gather new data with?
Why not just write directly to the database in the documents directory. Excuse my questioning, just trying to get an idea of the use case.
-dev
So its created on main.lua and saved to Documents, then the user does stuff and the data is saved to it. They then upload the database file to dropbox.
They then go to another tablet with the app, a new database is then created in that apps documents folder and the user does stuff that is then saved to that new database.
Finally, they will download the first database file using dropbox from the first tablet/app and combine it with the new database file that was created in this second tablet/app. Then they will upload the combined database one last time and hold onto the data from there.
I know its really convoluted and kind of crazy but this is what my client wants.
Btw I’m now getting the error “String expected got nil”… not sure why.
Hi,
Have you checked the validity of the file uploaded to dropbox, using a GUI tool on your desktop?
-dev
Not sure how. I can open the .db file using the db browser application and it does seem to be valid and working. Just cant seem to copy data from it. Is there a way I could download the data into my app somewhere then re-upload it to the newest database?
Hi,
Will this app be specifically between two different devices, or will there be others that need to do the same process?
-dev
Hi,
I’ve sent you a PM with a link to download a test project to take a look at if it helps at all.
-dev
Sounds convoluted to me. Can’t you just use a MySQL backend and then dump out a local db at the end of the process?