access database on server (godaddy)

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
davidchang
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

access database on server (godaddy)

Post by davidchang » Sun Jun 12, 2016 9:17 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: access database on server (godaddy)

Post by Mikey » Mon Jun 13, 2016 2:48 pm

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
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: access database on server (godaddy)

Post by davidchang » Mon Jun 13, 2016 10:12 pm

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

davidchang
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: access database on server (godaddy)

Post by davidchang » Wed Jun 15, 2016 12:12 am

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: access database on server (godaddy)

Post by FourthWorld » Wed Jun 15, 2016 1:13 am

Use binfile instead of file in the local URL.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

davidchang
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: access database on server (godaddy)

Post by davidchang » Wed Jun 15, 2016 1:37 am

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
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: access database on server (godaddy)

Post by Klaus » Wed Jun 15, 2016 11:32 am

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
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: access database on server (godaddy)

Post by davidchang » Wed Jun 15, 2016 11:31 pm

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
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: access database on server (godaddy)

Post by Klaus » Thu Jun 16, 2016 10:29 am

Hi David,

that's what I guessed! 8)

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
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: access database on server (godaddy)

Post by davidchang » Sun Jun 19, 2016 6:33 pm

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
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: access database on server (godaddy)

Post by Klaus » Sun Jun 19, 2016 7:39 pm

Hi David,

glad you got it working!


Best

Klaus

Post Reply

Return to “Databases”