I’m implementing the tutorial now. When I put the php on my own server and then try to execute it (the database is on another server) it goes:
Warning : mysql_connect(): Host ‘xxxxx.net’ is not allowed to connect to this MySQL server
I tried uploading the database to the server with the php file and I got this error:
Warning : mysql_connect(): Access denied for user ‘xxxxxx’@‘localhost’ (using password: Yes)
Any ideas?
server to server connects are tricky. First off the server running mysql might have firewall rules protecting it against remote connections. You need to provide your IP to that server’s admin and ask them that connections from your server’s IP is allowed. Then they also need to allow remote connections to their MySQL server and finally they need to create a MySQL user for you with connection rights from your IP address. Then you should configure your php script to use that MySQL user id etc.
If you run MySQL on your server where the php is also running then its a little simpler. You need to look at creating a user with the correct rights.
I have to also mention at this point, if you are going to run mysql on your server and run php etc you might as well look into Coronium.io search this site and google). It does so much more than mysql to lua bridging but just that feature alone will make it worth your time investigating it.
Best of luck!!!
Thank you all, I’ve managed to make it work.
Yet, some of the data is in Cyrillic and Corona as well as sqlitemanager shows it as ???
PHPMyadmin shows the data right.
I suppose the problem is with json. Any ideas on how to make it encode/decode other characters than Latin?
Great news!!! As for Cyrillic, set your mysql db to be UTF8. You may need to drop tables, recreate them and then import data again with the db set to UTF8. Hope this helps.
I’ve done it but it doesn’t work.
I’ve found this example http://forums.coronalabs.com/topic/47669-reading-arabic-text-from-mysql-via-json-gives/?p=246615
I’ve added this mysql_query(“SET NAMES ‘utf8’”); to my php and now the ??? transformed to encoding like this \u0443\u0440\u043e.
1)There are other steps in the example. For example, collocate table in the Mysql file, I’ve tried but I don’t know how to do it exactly. My table:
CREATE TABLE `dle_category` ( `id` smallint(5) NOT NULL AUTO\_INCREMENT, `parentid` smallint(5) NOT NULL DEFAULT '0', `posi` smallint(5) NOT NULL DEFAULT '1', `name` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO\_INCREMENT=51;
Where should I put something like DEFAULT COLLATE utf8_general_ci; ?
2)Another question: in the example I mentioned some code shoul be added to php file
\<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /\>
when I add it and run the file, it generates an error. How should it be added?
Sorry, for all the questions, I’m a real noob in php and Mysql:(
I’ve found how to collocate automatically through phpmyadmin and I changed it to utf8_general_ci
Still not working.
It works!
I’ve changed everything to UTF - 8, collocated the database automatically to utf8_general_ci;
and added this
header('Content-Type: text/html; charset=utf-8');
above my code in php file. Corona still displayed ???, but I went to project sandbox and deleted the SQLite file. It created the new file and now cyrrilic characters are displayed normally.
Thanks for your help.
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 :
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.