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

Asked by Ron Turrentine on 2018-08-13

************ only works up-to 1.1.3
for 1.1.4: bug 1789569

**** the solution from comment #6 --- thanks to the provider ***************
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
import datetime
import sys

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

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

I am running Windows 7.
I have SIkuliX 1.1.2 installed.
I have Python 2.7 installed (C:\Python27)

I have installed pyodbc via pip.

I added a "sites.txt" file per instructions in found in the "Answers" forum.
The file is located under my AppData folder here:
C:\Users\Ron\AppData\Roaming\Sikulix\Lib\site-packages

The sites.txt file contains only the following line:
C:\Python27\Lib\site-packages\

I placed the line "import pyodbc" in my script, but when I run it I receive a traceback error that states:
"ImportError: No module named pyodbc"

I thought that perhaps the pyodbc driver wasn't properly installed, so I tried running the pip again, but it reported:
"Requirement already satisfied: pyodbc in c:\python27\lib\site-packages"

I'm obviously doing something wrong. Please let me know how I can get this working.

Thanks in advance!

Ron

Question information

Language:
English Edit question
Status:
Solved
For:
Sikuli Edit question
Assignee:
No assignee Edit question
Solved by:
Ron Turrentine
Solved:
2018-08-14
Last query:
2018-08-14
Last reply:
2018-08-14
RaiMan (raimund-hocke) said : #1

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.

RaiMan (raimund-hocke) said : #2

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

Ron Turrentine (oldtimerocker) said : #3

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

Ron Turrentine (oldtimerocker) said : #4

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

Thanks!

Ron

RaiMan (raimund-hocke) said : #5

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

Ron Turrentine (oldtimerocker) said : #6

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

RaiMan (raimund-hocke) said : #7

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