access database on server (godaddy)
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
access database on server (godaddy)
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
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
Re: access database on server (godaddy)
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.
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.
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: access database on server (godaddy)
Thanks Mike,
Not sure what I was doing differently yesterday but got it working today.
Dave
Not sure what I was doing differently yesterday but got it working today.
Dave
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: access database on server (godaddy)
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
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
Thanks
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")
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?database key
database newdbname.splite sees encrypted. please enter the encryption key in order to be able to manage it.
Thanks
-
- VIP Livecode Opensource Backer
- Posts: 9840
- Joined: Sat Apr 08, 2006 7:05 am
- Location: Los Angeles
- Contact:
Re: access database on server (godaddy)
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
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: access database on server (godaddy)
Thanks FourthWorld but i am still getting the same encrypted result using...
Code: Select all
put tData into url("binfile:" & specialFolderPath("documents") & "/newdbname.sqlite")
Re: access database on server (godaddy)
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)...
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)...
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: access database on server (godaddy)
Hi Klaus,
I am first querying data from the online database with...
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...
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
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
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
Thanks for your suggestions
Re: access database on server (godaddy)
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
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
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: access database on server (godaddy)
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.
Re: access database on server (godaddy)
Hi David,
glad you got it working!
Best
Klaus
glad you got it working!
Best
Klaus