revOpenDatabase works fine in simulator, but not on iOS!

[JustinW42]

I am attempting to connect to a MySQL db that I am hosting on Hostgator. I have been connecting to databases for months now with no problems with various desktop apps, and the code works fine in the iOS simulator. However, when I deploy the app as an iOS standalone and copy it to a development device, the code always fails silently when I run the connectToDB function....

Code: Select all

on connectToDB
    
    put "xxx.xxx.xxx.xxx:3306" into gDatabaseAddress
    put "xxxxxxxx" into gDatabaseName
    put "xxxxxxxx" into gDatabaseUser
    put "xxxxxxxx" into gDatabasePassword
    
    --put URLEncode(gDatabasePassword) into tDatabasePassword
    
    put revOpenDatabase("MySQL", gDatabaseAddress, gDatabaseName, gDatabaseUser, gDatabasePassword) into tConxn
    
    --answer tConxn
    
    # it's a good idea, but not required, to check for a successful connection here
    if tConxn is a number then
       put tConxn into gConnectionID
       --put "Connection ID: " & gConnectionID & cr before fld "fldMessage"
       answer "DB Connection Success!"
        return empty
    else
        put "Unable to connect to database '" & gDatabaseName & "': " & tConxn into tErrMsg
        --answer error tErrMsg as sheet
        answer tErrMsg
        --put tErrMsg & cr before fld "fldMessage"
        put empty into gConnectionID
        return tErrMsg
        exit to top
     end if
     
end connectToDB

I put various answer commands scattered throughout my code to find out where they stop appearing on the iOS device, and it always happens during the connectToDB function. Any ideas on what I might be doing wrong? I check the MySQL box in the standalone iOS application settings in LC, and as I mentioned it works great in the simulator..... (there are some bugs with the way I set up the simulators so it only works on the iPhone 5 simulator, btw).

When I deploy the app for testing on an actual device, I am using an iPhone 5S and an iPhone 6. No error messages appear on the device, but it is clear that the code simply stops executing when I run the connectToDB function.

[Klaus]

Hi Justin,

the correct db description needs to be in small caps!
Since iOS is case sensitive, this is worth a try:
...
put revOpenDatabase("mysql", gDatabaseAddress, gDatabaseName, gDatabaseUser, gDatabasePassword) into tConxn
...
If in doubt, always consult the dicitonary!


Best

Klaus

[JustinW42]

I replaced "MySQL" with "mysql" in the connection string, but it still stops processing the code when it hits that particular call.... Is it possible that, even though I select MySQL in the build options for iOS in Livecode, that it is not actually adding this library to my stack? Do I need to consider enabling sqlite, as well?

[JustinW42]

Some additional information:

1. My databases are hosted on Hostgator, and when I check the Remote MySQL settings there is a wildcard option ("%") to enable universal access.

2. When I look at the DB format, it seems to be in MyISAM (should I switch all the tables to InnoDB?).

3. Collation is set to utf8_unicode_ci for all tables.

[JustinW42]

If it matters, I see a setting in my database server which reads as follows:

PHP extension: mysqli

I looked it up and the mysqli is for something called MySQL Improved, but I can't see to figure out how that might affect my connection attempts from iOS.... At this point I am simply running through every server setting, deployment setting and code that I can, although I know it is the revOpenDatabase which is failing.... If I put an answer command right before that point, it executes.... If I put an answer command right after that line of code, it does not execute. I have double checked the syntax of the revOpenDatabase command, but aside from the fact that it contained uppercase letters it appears to be correct....

All I can think of is that it might be a build issue or a database issue, but the DB works great in every other context (including accessing from desktop apps, as well as the iOS simulator).

[JustinW42]

One more thing - for the gDatabaseAddress field, I am using the explicit IP address to the server, followed by :3306 - not sure if iOS bugs out when you present it with an IP address, but it is worth noting.

[JustinW42]

After a couple days of attempting to make this work, my excitement for getting my app working in the simulator and eventually sifting through all the Provisioning Profile / Certificate nonsense that is required to put an app on an actual device, my excitement is waning.

I am starting to think the issue I am experiencing is a bug within Livecode, rather than a coding issue on my part (like I said, it is always the connectToDB command that fails, right at the moment I try to use revOpenDatabase on a mobile device - I have tested on iPhone 6 and an iPhone 5S with no luck). If this is the case, that means I really am stuck and can't (at this time) move forward with any of my projects.

Everyone says MySQL is "supposed" to work on iOS, but my code works great everywhere else except for any iOS device. I am reading up on lite databases to see if I can make something work with that, but it really sucks to have to completely re-think my software approach because iOS doesn't want to play nice with Livecode. Also, I am having a hard time figuring out how to connect remotely to a lite database, since my initial impression seems to be that it is structured more for local data connections.

Any last suggestions I can try before I gut my programs and start reworking everything?

[JustinW42]

If in doubt, always consult the dicitonary!

I spend a fairly significant amount of time reading through the dictionary on a nightly basis, but it does not always show examples of functional code nor does it always mention the little caveats and "gotchas" that I have run into. For instance, the revOpenDatabase entry does not seem to mention anything about the "MySQL" portion being case sensitive - many examples online and in the forum show it written out as I had it originally, rather than all lowercase letters. I'm not too bad at following instructions or following breadcrumb-trails to find information or likely bug culprits, but the provided documentation can only take one so far.

Unlike many other things I run into, I seem to have narrowed my current issue down to a very specific issue, so not being able to fix it has become increasingly frustrating. If it is not a Livecode bug, then it would almost certainly have to be an issue with the way my database in configured, but nothing looks out of place as I inspect it - the only things that "might" be an issue are the various settings listed above, which I have no idea if they would potentially affect how Livecode works on iOS.

I have about 30+ databases on my server configured for a myriad of software, both out-of-box and proprietary (with a lot of open source stuff running in different capacities, as well) - the mere action of connecting to the database itself has not presented a problem on any other platforms, with any other software projects or deployment methods... That is what leads me to suspect that LC is what is dropping the ball here, although I don't want to place blame until I completely rule out all other options. Tonight I am going to make a fresh stack that does nothing but use the revOpenDatabase function to connect to the database, and see if that gives me the same problem.... If NOT, then I will go back and evaluate my code again. If so, then that would be fairly strong evidence that the bug is on the side of Livecode, correct (or potentially the Apple provisioning process, as some people have mentioned issues with development profiles in the past - these seems less likely since I can actually build my standalone apps successfully and even copy them on to an actual device with no visible errors)?

EDIT: I had previously listed the version of LC I was using, but I realized it was incorrect since I develop on a Windows PC and test / deploy from a Mac. Once my partner gets to the shop with the test / deployment computer, I will verify which version we have installed on that computer. As of earlier this week (when we were tackling a different bug), we had confirmed we were running the "latest" version of LC.

[jacque]

If you uncomment the line that answers tConn, does it give any information? Also try adding a line directly above that one:

answer "Result is " & the result

Might give a clue what's wrong. Be sure to check the result before you run the tConn answer, otherwise the result will change.

[JustinW42]

If you uncomment the line that answers tConn, does it give any information? Also try adding a line directly above that one:

answer "Result is " & the result

Might give a clue what's wrong. Be sure to check the result before you run the tConn answer, otherwise the result will change.

Thanks for the feedback - when I un-comment that answer statement, it simply never runs.... The code always seems to stop executing once it hits revOpenDatabase.

I will try adding the answer "Result" line and see what happens.

[Simon]

Hi Justin,
Make sure you have this code in your stack;

Code: Select all

on errorDialog pExecutionError, pParseError
answer "An error occurred on line: " & item 2 of line 1 of pExecutionError & cr & pExecutionError
end errorDialog

As you know once on the device things can fail silently.

Simon

[JustinW42]

Thanks, Simon - I added your Error Dialog code, and along with the call suggested by Jacque I was able to view the returned error message..... Here is a screen shot of what is being reported:



It definitely appears that the revOpenDatabase function is where the issue is coming from.... Although I must admit, I don't know exactly what I am looking at in terms of all the number indicators and whatnot.

[JustinW42]

A few more things I tried and subsequently ruled out:

1. I tried creating an Ad Hoc Provisioning Profile / Certificate combo to see if it was the Development Provisioning Profile that was creating the issue..... No change in outcome.

2. I tried isolating the code by copying the connectToDB routine to a new stack and running it with a simple button - even in the most fundamental form, there was no change in outcome.

3. I tried reverting back to Livecode 6.6.5 and repeating the same steps, and I was actually able to get the TEST app (the stripped down connectToDatabase version) to successfully connect to the remote database!!!! However, when I tried to deploy the full program we ran into the same problem...... Weird.

Anyways, we kept trying with Livecode 6.6.5 and with no changes to the actual app code, we were able to get it to deploy and properly connect to the database! Therefore, I feel it is same to assume there is something amiss with the newest version of LC, since we have had zero success trying to get the same results, for several days.

As an added note, we eventually got it to work with the Ad Hoc Provisioning Profile, rather than the Deployment version.... Not sure if it actually makes a difference since we repeatedly tried both versions in the newer Livecode with no luck, but it is worth noting for anyone who might be running into similar issues.

Thanks a bunch to everyone who helped out with this issue - I know it is not exactly nailed down yet, but at least there are some good clues as to what the problem might be.

[jacque]

The numbers are error codes. The IDE translates those for you but in an app you need to do that yourself. You can either see the errorDialog entry in the dictionary for info on how to get the list of codes or find the LC Error code lookup stack in the user samples area on RevOnline. Usually you only need to look up the first line, which is where the actual error occurred, the remaining lines are the calls and that led up to it.

There was a version of LC that didn't correctly move externals into the build. I can't remember which one it was but it sort of sounds like what's happening. The error code will tell you.

Edit: Here's the link to the plugin: http://livecodeshare.runrev.com/stack/7 ... ror-Lookup