LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only
Back to discussions
Expand all | Collapse all

JDBC Store node - not working

  • 1.  JDBC Store node - not working

    Employee
    Posted 11-26-2014 22:49

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: LindaBris

    Good day,

    Can someone help me with JDBC Store Node? I tried to load data either from a csv file or lookup node. I tried to write to a table in MySQL database. As there are more columns/fields in target table, so I configured JDBC Store Node Exception Behavior as below:

    ExtraFieldBehavior �Ignore
    MissingColumnBehavior �Ignore
    When running the code, data is not writing to table, but to errordata.
    How could I work out to load data into table?

    Thanks,

    Linda


  • 2.  RE: JDBC Store node - not working

    Employee
    Posted 11-27-2014 16:44

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: LindaBris

    I simply made 2 column csv file: ID and Name. I tried to load this 10 rows of data into a table in mySQL having same columns. It still could not work. All 10 rows of data went into ErroredData, not the target table. I just simply filed up first tab with DbTable, DBUser, DbPassword, DbUrl and DbDriver, leaving all other as default.

    Did I do something wrong?

    thanks,

    Linda


  • 3.  RE: JDBC Store node - not working

    Employee
    Posted 11-27-2014 20:29

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mermacora

    Hi Linda

    One thing that could cause this behavior is if one or more of the columns in the target table which you are not supplying on the node input are defined in the DB as NOT NULL. This means that all new rows inserted in the table must have a value, and it's the MySQL database that would enforce this.
    There may be other causes, but difficult to know without looking at the definition of the target table and log information captured when the node is run.

    Hope this helps.

    Regards,
    Mario Ermacora


  • 4.  RE: JDBC Store node - not working

    Employee
    Posted 11-30-2014 15:30

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: LindaBris

    Hi Mario,

    Thanks for replying. I just double checked with my target table - for simple, only 2 fields are defined - ID and Name, both are allow NULL and default is NULL. The input node is defined as CSV File (only 2 fields with 10 pairs of strings.)

    I can output to a excel file without any problem. But only writing to a table, I could not. All records are dumped into erroreddata. Is there any other way I can try or something needs to be fixed?

    (I even created another same table structure in another database, I tried to move data from one table to another, still got the same error message.)

    Thanks,

    Linda


  • 5.  RE: JDBC Store node - not working

    Employee
    Posted 11-30-2014 16:41

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mermacora

    Linda

    What are the types of the ID and Name columns on your DB table? Maybe a type mapping issue.
    Is there anything captured in the node error log. Please try setting LogLevel to 0 (on the Logistics tab of the node editor) then rerun and post anything written to the node error log. Ctrl+Right-Click on the node will show the node log in a text editor.

    Mario


  • 6.  RE: JDBC Store node - not working

    Employee
    Posted 12-01-2014 06:32

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ejones

    What is the error message you are seeing?


  • 7.  RE: JDBC Store node - not working

    Employee
    Posted 12-05-2014 04:53

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Leaner

    I had the same problem, fixed it by making sure that the datatypes on the BRE output matched with the datatypes on the DB.

    Makes sense, but the bad thing is that even with all the exceptional behaviour settings set to 'error', the node will not error. So perhaps there needs to be an additional 'error' setting for datatypes mismatch?


  • 8.  RE: JDBC Store node - not working

    Employee
    Posted 12-05-2014 07:22

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    My understanding was that the issue is in the JDBC driver itself, not on the Lavastorm side.
    You would have to research the documentation on the JDBC driver to see if there is any additional error traps that you can turn on.


  • 9.  RE: JDBC Store node - not working

    Employee
    Posted 12-05-2014 08:40

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Leaner

    Originally posted by: stonysmith
					

    My understanding was that the issue is in the JDBC driver itself, not on the Lavastorm side.
    
You would have to research the documentation on the JDBC driver to see if there is any additional error traps that you can turn on.
    Thanks! You led me on the right track, feeding parameters on the URL string has now fixed several problems I had.



  • 10.  RE: JDBC Store node - not working

    Employee
    Posted 12-05-2014 09:01

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    It would be fantastic if you could share which specific driver you are using and what those magic parameters were <grin>


  • 11.  RE: JDBC Store node - not working

    Employee
    Posted 12-10-2014 03:28

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Leaner

    Originally posted by: stonysmith
					

    It would be fantastic if you could share which specific driver you are using and what those magic parameters were <grin>
    NP, I used parameters in the URL string for JDBC. It appears they are just Java parm declarations for the hibernate.connection object. I used: 
    zeroDateTimeBehavior=convertToNull
    The way to feed the parms is:
    jdbc:mysql://<server>:3306/<db>?zeroDateTimeBehavior=convertToNull
    Params are separated with an ampersand. I have not found a full list of params that can be fed, but so far I've even found that the one can even specify the char set if the driver is having problems understanding it.