Connecting to SQLServer

Databases
#1

Good morning.

There was a SQLServer database and an application (in C#, I believe). Client decides to rewrite completely. In Xojo.

Everything’s basically working including the moving of the data from the old database to SQLite. One problem: When I wrote the data moving code I set up an ODBC connector in Windows. The users, of course, don’t have this. Is there a way to set that up in code or connect directly from Xojo to SqlServer?

Thank you.

#2

There is a plugin specifically for Microsoft sql server available for Windows. It’s in the plugins folder.

#3

You can set up the ODBC connection completely in code

You might try something like whats mentioned in this thread https://forum.xojo.com/5741-update-my-odbc-database

#4

Can I develop and test on OSX connecting to a remote Windows Server with MS-SQL and deploy on the Windows Server?

Here is what I tried. This will Autocomplete when I enter the code but it won’t compile on OSX.

  MEDAPPDB = New MSSQLServerDatabase

I tried an ODBC connection mentioned here but I get a runtime exception (NilObjectException) when I run a debug session from OSX with this code during “launching”. (“private stuff” removed)

  Dim SQLdsn as string
  Dim MEDAPPDB as ODBCDatabase
  
  MEDAPPDB = New ODBCDatabase
  
  SQLdsn = "Provider=SQLOLEDB;Driver={SQL Server};Server=xxx.xxx.126.169;Database=MedAppDB1;Trusted_Connection=yes;"
  
  MEDAPPDB.DataSource=SQLdsn
  MEDAPPDB.UserName="<my-user-here>"
  MEDAPPDB.Password="<my-password-here>"

Helpful hints and opinions welcome …

This is a new app based on a new Windows server. Windows Server 2008r2 is a requirement but MS-SQL is “sort of” optional. Should I consider MySQL on the Windows box? I have MySQL on my MacBook and have deployed some apps on Linux and MySQL with great ease.

I am planning on the Abyss Webserver and have it installed already on the Windows box (but not tested yet with an XOJO app).

Thanks.

#5

you will need to get the activetechnologies odbc driver, or mbs sql to do this from a mac. both work well

#6

MBS Plugin can use native SQL driver from Microsoft on Windows. But we can also use OLEDB or DB-Library. I favor OLEDB as you don’t need to install drivers. We also support ODBC cross platform.
To avoid need of driver on Mac side, you can use freetds library (download for Mac on our website). This way we can connect from Linux or Mac to MS SQL without driver and configuration trouble :slight_smile:

#7

This is the code I use to connect to a MSSQL Server database. It does not require the SQL client to be installed or the ODBC connection to be setup. The downside is that you have to write more code to handle the connecting, etc.

– Rob

cnDatabase = New OLEObject("ADODB.Connection") cnDatabase.Open "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=SQLDBNAME; User Id=MySQLID;_ Password=MYSQLPASSWORD"

To call a Select Query, I use:

Dim rsTable As New OLEObject("ADODB.Recordset")
Dim strRecordSource as string

strRecordSource = "Select * From dbo.ViewName;"
rsTable.Open strRecordSource, cnDatabase

while not rsTable.eof
    whatever =rsTable.Fields("FieldName").Value
    rsTable.MoveNext
wend
rsTable.Close

To Call a Stored Procedure Function

  Dim resultText as string
  Dim cmd as New OLEObject("ADODB.Command")
    
  cmd.ActiveConnection = cnDatabase
  cmd.CommandType = 4
  cmd.CommandText = "FunctionName"
  cmd.Parameters.Refresh
  cmd.Parameters("@Parameter1").Value = value1
  cmd.Parameters("@Parameter2").Value = value2
  cmd.Parameters("@Parameter3").Value = value3
  cmd.Execute
  resultText = cmd.parameters(0).value

To Close the connection:

cnDatabase.Close
#8

Hummm … Robert this looks like you are on Windows. I am looking at “cross connecting” from Mac OSX to a Windows server.

#9

Than you can use ODBC Plugin or MBS SQL Plugin (With ODBC or freetds).

#10

Mark –

Yes I am developing on Windows and I wrote that in response to the original post, I believe the only option on OSX is what Christian has provided.

– Rob

#11