Using dns in trial version

I created an ODBC Dns entry in my local pc (windows 10). How can I make Iguana see this new connection?

What type of DSN, User or System? The former will only be visible to Iguana if the Iguana service is configured (in Service Manager) to run as the user the DSN was created under. Create it as a System DSN and it should be available to all Windows accounts on your PC.

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

Yes. I created it under the System DSN (see attachment). Should it show up in the settings section of Iguana?

Attachments:

You must be logged in to view attached files.

No, the settings section only displays available drivers. It does not display specific DSNs for ODBC connectivity.

You should now be able to configure a connection object in a translator using the new DSN:

local msdb = db.connect{api=db.SQL_SERVER,name='IguanaSqlServer',user='<username>',password='<password>',live=true}
local rSet = msdb:query{sql="<some sql statement>"}
<do something with result set rSet>

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

Thanks again. So now I get this error in the translator:

ODBC Error:
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Should this be a 32 or 64 bit driver? And is that what the message is complaining about?
I have sever sql server drivers available (see attachment) and I’ve tried them all and still get the above message.

Attachments:

You must be logged in to view attached files.

The architecture for the DSN must match the architecture of Iguana. 64bit DSN for 64bit Iguana, 32bit DSN for 32bit Iguana.

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

Yes and make sure it in the System section. This is fairly standard support question which you are probably best just to submit to support.

Well, I’m unclear as to what version of Iguana (32 or 64 bit) I’m using but I’ve tried creating a system dsn for every driver for sql server on my machine. I even downloaded the latest 64 bit drivers from MS (but when I create the DSN it still shows as 32 bit under platform in the ODBC source admin tool). All of these give me the same message in Iguana.

I’d ask support but since I’m just using the eval version I’m not sure they would respond.

The installation kit you downloaded will have either “x64” or “x86” as the last part of the filename before the extension. The one with “x64” is 64 bit; “x86” is 32 bit.

What specific version of Windows are you running?

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

I have this: iguana_6_0_7_windows_x64
Windows 10 Enterprise 2016 LTSB 64-bit.

Ok, so you have the 64 bit version of Iguana installed.

When you Click the “Settings” gear from the Windows Start menu, what do you see when you type “ODBC” into the search bar (“Find a setting”) at the top? I get both a 32 bit and 64 bit configuration option. If you’re not seeing that … there’s something wrong with your Windows installation. If you are seeing it, make sure you select the 64 bit ODBC option.

Attachments:

You must be logged in to view attached files.

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

That was it. Wasn’t paying attention to the version of the odbc admin I was opening. Thanks much!

Ok. So the db connection is working but I can’t get the sql to work. I’m basically just trying to take the hl7 read in via a file connector and insert it into a table.

local rSet = msdb:execute{sql="Insert into interface.dbo.hl7trans (LabOrg,Org,Transtext,Status,EntryDate,EntryUser) values ('LabTest','Mirth',Data,'Xpending',currentDate)"}

I’m assuming that I access the hl7 message from Data (as in function main(Data)) but that doesn’t work. It thinks Data is a column name instead of actual data.

I would recommend creating a table vmd with the database schema and then using db.tables and db.merge.

DB Tables:

http://help.interfaceware.com/api/#db_tables

and

http://help.interfaceware.com/api/#db_merge

and

Database Fundamentals

Should get you started.

Thanks for the info. So it seems I need to create a vmd file that is particular to the HL7 message I’m using. Is that correct?

Depends on what you expect to do with the message once it’s in the database. If you need to index the table on some of the HL7 fields, then yes. If you’re simply logging each message to the database as its received, then you really only need VMD Studio’s database table modeling functionality.

On a related note: I had created a separate post that detailed other options for logging the message content, but it’s not appearing in this thread. I suspect it may have ended up in the moderation (or SPAM) queue. I’ve pinged IFW to take a look, rather than attempting to repost (tried that, get a complaint back from the forum software that I’ve already posted it).

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

That is all I’m trying to do. Just save the incoming message to a db table for archival purposes. The table has a couple of columns unrelated to the message that also needs populating (like system it is coming from and practice it is going to) which we usually hard code in the interface.

You can do what you want without creating a VMD, but it’s not exactly intuitive. The problem is that the db.execute() method takes an SQL string as an argument, and Lua variables are not interpolated in the string. So you have to build the query with the value of the Data variable converted into something that works as a VALUES parameter. Here’s an example of something that works:*

function main(Data)
   local logDb  = db.connect{api=db.SQL_SERVER,name='IguanaInt',user='xxxxx',password='xxxxxx',live=true}
   local logHex = '0x'..filter.hex.enc(Data)
   local logSql = "INSERT INTO IguanaInt.dbo.MessageLog (SrcSys,SrcApp,TgtSys,TgtApp,MsgTxt) VALUES ('BMCLab','Cerner','BMCHS','ECW',"..logHex..")"
   local logRs = logDb:execute{sql=logSql}
end

* I probably should have created my test table with the same table and column names you did, oh well … and the data type I used for the MsgTxt column is VARCHAR(MAX) FYI.

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

Thanks. That did the trick. Guess I have to get used to the lua language. Seems a bit different that javascript or c#.