access database on server (godaddy)

[davidchang]

Hi,

I have a MySQL database currently hosted on godaddy. I want to be able to access and copy that database into the app on loading. This way the user can access the most current version of the database because it will be updated daily. But I can't seem to connect to the database on godaddy servers from inside the app. Is this possible? Any suggestions?

Thanks

[Mikey]

Just have the app access the url of the db to load it onto the local machine/device. The things you will have to ensure is that the db is at rest when the user downloads it, so you might want to have it copied/archived periodically, and have the client download that archive
We do this a different way, but similarly, using dropbox, but then we have also sped things up by having update files that are smaller than the full db that are downloaded to the client and then applied.

[davidchang]

Thanks Mike,
Not sure what I was doing differently yesterday but got it working today.
Dave

[davidchang]

Hi,
new problem... not sure if i should start another post... but I can now query the entire database into tData then save it using

Code: Select all

put tData into url("file:" & specialFolderPath("documents") & "/newdbname.sqlite")

It appears to save the db to my computer but when I attempt to connect to that new database it results in an error. When I try to open it directly from my computers drive it give the following error message

database key
database newdbname.splite sees encrypted. please enter the encryption key in order to be able to manage it.

So it appears that the database is automatically being encrypted on saving. Can I not do that? or can I set the password? or is there a better way to save the database in the first place?

Thanks

[FourthWorld]

Use binfile instead of file in the local URL.

[davidchang]

Thanks FourthWorld but i am still getting the same encrypted result using...

Code: Select all

put tData into url("binfile:" & specialFolderPath("documents") & "/newdbname.sqlite")

[Klaus]

Are you sure "tData" contains a valid SQLite database file?
You were talking about MySQL!

How did you fill "tData" with data from the online database?
What SQL command did you use in Livecode?

Please post your script(s)...

[davidchang]

Hi Klaus,

I am first querying data from the online database with...

Code: Select all

put revOpenDatabase("MySql", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
put "table_name" into tTableName
put "SELECT * FROM " & tTableName into tSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

So you are right that this first step is a MySQL database. I store the query result in tdata and can display the entire table in a field so I know querying from the online database is ok. Now, I could have the user just query the online database every time but I feel that its better to copy that database into the app then query from there. So I'm trying to copy that database to the app with...

Code: Select all

put tData into url("binfile:" & specialFolderPath("documents") & "/newdbname.sqlite")

set the defaultfolder to specialFolderPath("documents")   
put specialFolderPath ("documents") & "/newdbname.sqlite" into tDatabasePath
put revOpenDatabase ("sqlite", tDatabasePath,tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult   

I was switching to sqlite here because that is what I have used in app in the past with SQLite Manager. But as you suggested this might be the problem. However, at this point the database connection appears to be good but I get an "undefined SQLite error" message when querying. Also, when trying to open the copied database in SQLite manager from my computer I get an encryption key prompt.

Thanks for your suggestions

[Klaus]

Hi David,

that's what I guessed!

You are fetching the pure DATA from the database and so tData is NOT a (SQLite) database file,
that you can simply save on the users hd!

It is just a TAB and CR delimited TEXT list of all the data from the MySQL database!

Now you need to create an SQLite database file on the users hd and import all these
data into the newly created database file.


Best

Klaus

[davidchang]

Thanks, got it working. I was hoping to be able to do a quick copy and save of the database, hence the approach I initially tried. However, I've settled for saving the hosted table into a txt file then looping SQL queries by line to duplicate the table into the device's database. There is probably a more efficient way, I tried SQL bulk insert without success, but it works nonetheless. Thanks again.

[Klaus]

Hi David,

glad you got it working!


Best

Klaus