Topic
  • 3 replies
  • Latest Post - ‏2010-05-12T15:54:28Z by SystemAdmin
SystemAdmin
SystemAdmin
532 Posts

Pinned topic issue to insert new record into table (db2)

‏2010-05-11T00:25:26Z |
Hello,
I found an example here(sql_transaction_sample.zip) and now I tried to insert some records into a table.

1. I used sql-call builder to get some records with service operation builder(no parameters in SQL)
2. I used another sql-call builder "insertDatas" to write my insert statement with 5 parameters.
3. I used transaction builder which has only one step "insertDatas".
4. I used a method builder to do insert(multi rows insert). this method builder has an argument "Years" as IXml. in loop I assign each year to insert inputs "webAppAccess.getVariables().setXml("insertDatasInputs",year);" and then " webAppAccess.callMethod("transaction.execute","insertDatas");"
5.I create a serviceoperation for the method.

6.I create a service consumer in which I use action list to run first sql-call builder to get records and then run the insert method.

problem is I got an error "The value of positional parameter 1 in builder "insertDatasStatement" was null; you cannot use "Automatic" typecasting for NULL parameters."

I tried to override the inputs in service consumer builder with results of the first sql-call, but it didn't work either,

anyone got idea, did I miss something ?
Updated on 2010-05-12T15:54:28Z at 2010-05-12T15:54:28Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: issue to insert new record into table (db2)

    ‏2010-05-11T14:22:45Z  
    Hello,

    I have also two questions to the example "sql_transaction_sample.zip".
    1. In SQLTransactionService.model there is a method "updateTravelersAction", it has an argument "travelers" with type IXml. How can I pass the value to this argument in service consumer model?

    2. in this method
    "// set the input variable for the updateTravelersSQL SQL Call
    webAppAccess.getVariables().setXml("updateTravelersSQLInputs", traveler);"

    what does this command mean? set all parameters in update operation depend on traveler?
    I don't understand how it works and if I use insert operation instead of update operation,what do I need to do?

    pleas help me on this
    thanks a lot !!
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: issue to insert new record into table (db2)

    ‏2010-05-11T23:16:40Z  
    Hello,

    I have also two questions to the example "sql_transaction_sample.zip".
    1. In SQLTransactionService.model there is a method "updateTravelersAction", it has an argument "travelers" with type IXml. How can I pass the value to this argument in service consumer model?

    2. in this method
    "// set the input variable for the updateTravelersSQL SQL Call
    webAppAccess.getVariables().setXml("updateTravelersSQLInputs", traveler);"

    what does this command mean? set all parameters in update operation depend on traveler?
    I don't understand how it works and if I use insert operation instead of update operation,what do I need to do?

    pleas help me on this
    thanks a lot !!
    Hello,
    now I tried another way to use the sample above for update operation.

    1. in updateTravelersSQL SQL-call builder I have 4 parameters like
    "${Variables/*updateTravelersSQLInputs*/Parameter1Value}"

    2 in Method "updateTravelersAction" I didn't use argumente "travelers" instead, I pass the value to "travelers" with code "travelers = (IXml) webAppAccess.getVariables().getXml("getTravelersResults");"
    In loop I set xml like this "webAppAccess.getVariables().setXml("updateTravelersInputs", traveler);"
    actually, "updateTravelersInputs" is not for SQL-Call Builder with the name "updateTravelersSQL"

    3.So I set "value for input field" in input for operation section of service operation builder "updateTravelers" like follows:
    name: Myhouse and value ${Variables/updateTravelersInputs/Travelers/Traveler/MYHOUSE} and so on.
    I can't change the name of variables and the value exists

    but I still got this error message
    The value of positional parameter 1 in builder "updateTravelersSQLStatement" was null; you cannot use "Automatic" typecasting for NULL parameters.]

    I think if I can set the name and value in operation builder like this:
    name: Parameter1Value value: ${Variables/updateTravelersInputs/Travelers/Traveler/MYHOUSE}
    it could work but I cant change the name

    anyone has any idea ?
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: issue to insert new record into table (db2)

    ‏2010-05-12T15:54:28Z  
    Hello,
    now I tried another way to use the sample above for update operation.

    1. in updateTravelersSQL SQL-call builder I have 4 parameters like
    "${Variables/*updateTravelersSQLInputs*/Parameter1Value}"

    2 in Method "updateTravelersAction" I didn't use argumente "travelers" instead, I pass the value to "travelers" with code "travelers = (IXml) webAppAccess.getVariables().getXml("getTravelersResults");"
    In loop I set xml like this "webAppAccess.getVariables().setXml("updateTravelersInputs", traveler);"
    actually, "updateTravelersInputs" is not for SQL-Call Builder with the name "updateTravelersSQL"

    3.So I set "value for input field" in input for operation section of service operation builder "updateTravelers" like follows:
    name: Myhouse and value ${Variables/updateTravelersInputs/Travelers/Traveler/MYHOUSE} and so on.
    I can't change the name of variables and the value exists

    but I still got this error message
    The value of positional parameter 1 in builder "updateTravelersSQLStatement" was null; you cannot use "Automatic" typecasting for NULL parameters.]

    I think if I can set the name and value in operation builder like this:
    name: Parameter1Value value: ${Variables/updateTravelersInputs/Travelers/Traveler/MYHOUSE}
    it could work but I cant change the name

    anyone has any idea ?
    hello,

    I found why I got null exception.
    first I displayed the variable "updateTravelersSQLInputs" before setXml method:
    <updateTravelersSQLInputs>
    <Parameter1Value />
    <Parameter2Value />
    <Parameter3Value />
    <Parameter4Value />
    </updateTravelersSQLInputs>

    after I called setXml method:
    <Traveler>
    <RECORDDATE>2010-05-12 18:08:38.465</RECORDDATE>
    <MYHOUSE>2180307.760</MYHOUSE>
    <MYNEIGHBORHOOD>2567511.452</MYNEIGHBORHOOD>
    <MYZIPCODE>2240053.833</MYZIPCODE>
    <MYCITY>1260857.283</MYCITY>
    </Traveler>

    in SQL-Call Builder I used automatic for parameter list like:
    ${Variables/updateTravelersSQLInputs/Parameter1Value}

    that's why parameter1Value is always null, because after setXml there is no element with the name "Parameter1Value" in variable "updateTravelersSQLinputs". So what to do is fix it like "${Variables/updateTravelersSQLInputs/Traveler/MYHOUSE}" in sql-call builder paramenter list.

    and then set argument travelers in method "updateTravelersAction" in builder service operation by "value for input field", to do this you should give input schema, then WPF knows which elements in this travelers argument and give each parament its value like:
    ${DataServices/getTravelersSQL/execute/results/Travelers/Traveler/MYHOUSE}

    now you can call update operation in service consumer model, before that you should execute getTravelers.

    Thanks for your time !!