Eviware Forum

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

Hi,

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

regards!

/Ole
eviware.com

Sorry I tried that to no avail.

Hi,

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

regards,

/Ole
eviware.com

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

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

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

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

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

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

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

Hi,

this is an invalid error-message, just ignore it.

By default new connections are in autoCommit mode (ie each statement gets committed automatically), but maybe the commit is neccessary. So what happens if you put


into the SQL field?

regards,

/Ole
eviware.com

Hi,
I just get the message under the data log stating "wrote data xxms".  However nothing is populated in the database.

I found out one more thing. SoapUI Pro uses JDK 1.6.  We are running Oracle 10g, which does not have a driver for JDK 1.6.  It still seems very strange that I am able to access the DB using Groovy scripts and select records using the Data Source GUI. 

Thanks

Hi Ryan,

ok.. I'm going to try to reproduce this with another database before I move on to installing Oracle.. I'll get back to you with my findings..

regards!

/Ole
eviware.com

Ryan

I used insert statement like( below ) for SQL Server 2005.. and was  successful in updating the DB.   readcode and readCity are test properties values read from response..
Note: 'apupdate[' is the table name.

insert into apupdate(apcode, apcity) values ('${#TestCase#readCode}','${#TestCase#readCity}')

hope this might help you...

Thanks, for the suggestion,but still no insert.

Can someone tell me this.. when you are in the DataSink Tab and you specify the properties and associated values, do the values show up in the DataLog below...mine are not, but did not know if they should?


Also are there logs that I can enable or view that might help me determine if any communication is happening with the database?


Regards.

Hi!

The DataLog should show rows containing the property-values passed to the DataSink for each execution, ie if you have 2 properties in your DataSink the log will show those properties' values..

Regarding the logging, maybe you could try enabling oracle logging by adding the -Doracle.jdbc.Trace=true argument in soapuipro.bat to the run arguments (read more about this option at http://www.oracle.com/technology/tech/java/sqlj_jdbc/pdf/11.1%20logging%20white%20paper.pdf)

Hope this helps,

regards!

/Ole
eviware.com