#SQL

Hello,

I want to know if there is any possible way to use the same query for different ODBC  schemas.

Thanks.

Does anyone have any sample code for updating Caché users (and specifically Full Name and Comments) via SQL?  

Bonus points for knowing specifically what security resource needs to be assigned to allow someone to run the SQL.

Thanks in Advance!

Ben

Without installing Kerberos has anyone Authenticated a SQL JDBC connection? Currently we are using local SQL Accounts to sign onto External SQL Databases, but we are being told that we need to switch to Service accounts that live on a Active Directory Domain. 

I wrote with a little help a ZAUTHENICATE to do the Authentication for Ensemble, can I use something like that to connect to an External SQL Database using a Service Account on a Active Directory Domain?

Thanks

Scott

I  have generated a class using the linked procedure wizard however  I can't get it to work if the  datatype  of one of the parameters is VARCHAR(MAX).  It works fine if I change it to say VARCHAR(500) and  rerun the  stored procedure wizard. 

I get the following  error returned.:

ErrorMsg:   SQLState: (07002) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

Can anyone see what I am doing wrong?  I think it is to do with the way I am using the  %Stream.GlobalCharacter?   Any help is much appreciated.

Class generated by linked procedure wizard:

Hi All

I'm looking for the a simple-quick-easy solution to monitor a SQL table thought Ensemble.

I have a process that update a DataBase ,a scheduled task that runs every night  (Not Ensemble)

In the end it updates a table (replica_status) with a new recored with two fileds:  Id, DateTime

I looked around the community but didn't find an answerd case.

I'm thinking on a Task that will run a sql outboud adapter BO that checks that table and send a alert if no new record was created yesterday

is this the right approach or is there's a better solution?

Thanks Gadi

Hi Community!

New session recording from Global Summit 2017 is already on DC YouTube Channel:

SQL – Things You Should Know

Hello glabal masters,

I am trying to develop WAS using Cache and Spring Framework.

The ON DUPLICATE KEY UPDATE is not working.

Source : 

The documentation only provides the syntax but does not provide any sample DDL for this case.

I am modifying some DB2 queries to Cache SQL but always meet some errors, please provide a sample query for this case.

Thanks.

I have  a SQL  procedure that I call into and it does some processing and passes back any data validations that fail. I would like to receive those message in my operation and act upon the message received .The question is how do you get output messages from a stored procedure I am using MsSQL 2012  with ensemble

Hello Global masters!

I developed Web with Spring framework (JAVA, Jdbc Cache) and I wrote SQL when I made it.

And Window program with VB6, Cache Object and VisM.

Is there a significant performance gap? SQL and Cache Object?

same function, same computer performance just different Java and VB6.

Thank you.

Minsu.

Hello Global masters.

I am junior developer about Intersystems cache in South Korea.

I want to develop process management program on server.

My code :

My new question regards connecting to the external database to insert the data. I have the following code:

Any insights, news, alternatives or experience about sequence pattern querying like with Oracle's MATCH_RECOGNIZE, more declarative, less simple direct COS way? Both obvious solutions (shadowing data to oracle cloud or implementing MATCH_RECOGNIZE compiler in COS from scratch) seem big overkill. Do Intersystems have plans to adopt this "2007 ANSI standard proposal" and "SQL:2016 standard"

I have a lookup table and record batch Table I would like to do a count on the records stored in that batch by counting the number of records in that batch that have  a certain key on the responseKey column. This column keys are stored in the look up table for comparison. So I would like to do a join sql pivot that will use my keys stored in the lookup as columns and count as values

so far I have managed to do this but this is not efficient I would like to fire that sql once not on every count

Hi,

The following sql statement is working through the SMP but couldn't make it work through the code.

The table is a linked table to oracle db.

Tried to change the date property in the linked table class from %Date to %String and also tried using %SQL.Statement and %Library.ResultSet

this work through SMP:

INSERT INTO PACKAGE.SOME_TABLE_NAME

(EFFECTIVE_START_DATE,MORE,DATA,HERE)

VALUES (to_date ('2018-04-01','yyyy-mm-dd'),123,456,789)

In the code i'm using '?' in the statement for the parameters.

So in the code it's go something like this (where the to_date is in the sql statement):

How to convert SQL persistent classes to JSON output - I have tried 1 option but appears to be very lengthy process.

I have many other SQL storage's defined and have to expose them as well to JSON. Can anyone please suggest a better approach.

My column names usually match with my Json properties.

My User.TestClass is the class with GetJsonList() as my method to return /output JSON formatted data.

The Code tried and SQL storage structure are as follows -

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?

Will an upgrade from 2015.2 to 2017.1.2 or higher freeze the query plans from 2015.2? Is there some minimum source version where this will work?

Has anyone use any other setting then -1 on the Stay Connected setting on a SQL.OutboundAdapter? I am having issues with a particular database and wonder if it would be beneficial since I only connect once a day to read the data from the Adapter to create an HL7 message.

Thanks

Scott Roth

Morning guys  I have a SQL operation that I use to read data and results to a record map. But seems to have trouble saving the objects to a record map as I get this message

ERROR ErrException:
zInsertObject+2^%Library.RelationshipObject.1 -- logged as '-' number - @''

please help  here is my code let me know if I missing anything thank you in advance

Hi all.

I have some problem with getting data from external source (PostgreSQL) via JDBC and load it in DeepSee and I resolved some problems while writing this question :)

So..Below I proved my steps of working:

Hi all,

I have an operation which uses the SQL outbound adapter to query a data source set up using a system DSN  on the server which uses the Intersystems ODBC35 driver (this is a cache db hosted elsewhere).

Whenever Ensemble runs the following query:

SELECT lnkdid,
c_fastsearch_code,
c_drugfull
FROM JAC.drug_basic
WHERE c_drugfull LIKE 'Para%'

No data is returned, however running the same query via SQL Server Management Studio, all expected rows are returned.

Also if I run  the following via Ensemble:

SELECT lnkdid,
c_fastsearch_code,
c_drugfull
FROM JAC.drug_basic
WHERE c_drugfull NOT LIKE 'Para%'

I came across How to import a tab separated text file into a SQL table programmatically?, which appears to indicate that I can pass the filename and all records will be imported. However, when I put use the Import method only a single record gets imported. However, all records are imported if I use the management console to import the file by selecting it and choosing the options.

Has anyone ran into this in the past?

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:

It is often necessary to sort the results of a query on a string field containing a combination of alphabetic and numeric characters. In cases like this the default string collation may not always return the data in the expected sequence.

An example of this may be where a select from Samples.Person should order the results by the street address, but firstly ordered by the street number part as numeric, and then by the street name.

The default query will return the results as follows:

SELECTName,Home_Street

FROMSample.Person

ORDERBYHome_Street

Hi, Community!  

Please find a new session recording from Global Summit 2017:

Optimizing SQL Queries

I am working my way through some SQL documentation.

The documentation follows and my question comes after.

A query supplied to %Prepare() can contain input host variables, as shown in the following example:

  SET minage = 80

  SET myquery = 3

  SET myquery(1) = "SELECT %ID AS id, Name, DOB, Home_State"

  SET myquery(2) = "FROM Person WHERE Age > :minage"

  SET myquery(3) = "ORDER BY 2"

  SET qStatus = tStatement.%Prepare(.myquery)

This talks about input host variables

I thought that input host variables only work with Embedded SQL, not Dynamic SQL

Thank you

I don't quite understand the %NOMERGE mentioned in the documentation.

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_from#RSQL_from_nomerge

Is there any specific example provided to illustrate the idea?

Thanks.

Hi,

I am afraid I am stuck again. I have ensemble connected with a MsSQL database and I have a service that calls a store procedure. All is working, and I can get strings from the database in the service. The problem is that the data the store procedure returns is suppose to be an HL7 message so I need to convert the string into HL7 format. I have been using the ##class(EnsLib.HL7.Message).ImportFromString  method but although I have a valid HL7 message from the string received from the DB...apparently the content of the message is being modified.