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 Execute Node with Parameters

  • 1.  JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 06:46

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

    Originally posted by: Leaner

    Hi,

    I've trying to do an insert statement using the JDBC Execute node.
    INSERT INTO
ldte_contractCreditControl_DEV
(CONTRACTID)

VALUES
(CONTRACTID);
    However, as you can see, the VALUES statement does not pass the the data from the 'CONTRACTID' column, rather it passes the actual string "CONTRACTID".

    So, using parameters, I've done this directly on the JDBC Execute node:
    query="
INSERT INTO
ldte_contractCreditControl_DEV
(CONTRACTID)

VALUES
(':1');"

sqlNonSelect(1,query,'CONTRACTID')
    Java is complaining that my syntax is wrong as it seems to be feeding it ":1" instead of the contents of the 'CONTRACTID' in it's input pin.
    ERROR: error on row 0. com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'query="INSERT INTO ldte_contractCreditControl_DEV (CONTRACTID) VALUES (':1');"  ' at line 1
    Can the JDBC Execute Node not use parameters?


  • 2.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 07:03

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

    Originally posted by: ejones

    Based on what I think you are trying to do:
    1. Remove the tick marks from around the :1
    2. The semicolon, ';', at the end isn't necessary when you have only one SQL statement and I've seen it cause errors messages in some contexts.

    So:
    The SqlQuery parameter should be:

    INSERT INTO
    ldte_contractCreditControl_DEV
    (CONTRACTID)
    VALUES
    (?)

    The SqlQueryFieldBindings parameter should be:
    CONTRACTID

    (Edited: I was getting the marker for the parameters used by DB Execute mixed up with the JDBC Execute. Instead of ':1' you should have '?'. I've made the correction above)


  • 3.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 07:36

    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: ejones
					

    Based on what I think you are trying to do:
    
1. Remove the tick marks from around the :1
    
2. The semicolon, ';', at the end isn't necessary when you have only one SQL statement and I've seen it cause errors messages in some contexts.

    
So:
    
The SqlQuery parameter should be:

    
INSERT INTO
    
ldte_contractCreditControl_DEV
    
(CONTRACTID)
    
VALUES
    
(?)

    
The SqlQueryFieldBindings parameter should be:
    
CONTRACTID

    
(Edited:  I was getting the marker for the parameters used by DB Execute mixed up with the JDBC Execute.  Instead of ':1' you should have '?'.  I've made the correction above)
    Thank you!

    My solution with an example, for the next guy:
    node:INSERT_2
bretype:core::JDBC Execute
editor:Label=INSERT
editor:sortkey=5485bb445b380a0a_3
input:5485bb44414d6c89/=
prop:CommitFrequency=500
prop:DbDriver=com.mysql.jdbc.Driver
prop:DbUrl=jdbc:mysql://localhost:3306/dbname
prop:DbUser=WalterWhite
prop:SqlQuery=<<EOX
INSERT INTO ldte_contractCreditControl_DEV
(CONTRACTID)
VALUES 
(?)
EOX
prop:SqlQueryFieldBindings=CONTRACTID
editor:XY=1300,610
end:INSERT_2


  • 4.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 09:00

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

    Originally posted by: Leaner

    For the ultimate finish:

    INSERT INTO ldte_contractCreditControl_DEV
(CONTRACTID)
VALUES 
(?)
ON DUPLICATE KEY UPDATE
(?)
    Getting the error:
    ERROR: The number of column bindings (1) do not equal the number of parameters into the SQL query (2)

ERROR: error: com.lavastorm.brain.node.NodeFailedException
    Is it possible to use the parameter more then once?


  • 5.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 09:07

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

    Originally posted by: ejones

    Thanks for this question. Yes, you will need to have to list one value in SqlQueryFieldBindings for every question mark in your query and the order matters.
    In SqlQueryFieldBindings you will need to list first the value for the first question mark which is CONTRACTID followed by the value for the second question mark which I'm understanding is the same value, CONTRACTID and with a comma as a delimiter between them. This means it will look like this:
    CONTRACTID,CONTRACTID


  • 6.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 09:29

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

    Originally posted by: Leaner

    Thanks @ejones,

    I may have been simplifying my examples a little bit, I have 48 columns I am listing on the query. So would this be the complete solution?

    SqlQuery=
    INSERT INTO ldte_contractCreditControl_DEV
(CONTRACTID, ContractAnalysis, Ccs_Sil_App_THIS_RECORD, LLContractNo, ContractNumber, SiteAddress, ClientCode, ClientName, ClientRef, ClientContact, ClientContactPhone, ClientContactMobile, ClientContactEmail, ProjectManagerCode, ProjectManager, ProjectManagerPhone, ProjectManagerMobile, ProjectManagerEmail, OurContact, App_Number, ValuationDate, ValuationDateDue, ValuationDateDueWeekEnd, Today, TodayWeekEnd, WeeksOld, AgedStatus, AgedValue, App_Net, IPC_Number, IPC_Net, App_Not_IPC_Net, Inv_Number, Inv_Net, Inv_VAT, Inv_Total, IPC_Not_Inv_Net, Paid_Net, Paid_VAT, Paid_Gross, Inv_Not_Paid, Inv_Not_Paid_Gross, PaidStatus, App_CreditControl_NoteNo, IPC_CreditControl_NoteNo, Inv_CreditControl_NoteNo, InvOBalStatus, Inv_Due_Date, pk_id)
VALUES 
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    SqlQueryFieldBindings=
    CONTRACTID,CONTRACTID,ContractAnalysis,ContractAnalysis,Ccs_Sil_App_THIS_RECORD,Ccs_Sil_App_THIS_RECORD,LLContractNo,LLContractNo,ContractNumber,ContractNumber,SiteAddress,SiteAddress,ClientCode,ClientCode,ClientName,ClientName,ClientRef,ClientRef,ClientContact,ClientContact,ClientContactPhone,ClientContactPhone,ClientContactMobile,ClientContactMobile,ClientContactEmail,ClientContactEmail,ProjectManagerCode,ProjectManagerCode,ProjectManager,ProjectManager,ProjectManagerPhone,ProjectManagerPhone,ProjectManagerMobile,ProjectManagerMobile,ProjectManagerEmail,ProjectManagerEmail,OurContact,OurContact,App_Number,App_Number,ValuationDate,ValuationDate,ValuationDateDue,ValuationDateDue,ValuationDateDueWeekEnd,ValuationDateDueWeekEnd,Today,Today,TodayWeekEnd,TodayWeekEnd,WeeksOld,WeeksOld,AgedStatus,AgedStatus,AgedValue,AgedValue,App_Net,App_Net,IPC_Number,IPC_Number,IPC_Net,IPC_Net,App_Not_IPC_Net,App_Not_IPC_Net,Inv_Number,Inv_Number,Inv_Net,Inv_Net,Inv_VAT,Inv_VAT,Inv_Total,Inv_Total,IPC_Not_Inv_Net,IPC_Not_Inv_Net,Paid_Net,Paid_Net,Paid_VAT,Paid_VAT,Paid_Gross,Paid_Gross,Inv_Not_Paid,Inv_Not_Paid,Inv_Not_Paid_Gross,Inv_Not_Paid_Gross,PaidStatus,PaidStatus,App_CreditControl_NoteNo,App_CreditControl_NoteNo,IPC_CreditControl_NoteNo,IPC_CreditControl_NoteNo,Inv_CreditControl_NoteNo,Inv_CreditControl_NoteNo,InvOBalStatus,InvOBalStatus,Inv_Due_Date,Inv_Due_Date,pk_id,pk_id
    FUN!


  • 7.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 09:39

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

    Originally posted by: ejones

    Wow!
    I have not seen that syntax for "ON DUPLICATE KEY UPDATE" before. But, if I'm understanding correctly the order of the question marks inside the second pair of parenthesis correspond to CONTRACTID, ContractAnalysis, etc. in that order. So I believe the SqlQueryFieldBindings should be set to:
    CONTRACTID, ContractAnalysis, Ccs_Sil_App_THIS_RECORD, LLContractNo, ContractNumber, SiteAddress, ClientCode, ClientName, ClientRef, ClientContact, ClientContactPhone, ClientContactMobile, ClientContactEmail, ProjectManagerCode, ProjectManager, ProjectManagerPhone, ProjectManagerMobile, ProjectManagerEmail, OurContact, App_Number, ValuationDate, ValuationDateDue, ValuationDateDueWeekEnd, Today, TodayWeekEnd, WeeksOld, AgedStatus, AgedValue, App_Net, IPC_Number, IPC_Net, App_Not_IPC_Net, Inv_Number, Inv_Net, Inv_VAT, Inv_Total, IPC_Not_Inv_Net, Paid_Net, Paid_VAT, Paid_Gross, Inv_Not_Paid, Inv_Not_Paid_Gross, PaidStatus, App_CreditControl_NoteNo, IPC_CreditControl_NoteNo, Inv_CreditControl_NoteNo, InvOBalStatus, Inv_Due_Date, pk_id, CONTRACTID, ContractAnalysis, Ccs_Sil_App_THIS_RECORD, LLContractNo, ContractNumber, SiteAddress, ClientCode, ClientName, ClientRef, ClientContact, ClientContactPhone, ClientContactMobile, ClientContactEmail, ProjectManagerCode, ProjectManager, ProjectManagerPhone, ProjectManagerMobile, ProjectManagerEmail, OurContact, App_Number, ValuationDate, ValuationDateDue, ValuationDateDueWeekEnd, Today, TodayWeekEnd, WeeksOld, AgedStatus, AgedValue, App_Net, IPC_Number, IPC_Net, App_Not_IPC_Net, Inv_Number, Inv_Net, Inv_VAT, Inv_Total, IPC_Not_Inv_Net, Paid_Net, Paid_VAT, Paid_Gross, Inv_Not_Paid, Inv_Not_Paid_Gross, PaidStatus, App_CreditControl_NoteNo, IPC_CreditControl_NoteNo, Inv_CreditControl_NoteNo, InvOBalStatus, Inv_Due_Date, pk_id


  • 8.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 10:00

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

    Originally posted by: prashanth

    I think you may be missing a few parameter columns if I am not mistaken. The number of parameters must match the number of fields.


  • 9.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 10:20

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

    Originally posted by: ejones

    Thanks prashanth. I'm sure you're right. It would probably be better to use the "Get Metadata" node in LAE and other LAE nodes to build the SQL statement. This can be done in such a way that you are sure the order and number of parameters matches the question marks. I likely made a mistake with the cutting and pasting I did.


  • 10.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 10:35

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

    Originally posted by: Leaner

    Hey Chaps,

    I could only get it to work like so:

    SqlQuery=
    INSERT INTO ldte_contractCreditControl_DEV
(CONTRACTID, pk_id, <etc>)
VALUES 
(?, ?, <etc>)
ON DUPLICATE KEY UPDATE CONTRACTID = ?, pk_id = ?, <etc>
    SqlQueryFieldBindings=
    CONTRACTID, pk_id, CONTRACTID, pk_id, <etc>
    Thanks for the help


  • 11.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 10:59

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

    Originally posted by: prashanth

    I did it the hard way and counted them. I have seen those examples for whom the input to the Get MetaData node is from a db output or an existing file with header information. Is this what you are referring?


  • 12.  RE: JDBC Execute Node with Parameters

    Employee
    Posted 12-09-2014 11:37

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

    Originally posted by: ejones

    Exactly what I am referring to. Thanks. I'm trying to supply enough clues without having to go build an example.