CODEWITHSUNDEEP
python-3.xignite
Richard Crossley
Richard Crossley

Reputation: 586

Python string to varchar mapping using ODBC with Apache Ignite not supported

My Ignite instance (2.7.5) is up and working, I can connect to it with DBeaver and create tables, store and retrieve data etc.

I am now trying to connect from a Python 3 (Python 3.6.8) script using pyodbc. I have compiled and installed the Apache Ignite ODBC driver from the source code provided in the ${IGNITE_HOME}/platforms/cpp directory. The script is able to create a table with 2 columns one int and one varchar, but when I try to insert a string value into the varchar column an exception is thrown:-

    Traceback (most recent call last):
      File "/path/Projects/test_ignite/main.py", line 27, in <module>
        main()
      File "/path/Projects/test_ignite/main.py", line 23, in main
        create_table(conn)
      File "/path/Projects/test_ignite/main.py", line 16, in create_table
        cursor.execute(sql, (row_counter, col_1))
    pyodbc.Error: ('HYC00', '[HYC00] Data type is not supported. [typeId=-9] (0) (SQLBindParameter)')

Changing the data type on the second column works as expected.

The sample script is below:-

import pyodbc


def create_table(conn):
    sql = 'CREATE TABLE IF NOT EXISTS sample (key int, col_1 varchar, PRIMARY KEY(key))'
    cursor = conn.cursor()
    cursor.execute(sql)

    sql = 'insert into sample (key, col_1) values (?, ?)'
    num_rows = 10
    row_counter = 0

    while row_counter < num_rows:
        row_counter = row_counter + 1
        col_1 = 'Foo'
        cursor.execute(sql, (row_counter, col_1))    # Exception thrown here


def main():
    conn = pyodbc.connect('DRIVER={Apache Ignite};' +
                          'SERVER=10.0.1.48;' +
                          'PORT=10800;')
    create_table(conn)


if __name__ == '__main__':
    main()

Upvotes: 0

Views: 401

Answers (2)

ivandasch
ivandasch

Reputation: 1

Yep, ignite's odbc driver doesn't support this type (WVARCHAR), but this issue can be easily mitigated in this way:

with pyodbc.connect('DRIVER={Apache Ignite};SERVER=127.0.0.1;PORT=10800;SCHEMA=PUBLIC') as conn:
    conn.setencoding(encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')

Upvotes: 0

alamar
alamar

Reputation: 19343

PyODBC seems to use WVARCHAR type. Ignite's ODBC does not seem to support it. I would recommend using jdbc bindings or Python thin client.

I have filed an issue against Apache Ignite JIRA: IGNITE-12175

Upvotes: 1

Related Questions