Hi, I’m new to databases. I’ve got the .sql file to work with, but as far as I can see Corona sdk works only with .sqlite. When I try to load data from that db I have “Failed to parse error message: file is encrypted or is not a database” error message.
So what’s my options? Can I work with .sql on Corona or should I convert it somehow to sqlite?
I don’t know what kind of SQL database saves files in .sql file extension. I guess it could be anything. Bottom line is that Corona supports sqlite only so if its not sqlite you need to convert it. The extension is not important though, I do not name my sqlite databases .sqlite.
I see, I guess the problem is with the encryption, but not with the extension.
Do you know any tools to decrypt sql on mac OSX?
Sorry I don’t.
I think first you need to know what format the database is, if its really sqlite. Or are you sure its sqlite?
Get some app to inspect sqlite databases and try to open it. I use this Firefox extension that works well: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
So I’ve installed the addon as you recommended, thanks. It returns the same error that file is encrypted or not database. May it be because it is not sqlite file? what message pops up when you try get non-sqlite file there?
Got the same message when I tried to open an MP3 file as sqlite.
Crap. I got to know that the server uses MySql and the file is MySql accordingly. I’ve found some tutorials on how to work with Corona sdk and MySql, which is possible, but would be quite crazy.
Thanks for your help! If you have some useful materials on how to adjust Corona to work with MySql it would be great if you post it.
.sql extension is usually the extension you get when you export a database out of MySQL. If my hunch is right it would be a simple text file actually and it would be lines and lines of SQL statements such as CREATE, INSERT etc. Can you please try to open the file in a plain text editor and confirm whats in it?
If it is a mysql export as I’m guessing it might be then you can get it into sqlite. Lets confirm first what is in the file.
yep, it’s the text file
it goes like this:
DROP TABLE IF EXISTS `dle_admin_logs`; CREATE TABLE `dle_admin_logs` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `name` varchar(40) NOT NULL DEFAULT '', `date` int(11) unsigned NOT NULL DEFAULT '0', `ip` varchar(16) NOT NULL DEFAULT '', `action` int(11) NOT NULL DEFAULT '0', `extras` text NOT NULL, PRIMARY KEY (`id`), KEY `date` (`date`) ) ENGINE=MyISAM AUTO\_INCREMENT=22867 /\*!40101 DEFAULT CHARSET=cp1251 \*/;Great! Just as I thought. Now tell us more about what you need to do. Is this a one time import where you make a mobile app based on this data structure (and possibly data) given to you or will you have to exchange info back and forth with the source of this .sql file etc? Depending on what your ultimate goal is your next steps could be one of many.
I need just to be able to read data and parse it where necessary. And of course to be able to update data base with new entries. No data from app should be sent to server.
Ok. If its a one time deal then you can pretty much execute most of those SQL statements in a tool like SQLite Manager. I believe there is a free trial download. Steps to follow roughly are :
- Download SQLite Manager
- Use SQLite Manager to create a sqlite db file
- In the SQL window in SQLite Manager copy Paste the SQL commands from your .sql file. Some statements may need a little cleaning and adapting but for the most part MySQL syntax and SQLite syntax is quite similar.
Good luck!
That Firefox extension you got already does the same thing as SQLite Manager.
Its not going to be easy, for example the 3rd line in your example needs to be changed to:
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
and the last 3 lines you would have to skip.
Since you only need to read and not write, I recommend creating the tables that you need manually and forget about running that code. Hopefully its not that many.
Then you could find all INSERT statements and run them, probably somewhat unchanged.
And first of all, take a tutorial in SQLite 
Agreed. This will be a great crash course in SQL and its various flavors.
Thanks!
So I should turn it to SQlite. Still I don’t understand how it can be updated on the server? if it uses MySQL.
Not sure if I’m following, you said you did not have to update server right?
No, I need it to be possible to update. It’s an app for cookbook site, and the ability to add new entries is essential.
But you said:
Anyway, if this is a cooking app that displays up to date recipes from a server then the normal method is not storing the entire online database locally but rather set up a REST service on the server that your app can use to get the appropriate recipes to display to the user. Something like this: http://coronalabs.com/blog/2012/11/20/how-to-download-json-data-from-mysql-using-corona-sdk/
I mean that the new entries are added on the server by admin, but not in the app by the user.
I’ve seen this tutorial and probably will implement.
But I thought the db should be added locally anyway, because there are tons of recipes.
Syncing local DB with online DB will not be a trivial task though. But some sort of caching would be optimal I guess. But that would still happen after user has downloaded the recipes usually. Not preload the app with entire db.
But I would definitely start with just the ability to display a single recipe gotten from a REST API, that would be a good start.
Then later work out how to display multiple recipes in a tableview. That could be cached to a JSON file for example and only updated when server lets you know something is new.