search

oracle / soda-for-java

SODA (Simple Oracle Document Access) for Java is an Oracle library for writing Java apps that work with JSON (and not only JSON!) in the Oracle Database. SODA allows your Java app to use the Oracle Database as a NoSQL document store.
http://www.oracle.com/technetwork/database/application-development/oracle-document-store/index.html
Other
66 stars 22 forks source link

Bad Error Message When Saving Document #8

Open recursivecodes opened 4 years ago

recursivecodes commented 4 years ago

When trying to create a new document collection and save a new document with the 19.3 JDBC drivers, I received the following exception when calling insertAndGet:

oracle.soda.OracleException: Error occurred during conversion of the input document’s JSON content to binary. Ensure the content of the input document is valid JSON

This error message is misleading as the document was indeed valid JSON. The fix was to update to the latest 19.6 JDBC drivers, but this error message should have been more clear as to what the issue was.

jjspiegel commented 4 years ago

I agree, we need to fix this to give a better error message. This makes it sound like there was something wrong with the input document.

recursivecodes commented 4 years ago

Any update on this?

morgiyan commented 4 years ago

It's on our TODO list, we'll take a look.

recursivecodes commented 3 years ago

Any update?

morgiyan commented 3 years ago

We're working on a new release now, we'll fix as part of that.

kumarAppsCode commented 3 years ago

Hi Any update on this. I too facing same error oracle.soda.OracleException: Error occurred during conversion of the input document's JSON content to binary. Ensure the content of the input document is valid JSON.

morgiyan commented 3 years ago

@kumarAppsCode this will be fixed in the upcoming release we're working on now. But this issue is only about having a more descriptive error message... are you just asking about that, or are you trying to actually resolve the error?

If you're trying to resolve the error, please make sure you have the right version of the JDBC jar. Which database service are you trying to connect to, is it autonomous 19c or 21c or an on premise database?

For 19c autonomous or on premise, upgrading to ojdbc8.jar version 19.7 should resolve the error.

For 21c autonomous, upgrading to ojdbc8.jar version 21.1 should resolve the error.

kumarAppsCode commented 3 years ago

Hi, I am trying autonomous 19c, based on the oracle blog I am trying to run the java application SODA for Java Eg: Example 3-5 Creating a Document with JSON Content. while running I got the error.

morgiyan commented 3 years ago

@kumarAppsCode right, for autonomous, please make sure you have an ojdbc8.jar (i.e. JDBC jar), version 19.7 or higher, as I mentioned above.

Then the error will go away (of course, please make sure you don't still have an older JDBC jar on the classpath when your run your SODA program).

If you get either SODA 1.1.7.1 (the very latest) or 1.1.4 (the previous) releases of SODA from Maven Central, they'll automatically pull such a jar as a dependency (specifically, SODA 1.1.7.1 will pull a 21.1 ojdbc8.jar, and SODA 1.1.4 will put a 19.7 ojdbc8.jar).

If either one is OK for you, you might as well go with SODA 1.1.7.1 and 21.1 ojdbc8.jar, because that's needed to work with 21c autonomous. So it's future proof.

<dependency>
    <groupId>com.oracle.database.soda</groupId>
    <artifactId>orajsoda</artifactId>
    <version>1.1.7.1</version>
</dependency>

Hope this helps, please let us know if you have further questions.

kumarAppsCode commented 3 years ago

Thanks. Let me try and update you

kumarAppsCode commented 3 years ago

@morgiyan , I got the same error, I can able to connect with the database and I can able to print a list of the collection while trying to insert one JSON document getting below error [Ensure the content of the input document is valid JSON]

Exception in thread "main" oracle. soda.OracleException: Error occurred during conversion of the input document's JSON content to binary. Ensure the content of the input document is valid JSON. at oracle.soda.rdbms.impl.SODAUtils.makeException(SODAUtils.java:53) at oracle.soda.rdbms.impl.OracleDatabaseImpl.createBinaryGenerator(OracleDatabaseImpl.java:403) at oracle.soda.rdbms.impl.OracleDatabaseImpl.textToBinary(OracleDatabaseImpl.java:351) at oracle.soda.rdbms.impl.OracleCollectionImpl.convertToBinary(OracleCollectionImpl.java:393) at oracle.soda.rdbms.impl.TableCollectionImpl.getContentForTransfer(TableCollectionImpl.java:2433) at oracle.soda.rdbms.impl.TableCollectionImpl.bindPayloadColumn(TableCollectionImpl.java:2373) at oracle.soda.rdbms.impl.TableCollectionImpl.insertAndGet(TableCollectionImpl.java:756) at oracle.soda.rdbms.impl.TableCollectionImpl.insertAndGet(TableCollectionImpl.java:637) at oracle.soda.rdbms.impl.TableCollectionImpl.insert(TableCollectionImpl.java:628) at InsertJson.insertDocument(InsertJson.java:39) at InsertJson.main(InsertJson.java:15)

Note: If I try to insert null content. it was working fine.

kumarAppsCode commented 3 years ago

@morgiyan , I am referring to SODA for Java blog step I can able to print all collection from schema I can able to insert a null value
If I try to insert a new JSON in the document. Insert Code I got the below error oracle. soda.OracleException: Error occurred during conversion of the input document’s JSON content to binary. Ensure the content of the input document is valid JSON If I try to retrieve the document from collection getAllEmployee getting the below error. oracle.soda.OracleException: oracle.sql.json.OracleJsonFactory class is not available. Ensure the JDBC jar includes oracle.sql.json support.

If try to change all the latest jar files and run the application got the below error. java.lang.RuntimeException: Invalid character at 32 : \ at oracle.net.resolver.EZConnectResolver.parseExtendedProperties(EZConnectResolver.java:428) at oracle.net.resolver.EZConnectResolver.parseExtendedSettings(EZConnectResolver.java:401)
at oracle.net.resolver.EZConnectResolver.parse(EZConnectResolver.java:187) at oracle.net.resolver.EZConnectResolver.(EZConnectResolver.java:146) at oracle.net.resolver.EZConnectResolver.newInstance(EZConnectResolver.java:155) at oracle.jdbc.driver.OracleDriver.resolveNonSimpleURL(OracleDriver.java:860) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:657) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:602) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251) java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid" (CONNECTION_ID=mMCltp2jTTe99BuPIDjDwQ==) at oracle.jdbc.driver.T4CConnection.handleLogonNetException(T4CConnection.java:870) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:675) at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1032) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:90)

I am using cloud wallet in the window system and it doesn't have "host:port:sid" , my url will be String url = "jdbc:oracle:thin:@****_high?TNS_ADMIN=D:\Projects\New Learn\SODA\DB JAVA\Wallet_DB202106021234";

Attached My Sources. Kindly check and provide your suggestion.

Note: Only attached sources jar. I can able to see all collection name from the schema and I can able to insert null content remaining process getting errors. If new jar version modifying. it getting errors.

SODA for PLSQL working fine. I need to try JAVA

Thank you

morgiyan commented 3 years ago

@kumarAppsCode right, updating the jars was necessary, but now it can't establish a JDBC connection to autonomous for some reason. Yes, the URL looks correct, the host/port would come from the tnsnames.ora, which is in the TNS_ADMIN directory in the URL. Somehow it's not processing that TNS_ADMIN directory correctly.

So with your previous ojdbc8.jar it's working, but if you upgrade the jar to 19.7 or 21.1, then it starts giving this error you posted?

java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid"

For the set of JDBC jars you're using, could you please tell me the output of (this will tell me the versions of these jars):

java -jar ojdbc8.jar java -jar osdt_core.jar java -jar osdt_cert.jar java -jar oraclepki.jar

Let me also check with JDBC folks on connecting to autonomous from windows (I am personally not an expert on that, since it's orthogonal to SODA) and get back to you shortly... it might be next week, because it's the weekend now and folks might be off. But in the meantime, please let me know the output of java -jar commands above.

morgiyan commented 3 years ago

@kumarAppsCode and apologies for the inconvenience! We'll get this figured out asap.

kumarAppsCode commented 3 years ago

@morgiyan, Noted. Thanks for the update. Please refer the below jar version

java -jar ojdbc8.jar Oracle 18.3.0.0.0 JDBC 4.2 compiled with javac 1.8.0_171 on Tue_Jun_26_11:06:40_PDT_2018

Default Connection Properties Resource

Sun Jun 13 11:05:41 IST 2021

* JCE UNLIMITED STRENGTH IS INSTALLED

java -jar osdt_core.jar Version : 18.1.0.0.0

java -jar osdt_cert.jar Version : 18.1.0.0.0

java -jar oraclepki.jar Oracle PKI Version : 18.1.0.0.0

I have attached the source code with the java jar for your reference.

morgiyan commented 3 years ago

@kumarAppsCode Thanks for posting the info. I see that you don't have the right JDBC jar versions still... you have jdbc 18.3, which won't work for using SODA on Autonomous.

Please use 19.7 or 21.1. Best to use 21.1, because that'll also work with Autonomous 21c, as well as Autonomous 19c. These links point to a zipped tar of all the jars, in addition to ojdbc8.jar, that you need for connecting to Autonomous - specifically osdt_core.jar, osdt_cert.jar, and oraclepki.jar. Please make sure to download the jars from either one of these two links (21.1 is preferrable), and don't use your old 18.3/18.1 jars (make sure they are not on the classpath!)

Also, instead of escaped backslashes in the wallet path of the URL, please use forward slash, even for Windows (so instead of \ use /). And please don't use spaces in directory names used in the wallet path (e.g. don't use DB JAVA, or New Learn). You can put it in any directory, but with no spaces used in the path. For example, you can put all the wallet files in D:/Projects/NewLearn/SODA/DBJava/Wallet_DB202106021234.

So instead of:

String url="jdbc:oracle:thin:@db202106021234_tp?TNS_ADMIN=D:\Projects\New Learn\SODA\DB JAVA\Wallet_DB202106021234";

You'll have something like this:

String url="jdbc:oracle:thin:@db202106021234_tp?TNS_ADMIN=D:/Projects/NewLearn/SODA/DBJAVA/Wallet_DB202106021234";

You can just create D:/Projects/NewLearn/SODA/DBJAVA/Wallet_DB202106021234 and copy all the files from your original wallet directory (i.e. the one with spaces in the path) there.

kumarAppsCode commented 3 years ago

Thanks for the update. Let me try and update you