Forum Discussion

RYAN_HORST's avatar
RYAN_HORST
Occasional Contributor
17 years ago

Inserting into Oracle table as DataSink

I created a generic temp table in Oracle to make sure that I am getting a good connection, and I was able to pull data from this table.
However when I try to do an insert I always get the following error.
TestCase failed [Cancelling due to failed test step:java.sql.SQLException: Invalid column index], time taken = 1225

The SQL statement I am using is as follows:
insert into  XXX.temp_table
(MY_FIELD)
values
(${TempTable#MY_FIELD})


I am using a Transfer step from another DataSource to populate the ${TempTable#MY_FIELD} property. 

The Oracle Driver is: oracle.jdbc.driver.OracleDriver

Connection String is: jdbc:oracle:thin:username/pwd@host:port:SID


I am able to insert to this table using standard Oracle tools. Please advise on what my issue might be, and if there are additional logs I should review to help identify the problem.

Regards
  • omatzura's avatar
    omatzura
    Super Contributor
    17 years ago
    Hi,

    hmm.. Maybe you need to surround the value with single quotes (if it's a string)?

    regards!

    /Ole
    eviware.com
  • omatzura's avatar
    omatzura
    Super Contributor
    17 years ago
    Hi,

    ok.. hmm.. what type does this column have in the db?

    regards,

    /Ole
    eviware.com
  • RYAN_HORST's avatar
    RYAN_HORST
    Occasional Contributor
    17 years ago
    Thanks for the speedy replies.

    The column is defined as VARCHAR2 (15 BYTE), but I think there is something else at work here.  Even if I completely remove the insert statement I still get the invalid column index message. 

    This is my first attempt to use SoapUI so please forgive me for these next two questions that may or may not be related to this issue.
    When I hit the green arrow while viewing my DataSink configuration I get the message "wrote data in xx ms", as if something has just occurred?  But there is no change in what I see in the Data Log or in the actual database?  And in fact I do see a value in the Value field beside the Property field, but nothing in the data log other than the Name of the property.  Is  this behavior correct?


    Regards

    RH
  • omatzura's avatar
    omatzura
    Super Contributor
    17 years ago
    Hi Ryan,

    that does sound strange, running the DataSink as you describe should at least write the desired value to your database.. can you share your soapUI project file so I can have a look?

    regards,

    /Ole
    eviware.com
  • RYAN_HORST's avatar
    RYAN_HORST
    Occasional Contributor
    17 years ago
    Sorry for the delay on getting you the project file.  The good news is that I have been able to create the functionality I need via a Groovy script.  But my simple insert project still does not work populate the database.  You will see that I have commit in my sql statement, I am assuming I need it there.  I have tried hard coding values instead of using the property value and it still does not work. 

    Regards
  • omatzura's avatar
    omatzura
    Super Contributor
    17 years ago
    Hi,

    I'm very sorry, I think I have been giving you incorrect instructions.. The SQL statement is called as a prepared statement and must have parameters ordered as the properties in the DataSink step. Upon each execution the corresponding property-values will be assigned to the statement before it is executed.

    So in your case the statement should be something like

    insert into  XXX.temp_table
    (MY_FIELD)
    values
    (?)

    - if MY_FIELD is a string it will need to be quoted

    Make sure that the DataSink contains one property, whose value will be assigned to the ? in the statement on each run..

    Hope this works a bit better, let me know!

    regards,

    /Ole
    eviware.com
  • RYAN_HORST's avatar
    RYAN_HORST
    Occasional Contributor
    17 years ago
    Thanks again for the reply.

    Here is my sql statement:
    insert into xxx.temp_table (MY_FIELD) values (?);
    commit;

    The DataSink has one property which has been quoted.

    Is it correct to end with semi-colons and provide the commit explicitly?

    I am getting the message 'wrote data in xxms' and the error logs are clean.  But the DB is still not doing the insert.

    Regards
  • omatzura's avatar
    omatzura
    Super Contributor
    17 years ago
    Hi!

    hmm.. you should probably quote in the SQL (and not the value itself):

    insert into xxx.temp_table (MY_FIELD) values ('?')

    and the commit shouldn't be neccessary.

    Does that help?

    /Ole
    eviware.com
  • RYAN_HORST's avatar
    RYAN_HORST
    Occasional Contributor
    17 years ago
    Hi,
    Well I did miss something I should have caught...in the error log I am getting the following message.
    Thu Apr 03 14:36:56 EDT 2008:ERROR:java.sql.SQLException: No suitable driver

    This is very strange since I can specify the same connection and drivers in the groovy script and get this to work using the same insert statement.

    Regards