Passing Parameters to the Execute Method in Cursor

ahmedpopal456 commented 6 years ago

When using Jaydebeapi to connect to a MSSQL Server Database, and call stored procedures. an error occurs, in specific cases. Check below

_wConnection = jaydebeapi.connect(jclassname=iClassName, url=iUrl, jars=iSSQLDriverPath, driver_args=iList) cursor = wConnection.cursor() for item in df.collect(): cursor.execute("{call spUpsertTS(?,?,?,?,?,?)}", (item['id'], item['starttime'], item['endtime'], item['userid'], item['pairid'],item['username'])) wConnection.commit() cursor.close()

Now, calling stored procedure like these works well with JayDeBeApi when the list of parameters I provide it with only contains strings. But the moment I provide a datetime, for example (like above), I get the following error:

No matching overloads found for setObject in find. at native/common/jp_method.cpp:127 Traceback (most recent call last): File "", line 78, in main File "", line 33, in importTS File "/mnt/resource/hadoop/yarn/local/usercache/livy/appcache/application_1520609118420_0015/spark-94797d6f-1bfa-4070-88fd-60e83922a44f/userFiles-60a55fd2-8bc1-4f85-89bc-4853fd6e06ab/sparksentinelcluster_dependencies_general.zip/jaydebeapi/init.py", line 499, in execute self._set_stmt_parms(self._prep, parameters) File "/mnt/resource/hadoop/yarn/local/usercache/livy/appcache/application_1520609118420_0015/spark-94797d6f-1bfa-4070-88fd-60e83922a44f/userFiles-60a55fd2-8bc1-4f85-89bc-4853fd6e06ab/sparksentinelcluster_dependencies_general.zip/jaydebeapi/init.py", line 490, in _set_stmt_parms prep_stmt.setObject(i + 1, parameters[i]) RuntimeError: No matching overloads found for setObject in find. at native/common/jp_method.cpp:127

*EDIT: The dataframe which returns the datetime (e.g.item['starttime']), looks a little something like this: 2018-03-09 16:35:41.730000

ramkv commented 6 years ago

this is because of JDBC, Jdbc doesn't support named parameters.

ausiddiqui commented 6 years ago

The same issue was asked about on the jpype repo. I don't think this has anything to do with named parameters. As the OP posted and suggests it only affects a particular data type. This is my experience as well will insert statements, only if the variable inserted is a date/timestamp of some sort does this error pop up. The combinations I have tested are:

Data Type in SQL Table	Python Datatype	Datatype in Python Printed	Outcome
`TIMESTAMP`	`pandas._libs.tslib.Timestamp`	`Timestamp('2018-03-23 10:10:12')`	No matching overloads
`TIMESTAMP`	`datetime.datetime`	`datetime.datetime(2018, 3, 23, 10, 10, 12, 712610)`	No matching overloads
`TIMESTAMP`	`datetime.date`	`datetime.date(2018, 3, 23)`	No matching overloads
`TIMESTAMP`	`datetime.datetime`	`datetime.datetime.strptime('2018-03-23', '%Y-%m-%d')`	No matching overloads
`TIMESTAMP`	`string`	`datetime.date.today().strftime('%Y-%m-%d')` or `datetime.datetime.now().strftime("%Y-%m-%d")` or `'2018-03-23'`	`java.sql.BatchUpdateException: [Teradata JDBC Driver] [TeraJDBC 16.20.00.04] [Error 1338]`
`VARCHAR(10)`	`pandas._libs.tslib.Timestamp`	`Timestamp('2018-03-23 10:10:12')`	No matching overloads
`VARCHAR(10)`	`datetime.datetime`	`datetime.datetime(2018, 3, 23, 10, 10, 12, 712610)`	No matching overloads
`VARCHAR(10)`	`datetime.date`	`datetime.date(2018, 3, 23)`	No matching overloads
`VARCHAR(10)`	`datetime.datetime`	`datetime.datetime.strptime('2018-03-23', '%Y-%m-%d')`	No matching overloads
`VARCHAR(10)`	`string`	`datetime.date.today().strftime('%Y-%m-%d')` or `datetime.datetime.now().strftime("%Y-%m-%d")` or `'2018-03-23'`	No error / SUCCESS

DB Server: Teradata 15.10 DB Driver: Teradata JDBC 16.20 OS: macOS Python: 3.5.5 jaydebeapi: 1.1.1 jpype: 0.6.2

# have some code around batching but row count of 1 was tried for the above table with this code
with conn.session() as cursor:
    table = 'mydb.mytable'
    data = [['Chicago',1,'2018-03-23']]
    header = ['City','myInd','dt']
    query = 'INSERT INTO %s (%s) VALUES (%s);' % (table, ','.join(header), ','.join('?', * len(header)))
    cursor.executemany(query, data)

ausiddiqui commented 6 years ago

I was able to find a workaround for at least my version of this problem:

If the table doesn't exist
- create a temp table with datetimes as string
- insert data as string
- create actual table by selecting * from this temp table and cast datetime columns as the correct format
- drop the temp table
Else if the table exists already
- insert datetime fields as string using the same cursor.executemany() statement above

Once the table is created and populated with values, inserting string fields in the equivalent format as the DB expects into DATETIME fields seems to work without any errors. This works for Teradata flavor of SQL at least, but have not tried out other flavors.

Thrameos commented 6 years ago

I think this one is pretty clear

prep_stmt.setObject(i + 1, parameters[i])
RuntimeError: No matching overloads found for setObject in find. at native/common/jp_method.cpp:127

setObject likely has overloads listed on https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html. Thus the only one that applies is setObject(int, java.lang.Object).

A string can convert to an object as can any primitive type. But a datetime has no inherit conversion.
Thus I need to change my call to...

cursor.execute("{call sp_UpsertTS(?,?,?,?,?,?)}", (item['id'], java.sql.Timestamp(item['starttime'].XXX()), java.sql.Timestamp(item['endtime'].XXX()), item['userid'], item['pairid'],item['username']))

where XXX() is some method that gives a long in unix time in ms.

Basically, you must have a conversion to a java object instance unless it is a java object type or something that trivially converts: primitives, strings(or things that inherit from str), or sequences of those types. We don't currently support adding new inherit conversions (like adding __tojava__ to a class type) that would allow automatic conversions. It is certainly possible to do so but would get messy because that would happen if the user wanted more than one conversion to be implicit.

Thrameos commented 6 years ago

Has this issue been resolved?

ausiddiqui commented 6 years ago

Not sure what java.sql.Timestamp function is in python? What package import is that? I have tried:

java.sql.Timestamp(1515628800)

/Users/ash/miniconda3/envs/py35/lib/python3.5/site-packages/jpype/_jpackage.py:36: UserWarning: JVM not started yet, can not inspect JPackage contents
  warnings.warn("JVM not started yet, can not inspect JPackage contents")
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-40-5ad1755f77e4> in <module>()
----> 1 java.sql.Timestamp(1515628800)

AttributeError: 'str' object has no attribute 'Timestamp'

Thrameos commented 6 years ago

Seems like you haven't started the jvm before trying to perform the import, or you are using an old version that gives a bad warning.

Try this command (assuming python 2)

# Import the module
import jpype

# Start the jvm so we can access java 
jpype.startJVM(jpype.getDefaultJVMPath())

# Fetch the class from java
Timestamp = jpype.JClass('java.sql.Timestamp')

# Use it to create an object
print(Timestamp(1515628800*1000))

You should see this output 2018-01-10 16:00:00.0.

Here is the same code for python 3 using the latest jpype.

# Import the module and import hooks
import jpype
import jpype.imports

# Start the jvm so we can access java 
jpype.startJVM(jpype.getDefaultJVMPath())

# Import a java class
import java.sql.Timestamp

# Use it to create an object
print(java.sql.Timestamp(1515628800*1000))

ausiddiqui commented 6 years ago

Thanks for this, but the doesn't work with 0.6.2 which is what the requirements has, I'm using Python 3. There is no import.py file my version of 0.6.2 for some reason:

In any case updated to 0.6.3, however, now I get the following error:

# Import the module and import hooks
import jpype
import jpype.imports

# Start the jvm so we can access java 
jpype.startJVM(jpype.getDefaultJVMPath())

# Import a java class
import java.sql.Timestamp
---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-46-06fb951798d8> in <module>()
      1 # Import the module and import hooks
      2 import jpype
----> 3 import jpype.imports
      4 
      5 # Start the jvm so we can access java

~/miniconda3/envs/py35/lib/python3.5/site-packages/jpype/imports.py in <module>()
     45 from ._jclass import JClass as _JClass
     46 from ._jclass import _JavaClass as _JavaClass
---> 47 from ._core import registerJVMInitializer as _jinit
     48 
     49 __all__ = ["registerImportCustomizer", "registerDomain", "JImportCustomizer"]

ImportError: cannot import name 'registerJVMInitializer'

Thrameos commented 6 years ago

The python 2 pattern should work for python 3. The 0.6.3 looks like a broken install. The registerJVMInitializer is definitely there.

baztian / jaydebeapi

Passing Parameters to the Execute Method in Cursor #70