#ODBC

This is a sample Ensemble/Health Connect production which demonstrates how to receive an HL7 order (ORM) inbound from a file, extract fields (in this case, basic demographic information), and insert those into a table in an external SQL database via ODBC.  

Included in the zip file:

• Exported code
• Sample ORM message
• 'How to configure' doc 

I am tasked with using CACHE to insert data retrieved from a CACHE data base and insert it into an sql database. The columns in the sql table that I am trying to insert data into have names that contain underscores such as "ACCESSION_DATE" I found a utility in CACHE to connect to the sql data base and perform inserts. I have the data I need to insert. I need to pass the  data into the sql utility referencing the column names.

I tried using indirection to set the data into underscored variables, but that isn't allowed  in cache object script.

Anyone have suggestions as to how to do that?

I am trying to read data from MySQL Server 2012 to a stream  and I keep getting an error here is my code so far

ERROR

An error was received : ERROR <Ens>ErrException: <INVALID OREF>zGetBatchDetails+14^DDQTools.DQTGetBatchOpp.3 -- logged as '-' number - @' set sc=reStream.WriteLine(rec) '

Hi all,

I am new in Cache. I have to update a record with a long text field, for that,  I am using ODBC but the issue is that it is returning an error when I execute the ODBC  SQL statement because the field contains some commas, colon, double quotes, single quotes, and CR LF characters.

There is a way to escape this chars? I have seen I can save the field when I replace the single quotes with a double single quote (it's -> it''s) but I can not save the information when the field contains double quote chars. What about the other chars?

The statement is something like:

I have a service that that I would like to use to  poll the database table in MySQL server 2012 and get an id to use if the condition is met but have trouble achieving this here is what I have so far any suggestions please.

Method OnProcessInput(pInput As EnsLib.SQL.Snapshot, Output pOutput As %RegisteredObject) As %Status
{
 
  set pRequest=##class(SamsReq).%New()
      set pRequest.pID=pInput.Get("pID")
     
     
 

< set sc=..SendRequestSync("DQTT",pRequest,.pOutput)

 quit sc
}
 
 

the error that I am getting

>}

Howdy Developers of InterSystems Ensemble,

Has anyone experienced an alert generated from a business operation (BO), which uses EnsLib.SQL.OutboundAdapter? That comes with an error message as below:

ERROR #6022: Gateway failed: Execute. + ERROR <Ens>ErrGeneral: SQLState: (HY000) NativeError: [10054] Message: [Cache ODBC][State : HY000][Native Code 10054] [d:\cachesys\bin\cache.exe] [write_all: send() returned SOCKET_ERROR. Reason: (10054, 0x2746) An existing connection was forcibly closed by the remote host. ] (alert request ID=710761).

Hi, I am trying to execute a store procedure within an ensemble operation to connect to aMsSQL database and I am having problem to pass the input arguments... I have tried several things but only one seems to work and it is not the ideal solution.

This is the code that works...as you can see the parameter is added to the sql query...not the best solution but the only that works at the moment.

Hi

I am fairly new to Ensemble development.

I was wondering what is the best process of returning the resultset from a SQL outbound adapter to the BPL that had invoked it.

Thank you for your help.

Kind Regards,

Salma

I am running InterSystems Ensemble 2016.2 on ubuntu and trying to connect to a remote MS SQL server database.

Insofar, I have successfully configured my ubuntu machine to connect to the remote MS SQL server database using unix-odbc. That is:

• Telnet connection works
• tsql (test sql) connection works
• isql command succesfully connects to sql server and I am able to execute queries on ubuntu.
• The DSN for the isql command are defined in /etc/odbc.ini and /etc/odbcinst.ini and should be available systemwide.
• The DSN in the odbcinst.ini uses the microsoft odbc driver 13 for Sql Server for linux. 

What happens if you don't declare a Persistent value when you call ExecuteQuery()? What does Ensemble set as the key value for your query? I have a query that I've executed on SQL Server, and I get 15 rows, but because this is my second time querying the data Ensemble thinks it exists. 

The Method in question is SelectProviderClarityAudit. If I call this query multiple times it is not returning the same number of results each time in Ensemble.

In OS X 10.8 and earlier, PHP was built with ODBC support, making it easy to connect to a Caché database:

$ php -r 'phpinfo();' |grep 'ODBC Support'
ODBC Support => enabled

However, this support was removed in OS X 10.9:

$ php -r 'phpinfo();' |grep 'ODBC Support'

Some people use Homebrew, MacPorts, or Fink to get their tools back. If you're comfortable coloring a little outside the lines (i.e., using sudo and the command line), you can build the odbc.so or pdo_odbc.so extension (depending on which API you prefer), and use it with Apple's version of Apache and PHP. You'll need the following:

I ran the below query in three different modes.  Coordinated Universal Time is 5 hours ahead of Eastern Time so there is difference in value from column 1 and column 2 in ODBC, Display mode, but not in Logical Mode. By default the query executes in ODBC mode when we query the data from outside world(via ODBC connection). 

I don't know  why query 2 and query 3 outputs different from query 1.

Query 1. Ran in Logical mode, DATEPART() took in memory stored timestamp data(stored in UTC)

'Hour ' returned are same for both the columns

I am trying connect DB using Oracle DB link (ODBC).  I am getting the following error. Please help me fixing this error.

I am using Oracle 12 c.

Thanks,

Raj

Hi,

I'm developing an integration between Caché servers by ODBC conection, and I have the following problem.

In this call:

call COSClass_Methode('222169^^98^155^64530^06:30^021542987897458855441112877855^1^0^281992^GC')

the ODBC driver is truncating the string to 50 characters.

If I run this same command with $system.SQL.Shell(), this doesn't occur.
I did a test creating several parameters for COSClass_Methode, and they all have a 50 character limitation.

Does anyone know why the ODBC Cache Driver limits the number of characters per parameter of this type of call?
Or, how can I adjust this?

Hi, I would like to read a row out of an external SQL table and reference the returned results directly from my cache class. I've set up a  link table and a SQL gateway connection. but I'm not real sure how to use them in COS. Anyone have examples out there, or some assistance? Thank you.

Hi All, I tried to connect Crystal report with Cache 5.0 Its Show Crystal Report error as "The report you requested requires further information." How to resolve the issue in C#.

Hi all,

I have a non objectscript application connecting to a cache instance via ODBC and one column is a list of serial objects. The output from the query contains a lot of special characters and I'm hoping there's a better way to get this data back so I won't have to perform extensive parsing on the application side.

I've tried using the $ListToString() function, but that didn't help much, probably because the list contains complex objects rather than primitives. 

Any help will be greatly appreciated!

I am trying to set up a sensor in PRTG to connect to Cache, specifically the ens_util.log, so that I can have a live feed of my error count. I am having trouble getting the sensor to log in to Cache. Has anyone had any luck getting PRTG to connect on the database level? Thanks!

Hi ,

     i installed and configure the cache odbc ODBC-2016.1.1.107.0-lnxrhx64.tar in centos7.

     it connected  while executing  in terminal .(i.e isql -v DSN) 

     odbc_connect work in executing  php shell script  in terimal.

     but odbc_connect not working in PHP web applcation.

Thanks,

Prabakran A.

When working with a large query executed though an ODBC connection what is the best way to allow the paging of the results at the client side.  I have tried some methods using %VID and similar methods, but these really don't seem to work as the value returned is related to the ID of the data and not the position in the results set.  What would be ideal is if the value seen in the management portal when you check of "Row Number" was available to external queries through ODBC.  I have not seen a way to return this however.   This would be similar to SQL server Last n and Skip n capabilities.

I'm trying to use the standard TSQL DECLARE syntax but I get the following error:

[%msg: < IDENTIFIER expected, @ found^  DECLARE @>]

How do I declare a variable?  For instance:

I get this on some queries in some namespaces.  For instance, this query:

It works fine in HSBUS but in HSREG it throws the error.

Server closed communication device

Does anyone know what would cause this? Would it log something more useful somewhere?

Hi All,

We are accessing the InterSystems`s cache database via UNIX ODBC and displaying the data in PHP website . Recently we have upgraded the PHP version to 5.6.  We are getting the nondisplayable characters (�) for only strings. But the numbers and date fields are displaying correctly. 

While querying the database via ISQL everything working fine (No Special characters).

I have looked around the internet and found the PHP 5.6 changed the default character to UTF-8.

For this issue anything, we can do from cache side.

Thanks in advance.

I've asked a lot of questions leading up to this, so I wanted to share some of my progress.

The blue line represents the number of messages processed.  The background color represents the average response time.  You can see ticks for each hour (and bigger ticks for each day).   Hovering over any point in the graph will show you the numbers for that period in time.

This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.

Here is the query used:

Hi All,

            I have a general query in regards to developers experience on extracting data from cache databases and the most efficient way to do so. I work with a number of clients who have applications with cache databases and require the data off the host system and onto data warehouse platforms for research and analysis. Often they require the data in source state which means the extracts are often simply a table scan of the entire database table without any aggregation or manipulation.

It would be convenient to be able to fetch the whole query result in one operation instead of looping through every row. A fetch_all() method or something similar would provide this functionality I guess. Is there a reason why this is not implemented?

I have a class with a string property with DISPLAYLIST and VALUELIST parameters specified.

When I query this class via ODBC I receive logical value as it is and not a display value.

Is there any way (without stored procedure) to get display value?

Hello everyone,

I'm trying to authenticate a user(Health Share clinician) from a Java Application. 

I 'm already connected to Caché and able to run SQL commands.

My question is: How can I authenticate a user using only SQL? In fact, what I want is verify if the users exists in the base and if the given password is the same used in Health Share.

There is a column 'password' in Security.users table but I'm not able to see its content, even so, I don't know which hash function to use to compare with.

We are creating a package (written in Caché Object Script) that will provide access to an external DB (MySQL). Because applications that use our package will be run from machines with various, potentially unexpected, operating systems, we’d like to establish a connection to the external DB without using DSNs (we’ve heard that setting up DSNs on certain non-Windows machines can be cumbersome and problematic).

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table. 

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one  (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from  MySQL "posts" table.

How do I synchronize deletions?