Getting invalid column type while inserting data in to database

Question

I am using database connector to insert data in database in Mule-4 the insert query statement looks like this. I have tested multiple times. below is the sql query using for inserting data

INSERT INTO TABLE
        (
            GUID, 
            TIME_GENERATED_ON, 
            ENVELOPE_ID,
             SUBJECT, 
             USER_NAME,
            EMAIL, STATUS, 
            CREATED_ON, VOID_ON, DECLINED_ON, SENT_ON, DELIVERED_ON, 
            AC_STATUS, AC_STATUS_DATE, AC_HOLDER,
            AC_HOLDER_EMAIL, AC_HOLDER_LOCATION, SIGNING_LOCATION, SENDER_IP_ADDRESS,
            ENVELOPE_ID_STAMPING, AUTHORITATIVE_COPY, RAW_XML_DATA,WORK_OUI,UPI
        )
        VALUES
        (
            :guid, 
            TO_TIMESTAMP(:timeGenerated, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
            :EnvelopeID, :Subject, :UserName,
            :Email, 
            :Status, 
            TO_TIMESTAMP (:Created, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
            TO_TIMESTAMP (:Void, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
            TO_TIMESTAMP (:Declined, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
            TO_TIMESTAMP (:Sent, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
            TO_TIMESTAMP (:Delivered, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
            :ACStatus,
            TO_TIMESTAMP (:ACStatusDate, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
            :ACHolder,
            :ACHolderEmail, :ACHolderLocation, :SigningLocation, :SenderIPAddress,
            :EnvelopeIdStamping, :AuthoritativeCopy, :originalPayload,  :WorkOui, :upi
        )

input parameters looks like this. I am supplying it dynamically.

{
    guid:payload.Guid,
    timeGenerated   :payload.TimeGenerated,
    EnvelopeID:payload.EnvelopeID,
    Subject: payload.Subject,
         UserName   : payload.UserName,
         Email:payload.Eamil,
         Status:payload.Status,
          Created: payload.Created,
          Void:payload.Void,
          Declined:payload.Declined,
              Sent    :payload.Sent,
            Delivered:  payload.Delivered,
           ACStatus :payload.ACStatus,
             ACStatusDate       :payload.ACStatusDate,
             ACHolder  :payload.ACHolder,
              ACHolderEmail  :payload.ACHolderEmail,
                  ACHolderLocation : payload.ACHolderLocation,
                    SigningLocation  : payload.SigningLocation,
                    SenderIPAddress: payload.SenderIPAddress,
                     EnvelopeIdStamping: payload.EnvelopeIdStamping,
                    AuthoritativeCopy :payload.AuthoritativeCopy,
                    originalPayload:vars.originalPayload,
                    WorkOui:payload..WorkOui,
                    upi:payload.upi
}

I am getting this error can some one help on this

org.mule.runtime.api.exception.MuleRuntimeException: java.sql.SQLException: Invalid column type
Caused by: org.mule.db.commons.shaded.api.exception.connection.QueryExecutionException: Invalid column type
Caused by: java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8921) ~[?:?]

Providing the names of the arguments says little about the values or types. Please provide the schema of the table and the actual values and data types of the payload contents. — aled, Dec 15 '22 at 18:36
You probably must have got more info in the error message. But looks like you did a typo in `WorkOui:payload..WorkOui,` and used two dots, which makes it an array — Harshank Bansal, Dec 16 '22 at 05:06

score -1 · Answer 1 · edited Dec 17 '22 at 08:15

When using xml as one of the parameter for the database column using write(payload) solved this problem now the final parameters are

{
    guid: payload.Guid,
    timeGenerated: payload.TimeGenerated,
    EnvelopeID: payload.EnvelopeID,
    Subject: payload.Subject,
    UserName: payload.UserName,
    Email: payload.Email,
    Status: payload.Status,
    Created: payload.Created,
    Void: payload.Void,
    Declined: payload.Declined,
    Sent: payload.Sent,
    Delivered: payload.Delivered,
    ACStatus: payload.ACStatus,
    ACStatusDate: payload.ACStatusDate,
    ACHolder: payload.ACHolder,
    ACHolderEmail: payload.ACHolderEmail,
    ACHolderLocation: payload.ACHolderLocation,
    SigningLocation: payload.SigningLocation,
    SenderIPAddress: payload.SenderIPAddress,
    EnvelopeIdStamping: payload.EnvelopeIdStamping,
    AuthoritativeCopy: payload.AuthoritativeCopy,
    originalPayload: *write(vars.originalPayload)*,
    WorkOui: payload.WorkOui,
    upi: payload.upi
}

How did you find which of all fields was the problematic one? Why this solution works? — aled, Dec 18 '22 at 12:58

Getting invalid column type while inserting data in to database

1 Answers1