Perform Database Query

[bobbie]

I have created a transformer that needs to Perform a Database Query. I have tried different things but I keep getting invalid syntax when I try and deploy my channel. Does anyone have a working example. What I am trying to do is take a value that I am recieving and go to the database and replace it with a different value.

Thanks...

[chrisl]

Re: Perform Database Query

Can you paste your query/transformer code.
Thanks,
-Chris

[bobbie]

Re: Perform Database Query

this is the code from the mapping box:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('sqlserver', 'HL7_Test', 'webvmc', 'webvmc'
var result = dbConn.executeCachedQuery('Select StateID FROM State WHERE Abbreviation = SC'
dbConn.close();
return result;

What I will eventually want to do is replace SC with msg['PID']['PID.11']['XAD.4'] if it will do that.

[chrisl]

Re: Perform Database Query

So two things:

1. You need to make sure this is in a Javascript step rather than a mapping step. While you can execute some code in the mapping steps, they will attempt to assign the result of the execution to a var (and the use of "return" is not allowed).

2. In your database connection, you are just passing it "sqlserver". This needs to be the full JDBC connection string for the database:

You SQL Server connection string could be something like this:
jdbc:jtds:sqlserver://localhost:1433;databaseName=mydatabase;domain=mydomain

[bobbie]

Re: Perform Database Query

I have my database connection working. It is getting to the database. Now I need the value from my select statement. My select statement only returns 1 value and it is an int. 'select StateId from state where abbrv = 'SC''. My return value is 36. I am not able to get anything from the result of the executeCachedQuery. It puts it in some sort of cached resultset and I don't know how to get it out. IF anyone has an example that would be great. Or if there is a better way please let me know.

Thanks.... bobbie

[chrisl]

Re: Perform Database Query

use:

result.getInt(0)

The 0 is the column number.

[bobbie]

Re: Perform Database Query

Now I get an error "invalid column index"

here is what I have in the transformer

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:sqlserver://localhost:1433/HL7_Test', 'user', 'pass'
var result = dbConn.executeCachedQuery('Select StateId from State where abbreviation = ' + "'" + msg['PID']['PID.11']['XAD.4'].toString() + "'");
var returnval = result.getInt(0);
localMap.put('StateId', returnval);
dbConn.close();

[chrisl]

Re: Perform Database Query

Sorry it's

result.getInt(1)

I forgot that they are 1-index based.