ERROR-200: Filter error

[shifting_18]

I'm doing a File Reader to Database Writer. I placed this code in the Edit filter page. Is it right?? By the way, what will I put on the Sql/Javascript part?? Thanks!!

var driver = "net.sourceforge.jtds.jdbc.Driver"; 
var address = "jdbc:jtds:«»sqlserver://saleslip2k/mirthdb";  
var username = "sa"; 
var password = "cardtech"; 
 
//DatabaseConnection 

var dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password); 
 
var patId = msg['PID']['PIaD.2']['CX.1'];  

var expression = "INSERT IGNORE INTO patient_id values ('" + patId + "');"; 
 
var result = dbConn.executeUpdate(expression);

//closes the connection to the DB
dbConn.close();

 
//finally you can return whatever you want to do with your filter.
return true;

-----------------------------------------------

But I received this error, patient_id is my table name

ERROR-200: Filter error
ERROR MESSAGE: Error evaluating filter
org.mozilla.javascript.WrappedException: Wrapped java.sql.SQLException: Invalid object name 'patient_id'. (0a0b339f-82bf-4d94-974f-7be2a41cb51d#20)

Post edited by: chrisl, at: 04/25/2007 00:17

[chrisl]

Re:ERROR-200: Filter error

Try:

1.

"INSERT IGNORE INTO 'patient_id' values ('" + patId + "');";

or

2.

"INSERT IGNORE INTO dbo.patient_id values ('" + patId + "');";

I'm not a SQL Server expert, but I've seen other examples where the full table path needs to be qualified (i.e. dbo.demographics.patient_id)

[shifting_18]

Re:ERROR-200: Filter error

File_to_db.doc (0 bytes)
Kindly check this file. I hope it uploads. Thanks!

[shifting_18]

Re:ERROR-200: Filter error

I cant uplaod..wahhhh

[chrisl]

Re:ERROR-200: Filter error

Shoot it to me in an email, chrisl [at] webreachinc.com

[nshaik]

Re:ERROR-200: Filter error

The JDBC connection string for SQL Server needs to have the port number.

var address = "jdbc:jtds:«»sqlserver://saleslip2k:1433/mirthdb";

Default Port is 1433. Check your server settings (Enterprise Manager / Right-click on server / Properties / General Tab / Network Configuration / TCP/IP / Properties)

[shifting_18]

Re:ERROR-200: Filter error

Thanks! I tried your suggestions! I put the port number already. I think there's something wrong with my sql code.
This is what I tried the latest..but it says an error occured 'near patient_id"..

var expression = "INSERT IGNORE INTO patient_info patient_id values ('"+patId+"');";

patient_info - table name
patient_id - field

[shifting_18]

Re:ERROR-200: Filter error

wait! Am I supposed to write the code on the Filter Page like what I did?
:huh:

[chrisl]

Re:ERROR-200: Filter error

Try:

"INSERT IGNORE INTO patient_info (patient_id) VALUES ('"+patId+"');";

Yes - it should be in the filter.

[shifting_18]

Re:ERROR-200: Filter error

Thank you guys!
It's already inserting onto the database but I'm still having this error:

Error-200: Filter Error
String index out of range: 6


This error always turn up even though I used Varchar(10) as my data type of patient_id when the value = 8. It's not out of range, isn't it? What does the error mean exactly?

Post edited by: shifting_18, at: 04/29/2007 18:31