SQL for iOS

Is there an iOS example around how to connect to a SQLserver?

You can’t connect directly.
But we once made a Database Connector to talk to a database proxy app:


Hi Christian,
On DT I used a database that resides with a provider. So I connected like this:

[code]mDb.Host = ip-address"
mDb.port = 3306
mDb.UserName = “dbierman”
mDb.Password = “test”
mDb.DatabaseName = “dbierman_pktest”

If mDb.Connect Then
DB_IsConnected = True
return true
DB_Isconnected = false
return false
end if[/code]

I have looked into the iOSSQLIteDatabase class. However I don’t see how you can connect to an external SQL database. It has a connect method but it uses a dbFile. I assume that Host, port, UserName etc. can be specified in that dbFile? But how? Syntax? The same for the AttachDatabase method. Once connected, I think I can handle al I need through the SQLExecute method. Maybe you know the format of the dbFile? I do not need encryption for the moment. Thanks for any help.

As Christian stated, you cannot connect directly to a hosted database.

You will need some middleware, written in Xojo or another language to communicate between the iOS app and the hosted database.

I personally use https://github.com/alixaxel/ArrestDB which is super easy to install and work with.

In Xojo I created a subclass of Xojo.Net.HTTPSocket to handle the SQL queries which are returned as JSON.

Hi Jeremie,
If I understood the manual correctly the SQLiteDatabase class uses a dbFile to specify the (absolute) path to the database. I hoped one could use http:// as a path. Not so. I looked at ArrestDB and there you can actually GET directly from “http://. Anyway I never used an API with XOJO so I searched for API in the manuals hoping to find something like 'using API”, but I found Database.connect API!!! It claims to work with all targets. In the example for SQLIte there is the dbFile again apparently specifying user etc. In the PostgreSQL example there I see something like:

[code]Var db As New PostgreSQLDatabase

db.Host = “localhost”
db.UserName = “postgres”
db.Password = “dbexample”
db.DatabaseName = “postgres”

MessageBox(“Connected to PostgreSQL!”)
Catch error As DatabaseException
MessageBox("Error connecting to PostgreSQL: " + error.Message)
End Try[/code]

Looks very much like I used SQLCommunityserver before. However “localhost” makes me wonder.

Anyway, as I said, I have no idea how to use API’s. Would it be possible for you to give me acces to a most simple iosXOJO project where the API is embedded or is that not the way to use it (kind of plugin maybe). I am retired scientist, not commercial and I am not a programmer as is evident from my struggles. I am just trying to implement a small experiment people can run on their phone where the results are stored in an SQL database…

Do you need all results to be shared with every user of the iPhone app? In that case you need a hosted database (on a remote server) and it will take time to set up.

If each user is independent from the other and you don’t need to retrieve any information from each iPhone running the app, your best solution is a local SQLite database.

Hi Jeremy,
Each time the app is used it has to send the results to an SQL server. In the past I had the same application on DT and used a remote server. I would be nice if I could continue to use that server because it allows me to combine the old results on the laptops and the future results on the iPhones. I am not sure what you mean by local server. Would the SQLite server be on the iPhone itself? Then I don’t see how I ever can analyze the results. I would also be happy if the data could go to some cloud-file (dropbox or similar).
The data should never change only expand. (It s not allowed to select data potshoc in a scientific experiment)

From what you describe, you can continue using the same remote server as for desktop.

BUT you will need to add some middleware to communicate between the iPhone and the server, as I stated above. This can be done in Xojo (Christian did it) or with arrestDB written in php.

THanks Jeremy,
I just contacted Christian directly to ask some more details that I couldn’t find in the Kit’s manual and will probably order his Kit. Yes I would prefer to continue the database I used in the DT-app.
Thanks for your help,