[HowTo] access SQL Server 12.0.5207 on a Windows Server VM

Since SikuliX internally uses the Java based Jython interpreter (language level 2.7), only pure python-language modules can be used from a C-Python installation.

If a C-Python module needs any support off C-based stuff (packed as dll-libs), then this will lead to an import error.
Those modules are simply not useable in the SikuliX environment.

So be sure pyodbc meats the requirement.

If it does not look for an odbc-driver that is compatible with Jython 2.7.

You might try, wether the bundled zxJDBC.jar driver lib meets your requirements.

use
load("zxJDBC.jar")

to make it available on sys.path.

For usage look in the net.

BE AWARE: load() need Java 8 (does not work with Java 9+)

Thanks Raiman! I will definitely look into using JDBC and the load() statement.

BTW I checked and my machine is on Java 8 build 171.

Ron

PS: don't see the bundled JDBC jar file. Where would I find it?

Thanks!

Ron

bundled stuff is in the Lib folder in the <appdata> path (see docs)

I took Raiman's advice and nixed pyodbc for zxJDBC instead (which is bundled with Sikuli).
It works, and this is how I got it to work with our SQL Server database.

The "missing link" was the Microsoft JDBC Driver 7.0 for SQL Server. I downloaded the jar from them here:
https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017
(which directs you to here: https://www.microsoft.com/en-us/download/details.aspx?id=57175)

The jar file referenced in my "load" command after the imports.
So far it appears to be working well!

By the way, this is connecting to SQL Server 12.0.5207 on a Windows Server VM.

Thought that I would share it in case someone else needed help with this as well.

Thanks for your help, Raiman!

Ron

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
from com.ziclix.python.sql import zxJDBC

load("C:\Users\BYE8557\Documents\SQLJDBC\sqljdbc_7.0\enu\mssql-jdbc-7.0.0.jre8.jar")

conn = None

d, u, p, v = "jdbc:sqlserver://yourservername", "youruserid", "yourpassword", "com.microsoft.sqlserver.jdbc.SQLServerDriver"

try:
    conn = zxJDBC.connect(d, u, p, v)

    cursor = conn.cursor()

    cursor.execute("SELECT * FROM dbname.dbo.tablename as a WHERE a.fieldone = 'sometext' and a.fieldtwo = somevalue")

    print cursor.fetchone()

    print "DB Name: " + conn.dbname
    print "DB Version: " + conn.dbversion
    print "Driver Name: " + conn.drivername
    print "Driver Version: " + conn.driverversion
    print "Database URL: " + str(conn.url)
    print "Connection info: " + str(conn.__connection__)
    print "Is connection closed? -> " + str(conn.closed)

    conn.close()

except:
    exc_type, exc_val, exc_tb = sys.exc_info()

    ErrorText = "***** ERROR IN SCRIPTNAME = " + sys.argv[0] + " *****\n"
    ErrorText += "Date/Time : " + str(datetime.datetime.now()) + "\n"
    ErrorText += "Line Number: " + str(exc_tb.tb_lineno) + "\n"
    ErrorText += "Error Type : " + exc_type.__name__ + "\n"
    ErrorText += "Error Value: " + exc_val.message + "\n"
    ErrorText += "***************************************************************\n\r"

    print ErrorText

@Ron
thanks for the elaborated feedback.
I put it as solution to the top and changed the title.