ODBC connecting problems

  1. 5 weeks ago

    Alexandre C

    Mar 20 Pre-Release Testers Brazil

    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" // it crashes the application (get error 500)

    - if i include:
    db.DataSource="Driver={ODBC Driver 17 for SQL Server};" // it crashes the application (get error 500)

    - 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 !@#$% 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

  2. Alexandre C

    Mar 20 Pre-Release Testers Brazil

    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

    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
  3. Alexandre C

    Mar 21 Pre-Release Testers Brazil
    Edited 5 weeks ago

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

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

    -image-

  4. Tim P

    Mar 21 Pre-Release Testers Austin, TX

    @Alexandre C ODBCDatabase.DataSourceNames() As RecordSet
    Does not return anything to me on compiled CGI.

    This is what I would look into. This is why you can't connect on Linux.

  5. 4 weeks ago

    Alexandre C

    Mar 21 Pre-Release Testers Brazil

    @Tim P 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.

  6. Alberto D

    Mar 22 Pre-Release Testers

    You said:

    @Alexandre C 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!!!

    ------------------------------------------------------------------------------------------------------------------------------------------------------

    then:

    - 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" // it crashes the application (get error 500)

    - if i include:
    db.DataSource="Driver={ODBC Driver 17 for SQL Server};" // it crashes the application (get error 500)

    - 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

    ------------------------------------------------------------------------------------------------------------------------------------------------------

    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.

  7. Isaac R

    Mar 22 Pre-Release Testers, Xojo Pro Minnesota, USA
    Edited 4 weeks ago

    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
  8. Alexandre C

    Mar 22 Pre-Release Testers Brazil

    @Alberto D;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.

    Hi Alberto!

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

  9. Alexandre C

    Mar 22 Pre-Release Testers Brazil

    @Isaac R 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!

  10. Alexandre C

    Mar 27 Pre-Release Testers Brazil

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

or Sign Up to reply!