Start a new topic
Discussions xDI xDI - Designer
Answered

Mapping avec source JSON

Bonjour,

Je tente de charger un json dont certaines propriétés sont facultatives.

Exemple :

{

 "data":

 [

  {

   "id": "001",

   "prenom": "Marjorie",

   "nom": "DUPONT",

   "tel": "123456789"

  },

  {

   "id": "025",

   "nom": "Accueil",

   "tel": "015555555"

  }

 ]

}


Le 2ème enregistrement n'a pas de prénom.
Lorsque je charge ce json dans une table avec les 4 champs id, prenom, nom, et tel, j'ai l'erreur suivante au moment du Load :
java.sql.SQLException: JZ0S2: L'objet Statement a déjà été fermé.

 at com.sybase.jdbc4.jdbc.ErrorMessage.raiseError(ErrorMessage.java:779)

 at com.sybase.jdbc4.jdbc.SybStatement.checkDead(SybStatement.java:3059)

 at com.sybase.jdbc4.jdbc.SybStatement.checkStatementAndTransaction(SybStatement.java:3024)

 at com.sybase.jdbc4.jdbc.SybPreparedStatement.checkStatementAndTransaction(SybPreparedStatement.java:4445)

 at com.sybase.jdbc4.jdbc.SybStatement.sendBatch(SybStatement.java:2168)

 at com.sybase.jdbc4.jdbc.SybStatement.executeBatch(SybStatement.java:2105)

 at com.sybase.jdbc4.jdbc.SybStatement.executeBatch(SybStatement.java:2046)

 at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeBatch(SybPreparedStatement.java:2053)

 at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)

 at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)

 at com.indy.engine.actionCodes.jdbc.SimpleJdbcActionCodeTypeI$WrappedPreparedStatement.executeBatch(SimpleJdbcActionCodeTypeI.java:298)

 at com.indy.engine.actionCodes.jdbc.SimpleJdbcActionCodeTypeI.finalizeStatement(SimpleJdbcActionCodeTypeI.java:349)

 at com.indy.engine.actionCodes.XmlToJdbcActionCodeI.finalizeExecuteCode(XmlToJdbcActionCodeI.java:325)

 at com.indy.engine.action.common.ActionCodeTypeI.finalizeExecuteCode(ActionCodeTypeI.java:284)

 at com.indy.engine.manager.ActionCodeExecutor.executeCode(ActionCodeExecutor.java:148)

 at com.indy.engine.manager.ActionCodeExecutor.run(ActionCodeExecutor.java:267)

 at java.base/java.lang.Thread.run(Thread.java:834)


image

Est-il possible de charger ce type de fichier json avec des propriétés facultatives ?


Merci pour votre aide.

Best Answer

Hi Michel,


let me copy/paste the answer that solved your issue then, as this topic might help other users.

"

The problem comes from the fact that in your json structure you have "Value" elements without the "First Name" element in them:


For example in the case below:

​

The structure of the “Value” element is therefore dynamic.

Normally xDI inserts "nulls" into load tables for occurrences of "Value" without "First Name" and it is a priori this case which causes the exception.

Apparently the method used by xDI to do this is not supported by the sybase driver (but it works on all other bases like h2, hsql, mysql, mssql etc.). 

To get around this problem you can use the "Name Selection Pattern" property: 

 

And copy the field name there.

This will force xDI to only take values ​​from elements that actually exist in the json structure.

In this way the unsupported method will not be used, but functionally it gives the same result on the stage:

Here is the course content on HSQL (where no need to use "Name Selection Pattern"):

​

And the course on Sybase (with the "Name Selection Pattern" property set to "First Name" for the "First Name" field):


A priori you should therefore set this property for all the "optional" elements of your json structure.

"

We hope this helps.

2 CommentsSorted by Oldest First

Solution déjà donnée dans le ticket #20841. 

Désolé.

Answer

Hi Michel,


let me copy/paste the answer that solved your issue then, as this topic might help other users.

"

The problem comes from the fact that in your json structure you have "Value" elements without the "First Name" element in them:


For example in the case below:

​

The structure of the “Value” element is therefore dynamic.

Normally xDI inserts "nulls" into load tables for occurrences of "Value" without "First Name" and it is a priori this case which causes the exception.

Apparently the method used by xDI to do this is not supported by the sybase driver (but it works on all other bases like h2, hsql, mysql, mssql etc.). 

To get around this problem you can use the "Name Selection Pattern" property: 

 

And copy the field name there.

This will force xDI to only take values ​​from elements that actually exist in the json structure.

In this way the unsupported method will not be used, but functionally it gives the same result on the stage:

Here is the course content on HSQL (where no need to use "Name Selection Pattern"):

​

And the course on Sybase (with the "Name Selection Pattern" property set to "First Name" for the "First Name" field):


A priori you should therefore set this property for all the "optional" elements of your json structure.

"

We hope this helps.

Login to post a comment
More topics in xDI - Designer
See all 45 topics
Help Desk Software by Freshdesk