Fix JSON Binding Logic for Thin and Thick Modes in python-oracledb

[xkazuma]

Isn't the following code the opposite of what is in the if and else blocks?

python-oracledb/samples/json_direct.py

Lines 68 to 74 in 2325059

	if connection.thin or client_version >= 21:
	# Take advantage of direct binding
	cursor.setinputsizes(None, oracledb.DB_TYPE_JSON)
	cursor.execute(inssql, [1, data])
	else:
	# Insert the data as a JSON string
	cursor.execute(inssql, [1, json.dumps(data)])

So, I think the below code is preferred:

 if connection.thin or client_version >= 21: 
     # Thin mode
     cursor.execute(inssql, [1, json.dumps(data)]) 
 else: 
     # Thick mode
     cursor.setinputsizes(None, oracledb.DB_TYPE_JSON) 
     cursor.execute(inssql, [1, data]) 

In my environment, softwares' version are:

• Oracle Database Server: Oracle Database 23ai,
• Database Client: instantclient-basic-linux.x64-23.4.0.24.05.zip ,
• Python: Python 3.12.4, python-oracledb==2.2.1.

If I use direct binding in Thin mode, clients can't display rows including a JSON column, or the column's value shows "java.lang.UnsupportedOperationException".

[cjbj]

The committed sample seems correct (and runs for me): Old Oracle Client libraries don't know about the JSON type, but both Thin mode and new Oracle Client libs do.

What are you running that Python is giving a java error?

Also, you might want to update python-oracledb and Instant Client as they are getting a bit old.

[xkazuma]

Thank you for your reply.

What are you running that Python is giving a java error?

The java.lang.UnsupportedOperationException I reported appeared when I viewed the table with JetBrains DataGrip after the insertion, not during the Python script's execution. My apologies for not making that clearer.

Also, you might want to update python-oracledb and Instant Client as they are getting a bit old.

Since direct binding is working in Thick mode, I'll check the python-oracledb version.

[anthony-tuininga]

Depending on the type of JSON that is being stored and the capabilities of JetBrains DataGrip there may be an incompatiblity. Specifically, the number of characters allowed in a field name and the number of field names allowed are limited in older versions -- although that seems an unlikely source of the issue, unfortunately. As noted by @cjbj, the sample code is correct. Thin mode and newer versions of thick mode are capable of writing JSON directly whereas older versions would be forced to use string. Forcing the use of string will cause the server to perform the conversion and will limit what you can successfully store (since the string representation is less capable than the OSON representation used by the database).

[cjbj]

@xkazuma Check whether you can query the inserted data in (i) SQL*Plus 23.8 (ii) python-oracledb.

Can you share a reproducible testcase that includes the SQL to create the table, and also includes data that gives the problem?