Web App connection to MS SQL - IDE on OSX

Databases
1

Hi there,

I’m a newbie, just installed Xojo on my Mac (OSX 10.8.5) and need to develop a Web App connecting to an existing MSSQL Express database. I just need read-only access.
It doesn’t seem possible to do it with Xojo.

Does anyone have a solution ?

I’ve been reading posts about MBS SQL plugin and others about Actual ODBC but I’m not sure it works for a Web app.

Any hints ?

Many thanks,

Olivier

2

I’ve not tried the Actual Microsoft SQL Server ODBC driver, but in general ODBC works fine with web apps. You should be able to download their evaluation version to test it out with your app.

3

I use SQL Server with a web app, but on Windows. I think it should work for you.

Here is something you may want to look at. I am going from memory, I have not installed Express in a long time… I believe that by default, SQL express activates only the shared memory conduit. You may have to activate the others (TCP/IP and trusted connexion, I believe. But I am not entirely sure of the latter) I remember I once had a similar issue with a VB6 app, and the solution was to activate the other conduits. I would verify which one the ODBC driver (or the plugin) uses - i suspect it is TCP/IP. I cannot be sure, because I don’t use ODBC. I prefer straight ADO, which you can unfortunately not use.

4

I have used the ActualTechnologies and they work exceptionally well.

5

While it’s quite simple to connect to any MSSQL on Mac (using Xojo’s MSSQL plugin or Actual ODBC Driver as mentioned before) you might have some drawbacks on Windows in using ODBC. You must not use the default ODBC MSSQL Driver. Try to install and use the SQL Server Native Client OLE DB instead.

You may redist the MSSQL native client in your software:

http://technet.microsoft.com/de-de/library/ms131321.aspx

6

Hi Greg,

I’m trying to configure the ODBC connection but can’t get it work.
Do you use MSSQL Express ? Are there any settings on the MSSQL server needed to let me access the db ?
I have a copy of the MSSQL db installed on my Mac in a Parallels virtual Win XP.

Thanks,

Olivier

7

[quote=39367:@Louis Desjardins]I use SQL Server with a web app, but on Windows. I think it should work for you.

Here is something you may want to look at. I am going from memory, I have not installed Express in a long time… I believe that by default, SQL express activates only the shared memory conduit. You may have to activate the others (TCP/IP and trusted connexion, I believe. But I am not entirely sure of the latter) I remember I once had a similar issue with a VB6 app, and the solution was to activate the other conduits. I would verify which one the ODBC driver (or the plugin) uses - i suspect it is TCP/IP. I cannot be sure, because I don’t use ODBC. I prefer straight ADO, which you can unfortunately not use.[/quote]

Thanks Louis, I’ll check the SQL Express settings and see if it helps.

8

Assuming you have created a database and created users that are allowed to access the data, the only stumbling block I remember was needing to use connection strings instead of setting the username, password, host and database properties.

9

Thanks Greg, I’ll dig into it tomorrow.

10

Olivier, in case you are unsure about the connection string, you may want to take a look at this link: . I find it useful to jog my memory from time to time. You will find there a few specific things about connecting to an Express database. While is it functionally equivalent to its big SQL Server brother, Express does have a few minor differences in the way it is managed. Once connected, there should not be any difference in the way that you code for either SQL Server or Express.

11

Oh, and I was forgetting. I like to simplify the user management by having only one db user for my application, and having a routine that compares the login and user password to a hash code kept in the db. This way, I need to maintain only one db user, and I can authenticate and manage any number of users easily.

12

Greg, Louis, THANKS

Combining your comments and advices I’m now able to connect to my MSSQL db, using the ActualTechnologies ODBC driver.
However, if everything’s fine while testing my WEB app on my Mac, I don’t see exactly what’s needed to be deployed on my linux server.
My “test” configuration is as follow:

  • Ubuntu server with apache to run CGI scripts
  • Win XP running the MSSQL Express db

I guess I need to configure the ODBC connection on the Ubuntu server, but how ?

Thanks again,

Olivier