MS-SQL Server ODBC Connection

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
cncgeorge
Posts: 3
Joined: Mon Dec 17, 2018 8:42 pm

MS-SQL Server ODBC Connection

Post by cncgeorge » Mon Dec 17, 2018 10:34 pm

All,

I need to build an app that connects to a MS-SQL Database on port 59354. I am not able to connect, I am able to connect with Python using pyodbc and SQLCMD both work, I have connected in Jupyter Notebooks. But when I use LiveCode the I get (iODBC) error. I have tried the live code wiki at fandom, etc... Any comments would be useful, I am out of ideas!
if anyone has a code snippet that has worked on MSSQL I would love to review.

Thanks,
George

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: MS-SQL Server ODBC Connection

Post by bogs » Mon Dec 17, 2018 10:57 pm

Welcome to the forums :)
cncgeorge wrote:
Mon Dec 17, 2018 10:34 pm
if anyone has a code snippet that has worked on MSSQL I would love to review.
Likewise, if you can show us what you've tried, someone will likely have a better shot at telling you where the mistake(s) might be :D

The wiki Max put up is a very good first place to look, I'm glad to hear you located it on your own.

I am a bit surprised by the error you list (iODBC), I wasn't aware of any that began with 'i' :?
Image

cncgeorge
Posts: 3
Joined: Mon Dec 17, 2018 8:42 pm

Re: MS-SQL Server ODBC Connection

Post by cncgeorge » Tue Dec 18, 2018 2:48 am

Bogs, Thanks for the feedback, I am running 9.02 Indy,
I installed an app on my Mac a while back when I initially tried to connect using ODBC and so it appears to be managing my ODBC connections now it is called iODBC. So I was trying to configure a DSN but the example uses Driver, Server, etc...
I replaced credentials and server name with ***

This code in python3 works #conn = pyodbc.connect("DSN=MAS-ODBC; SERVER=***\sqlexpress2008,59354; DATABASE=em9demo_test; UID=****; PWD=****")

This code in LIveCode does not.
put revOpenDatabase("ODBC","DRIVER=ODBC Driver 17 for SQL Server;SERVER=***,59354;DATABASE=em9demo_test;UID=***;PWD=***;Trusted_Connection=No",,,) into connID

[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

This code in LiveCode does not work, but seems to time out with this error
put revOpenDatabase("ODBC","MAS-ODBC", "***sqlexpresss2008:59354","em9demo","***","***") into connID

[iODBC][Driver Manager]Data source na

Here are my ODBC details...
user ODBC.ini
[MAS-ODBC]
Description = MAS MSSQL
Driver = /usr/local/lib/libmsodbcsql.17.dylib
Server = ***\sqlexpress2008:59354
Database = em9demo_test
Port = 59354
Uid = ***
Pwd = ***

[MAS-ODBC1]
Driver = /usr/local/lib/libmsodbcsql.17.dylib
Server = 192.168.1.51\sqlexpress2008,59354
Database = em9demo_test
Port = 59354
Uid = ***
Pwd = ***

[ODBC]
Trace = 1
TraceFile = /tmp/odbctrace.log
Debug = 1
DebugFile = /tmp/odbcdebug.log

[ODBC Data Sources]
FreeTDS = FreeTDS
SQL-Server = SQL Server

[FreeTDS]
Driver = /usr/local/lib/libtdsodbc Removed ".so" to get this to post
Description = Gotta Work
Server = ***\sqlexpress2008

[SQL-Server]
Driver = /usr/local/lib/libmsodbcsql.17.dylib
Description = Hope
Server = ***\sqlexpress2008
Port = 59354


ODBCinst.ini -j
Gs-MacBook-Pro:masSqlTest george$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/george/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

ODBCinst.ini
Gs-MacBook-Pro:masSqlTest george$ cat /usr/local/etc/odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.13.dylib
UsageCount=3

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
UsageCount=3

[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.Removed ".so" to get this to post
Setup = /usr/local/lib/libtdsodbc.Removed ".so" to get this to post
FileUsage = 1


System ODBC.ini file
[ODBC Data Sources]
ODBC Driver 17 for SQL Server = [MAS-ODBC]
FreeTDS = [FreeTDS]


[MAS-ODBC]
Description = MAS MSSQL
Driver = /usr/local/lib/libmsodbcsql.17.dylib
Server = ***\sqlexpress2008
Port = 59354
Username = ***
Password =***


[MAS-SQL]
Driver = FreeTDS
Server = ***\sqlexpress2008
Port = 59354
UserName = ***
LogonId =***
Password = ***

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 190
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: MS-SQL Server ODBC Connection

Post by matthiasr » Tue Dec 18, 2018 10:13 am

George,
[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded
I get this error message too under Mac OS X when using an SQL connection string to connect, regardless which drivers i use. I´ve filed a bug already. https://quality.livecode.com/show_bug.cgi?id=18151

I am not sure if you can create a DSN for the MS SQL ODBC Drivers. If this is possible, then you should try that.

If not you could try an evaluation version of Actualtech's ODBC drivers for SQL servers to test your code with a DSN.
http://actualtech.com/product_sqlserver.php

I am using their drivers for years with MS SQL Servers.

Regards,

Matthias

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: MS-SQL Server ODBC Connection

Post by AxWald » Tue Dec 18, 2018 1:25 pm

Hi,

I never used ODBC the way you mention - there's a much more comfortable way (at least when using Win). Look at Max' wiki regarding MS Access. That's the method using a prefab DSN and simply calling it by name:

Code: Select all

get RevOpenDatabase("odbc", "DSNName",,,)
(All data needed are in the DSN, so you have a single point where to make changes. This method works with MS SQL as well.)

I usually use a System DSN, and never had problems with it. Dunno if this helps (and works on a Mac), but it may be worth a try.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 190
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: MS-SQL Server ODBC Connection

Post by matthiasr » Tue Dec 18, 2018 3:24 pm

Dunno if this helps (and works on a Mac), but it may be worth a try
DSN is also supported on Mac OS X, but i think the driver has to support it and until now i did not find a way to use the Microsoft ODBC SQL driver with DSN.

Btw.: The advantage of using ODBC connection strings instead of DSN is, that the user does not have to create the DSN to get the software working. See below some examples of connection strings for different MSSQL clients.

Code: Select all

// Connectionstring Examples
// in this examples the MSSQL Server has ip address 192.168.1.250

// Windows
//SQL SERVER Driver
get revOpenDatabase("ODBC","DRIVER=SQL Server;SERVER=192.168.1.250;DATABASE=DBNAME;UID=DBUSER;PWD=DBUSERNAME;Trusted_Connection=No",,,)  

//SQL Server Driver - connect to instance
get revOpenDatabase("ODBC","DRIVER=SQL Server;SERVER=192.168.1.250\INSTANCENAME;DATABASE=DBNAME;UID=DBUSER;PWD=DBUSERPASSWORD;Trusted_Connection=No",,,)  

//Native Client 11 Driver
get revOpenDatabase("ODBC","DRIVER={SQL Server Native Client 11.0};SERVER=192.168.1.250;DATABASE=DBNAME;UID=DBUSER;PWD=DBUSERPASSWORD;Trusted_Connection=No",,,)  

//Native Client 11 Driver - connect to instance
get revOpenDatabase("ODBC","DRIVER={SQL Server Native Client 11.0};SERVER=192.168.1.250\InstanceName;DATABASE=DBNAME;UID=DBUSER;PWD=DBUSERPASSWORD;Trusted_Connection=No",,,)  

--MAC
//Actualtech ODBC driver
get revOpenDatabase("ODBC","Driver={Actual SQL Server};Server=192.168.1.250\INSTANCENAME;Database=DBNAME;UID=DBUSER;PWD=DBPASSWORD;",,,)

cncgeorge
Posts: 3
Joined: Mon Dec 17, 2018 8:42 pm

Re: MS-SQL Server ODBC Connection

Post by cncgeorge » Wed Dec 19, 2018 1:56 am

Matthiasr,

Thanks for the snippets. The driver names are not matching I recognize, should they be changed to my driver name, or is LiveCode providing drivers based on those driver names?

George

SparkOut
Posts: 2839
Joined: Sun Sep 23, 2007 4:58 pm

Re: MS-SQL Server ODBC Connection

Post by SparkOut » Wed Dec 19, 2018 8:18 am

A very old post may still be relevant to create dsn files without having to have the user do it beforehand.

viewtopic.php?t=2441

The link referred to will need to have "runrev" replaced with "livecode" and "/phpBB2" replaced with empty thus:

viewtopic.php?t=2382

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: MS-SQL Server ODBC Connection

Post by AxWald » Thu Dec 20, 2018 11:02 am

Hi,
matthiasr wrote:
Tue Dec 18, 2018 3:24 pm
The advantage of using ODBC connection strings instead of DSN is, that the user does not have to create the DSN to get the software working.
That may be seen as a disadvantage, too ;-) I don't want to have the credentials in my software, that's the job of the local admin IMHO. It's he that determines which user has access to which database, not me!
I could always use some shell calls to create the system DSN, but it would still need his OK.

Guess it's a case of personal preferences, or customer requirements, though.

Anyways, I always used the System DSN method w/o any problems (file DSN never worked ...), so I thought it may be worth a try for the OP - having an ODBC connection independent of LC, that you can test with other programs, that's known to be "good", isn't this a gift from heaven when it comes to debugging?

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 190
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: MS-SQL Server ODBC Connection

Post by matthiasr » Thu Dec 20, 2018 12:27 pm

I don't want to have the credentials in my software, that's the job of the local admin
That´s why my software has a preferences stack, where the user can add credentials e.g for ODBC, SMTP, SFTP or other services. :wink:

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 190
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: MS-SQL Server ODBC Connection

Post by matthiasr » Thu Dec 20, 2018 12:35 pm

cncgeorge wrote:
Wed Dec 19, 2018 1:56 am
Matthiasr,

Thanks for the snippets. The driver names are not matching I recognize, should they be changed to my driver name, or is LiveCode providing drivers based on those driver names?
No, you have to use the exact driver name. Currently it´s not possible to use ODBC connection strings under Mac OS X in LC9. See the bug https://quality.livecode.com/show_bug.cgi?id=18151

If it´s not possible to create a DSN for the MS SQL ODBC Drivers 17, then you have to use an other driver.
That´s why i suggested to try ActualTech´s ODBC driver for SQL Servers. Those drivers support DSN.

Matthias

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 190
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: MS-SQL Server ODBC Connection

Post by matthiasr » Thu Dec 20, 2018 12:37 pm

SparkOut wrote:
Wed Dec 19, 2018 8:18 am
A very old post may still be relevant to create dsn files without having to have the user do it beforehand.

viewtopic.php?t=2441

The link referred to will need to have "runrev" replaced with "livecode" and "/phpBB2" replaced with empty thus:

viewtopic.php?t=2382
Thanks for this. I will have a look.

Unfortunately this will not help George, as he is trying to connect from Mac OSX and the post is about creating DSN under Windows.

Post Reply

Return to “Databases”