ODBC connecting problems

Hi!

I really need help here. I’ll try to be the more clear as i can!

The facts:

  • IDE: Mac Xojo 2018r1.1 / Actual ODBC Drivers
  • Compiled CGI LINUX 64Bit (the linux is a debian 8)
  • MSSQL server 2003

LINUX and MSSQL SEVER 2003 are on the SAME network


ON MAC

From my mac, remotely, i CAN connect to MSSQL server 2003, using ODBC and ACTUAL drivers.
this is the connection:

dim db as new ODBCDatabase db.DataSource="girassol" db.UserName="alemac" db.Password="xxxx" if db.Connect then // IT'S OK!!!

ON LINUX

root@S11:~# odbcinst -j
unixODBC 2.3.7
DRIVERS…: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES…: /etc/ODBCDataSources
USER DATA SOURCES…: /root/.odbc.ini
SQLULEN Size…: 8
SQLLEN Size…: 8
SQLSETPOSIROW Size.: 8

root@S11:~# odbcinst -q -d
[ODBC Driver 17 for SQL Server]

root@S11:~# odbcinst -q -s
[girassol]

and the “girassol” is inside “SYSTEM DATA SOURCES: /etc/odbc.ini”

#### #### #### #### #### #### #### #### ####

[girassol]
Driver = ODBC Driver 17 for SQL Server
Server = 192.168.2.6 // i’m on SAME network of MSSQL server 2003
Database = girassol
UID = alemac
PWD = xxxx

#### #### #### #### #### #### #### #### ####

On Linux on SAME network of MSSQL server 2003 if i execute:

- isql -v girassol alemac xxxx

±---------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------+
i can connect to MSSQL server 2003

The problem is, i can’t figure out HOW to configure the project to do the CGI (on linux) connect to MSSQL (on same network)

What I KNOW:

  • if i use this configuration:

db.Host="192.168.2.6" db.DatabaseName="girassol" db.UserName="alemac" db.Password="xxx"
I can’t connect, that’s the message:
[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

  • if i include:
db.DataSource="dsn=girassol"
  • if i include:
db.DataSource="Driver={ODBC Driver 17 for SQL Server};"
  • if i include:
db.DataSource="Driver={/etc/odbcinst.ini};"

error:[iODBC][Driver Manager]{/etc/odbcinst.ini}: cannot open shared object file: No such file or directory
or if…

db.DataSource="Driver={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1};"

error:[iODBC][Driver Manager]{/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1}: cannot open shared object file: No such file or directory


The application it’s 100% and this is the only fucking thing i don’t know how to fix!

Please, i really need help!

I’m sure someone on XOJO.inc can help not only me, but all Xojo users, just answering here and putting just a little more examples on documentation…

Thank you all,

Alex

Another thing…

ODBCDatabase.DataSourceNames() As RecordSet
Does not return anything to me on compiled CGI.
On my mac, it’s ok. I receive the DSNs

[code]DataSourceList.DeleteAllRows

Dim db As New ODBCDatabase

Dim rs As RecordSet
rs = db.DataSourceNames

If rs <> Nil Then
While Not rs.EOF
DataSourceList.AddRow(rs.Field(“Name”).StringValue, rs.Field(“Description”).StringValue)
rs.MoveNext
Wend
rs.Close
End If[/code]

[quote=429535:@Alexandre Cunha]ODBCDatabase.DataSourceNames() As RecordSet
Does not return anything to me on compiled CGI.
On my mac, it’s ok. I receive the DSNs[/quote]

On a new cgi, with only this code, the ODBCDatabase.DataSourceNames() As RecordSet works

[quote=429535:@Alexandre Cunha]ODBCDatabase.DataSourceNames() As RecordSet
Does not return anything to me on compiled CGI.
[/quote]
This is what I would look into. This is why you can’t connect on Linux.

ODBCDatabase.DataSourceNames() As RecordSet is working. see the picture above.

You said:

[quote=429527:@Alexandre Cunha]From my mac, remotely, i CAN connect to MSSQL server 2003, using ODBC and ACTUAL drivers.
this is the connection:

dim db as new ODBCDatabase db.DataSource="girassol" db.UserName="alemac" db.Password="xxxx" if db.Connect then // IT'S OK!!!

[/quote]
then:

I don’t know if it is a copy/paste error but maybe:

db.DatabaseName="girassol"

should be

db.DataSource="girassol"

I hope you find your issue soon. Good luck.

What user are you running the CGI under? If not root then you may have file permissions problems. Note however that you actually should not run a CGI app as root, so you will likely need to change permissions for the files that the CGI program needs access to in order to complete the connection.

Check permissions with:

ls -al /etc/odbcinst.ini
ls -al /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1

Also worth double checking the kernel, what do you get from this command on the linux machine:

uname -a

[quote=429877:@Alberto De Poo]You said:

then:

I don’t know if it is a copy/paste error but maybe:

db.DatabaseName="girassol"

should be

db.DataSource="girassol"

I hope you find your issue soon. Good luck.[/quote]

Hi Alberto!

The database name and the datasource names are the same. it’s correct.

[quote=429880:@Isaac Raway]What user are you running the CGI under? If not root then you may have file permissions problems. Note however that you actually should not run a CGI app as root, so you will likely need to change permissions for the files that the CGI program needs access to in order to complete the connection.

Check permissions with:

ls -al /etc/odbcinst.ini
ls -al /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1

Also worth double checking the kernel, what do you get from this command on the linux machine:

uname -a

Hi Isaac!

I can connect from linux to mssql usgin isql -v girassol alemac xxx command
I’m trying to configure XOJO odbc to use this dsn, girassol, but i do no how to do that. there is no documentation on xojo.

About the permissions, the dsn girassol is SYSTEM DSN not user dsn. so… it’s correct.

About the informations…

root@S11:~# ls -al /etc/odbcinst.ini
-rw-r–r-- 1 root root 163 Mar 18 21:54 /etc/odbcinst.ini
root@S11:~# ls -al /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
-rwxr-xr-x 1 root root 2069832 Feb 12 19:05 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
root@S11:~# uname -a
Linux S11 3.16.0-4-amd64 #1 SMP Debian 3.16.51-3 (2017-12-13) x86_64 GNU/Linux

I think it’s all ok, right?

I should not change the driver’s permissions, right? both are system wide readable… right?

Thank you for your help!

Can anyone tell me what’s the mininum / correct configuration on XOJO ODBCDatabase to connect to MSSQL?