Hi I've got a service setup to query a sql server via ODBC.

I've got all the parameters etc setup and everything is ok

My Query is

Hey I would like to INSERT mulitple rows to 1 table. I need to intersystems cache equivelant for

INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
(value_list_3);

Have tried this post https://community.intersystems.com/post/how-execute-multiple-query-or-script-sql-management-studio
but still got an error

Hi, Community,

This post will demonstrate how to display data on the web by using Embedded Python , Python Flask Web Framework and Jquery datatable

We will display processes from %SYS.ProcessQuery table.

Hello,

We are sometimes asked for examples of how to do 3rd party SQL database selects/updates/inserts/stored procedures from an Ensemble SQL Business Operation.

In the attached file there are 4 sample Ensemble Operations that demonstrate four different types of transactions with an external SQL DB:

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.

Overview

Encryption of sensitive data becomes more and more important for applications. For example patient names, SSN, address-data or credit card-numbers etc..

Cache supports different flavors of encryption. Block-level database encryption and data-element encryption. The block-level database encryption protects an entire database. The decryption/encryption is done when a block is written/read to or from the database and has very little impact on the performance.

With data-element encryption only certain data-fields are encrypted. Fields that contain sensitive data like patient data or credit-card numbers. Data-element encryption is also useful if a re-encryption is required periodically. With data-element encryption it is the responsibility of the application to encrypt/decrypt the data.

Both encryption methods leverage the managed key encryption infrastructure of Caché.

The following article describes a sample use-case where data-element encryption is used to encrypt person data.

But what if you have hundreds of thousands of records with an encrypted datafield and you have the need to search that field? Decryption of the field-values prior to the search is not an option. What about indices?

This article describes a possible solution and develops step-by-step a small example how you can use SQL and indices to search encrypted fields.

I want to override the Get and Set methods of a class property. The class maps to a pre-existing global. The property is defined like so:

Property Invalid As %Library.Boolean;

with the property mapping to a node like ^GLOBAL(Code,"INVALID")=1
Code is a property in the same class.

The value can be 0 or 1 or the node might not exist. When it doesn't exist I want the value of the SQL field to come out as 0 (false).

If I have defined a class query in one of my classes and I want to use that query from a method of another class, what are the pros and cons of using the %SQL.Statement interface versus the %Library.ResultSet interface?

I believe %SQL.Statement is the newer interface.

So if the old way is:

USER>s rs=##class(%Library.ResultSet).%New("%Library.File:FileSet")
 
USER>s sc=rs.Execute("c:\s\","*.txt")
 
USER>w sc
1
USER>while rs.%Next() {w !,rs.Data("Name")}

...

then the new way is:

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.

See Part 1 here.

Part 2: Index Handling

Hi everyone, how do you run multiple quires?

I have tried couple of different ways, but not working.

SET sql = 2

Set sql(1) = "UPDATE QUERY"

Set sql(2) = "UPDATE QUERY"

Set sqlStatement=##class(%SQL.Statement).%New()

Set sc1=sqlStatement.%Prepare(.sql)

If $$$ISOK(sc1) {

Set tResult = sqlStatement.%Execute()

}

else{

$$$LOGERROR("Failed")

}

InterSystems FAQ rubric

The ID of the last updated record can be obtained using the SQL function LAST_IDENTITY().
* This function can be used with embedded SQL or ODBC but not with Dynamic SQL, SQL Shell, or the Management Portal's SQL interface.

Here's an example of usage with simple Embedded SQL:

The select query here "select Settings from Ens_Config.Item" in the SQL Query of management portal returns this:

ValidationHost 7ActOnTransformErrorHost1 ReplyCodeActionsHostE=D AlertOnErrorHost1 9 AlertGroupsHost$LabAlertGroup,1-CriticalAlertGroup BusinessPartnerHost

How do I isolate just one of the settings e.g. ReplyCodeActions or LocalInterface

For example, i want to search for all entries that have a value for ReplyCodeActions

I realize i can open the production in studio and search for those setting values.

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