New post

#SQL

16

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

I running a query and get the results inside a result set. Now I have to iterate through the result set many times. From the doc I've seen only the Next() method. Is there a way to reset the cursor? Otherwise what is a good data structure to save multiple rows of a table?

My code in this case is something like this:

set sql = "SELECT * FROM MY_TABLE WHERE X= '"_Y_"'"
set status = ..Adapter.ExecuteQuery(.rs, sql)
// somehow iterate the rs more than one time

0 6
1 1.3K

Hi,

The system I am working on processes large numbers of records. Inserts, updates and so forth. There are multiple processes that can potentially work on the same table at the same time.

It is an almost impossible task to try and time these processes to not run at the same time, due to volumes, SLAs, etc.

Every now and then there are a few locking issues, Not many considering the volumes. It is just a bit of a nuisance as we need to reprocess these records later.

0 6
0 155

Hello,

I need to create a query that return the amount of heath related information from a patient.

I created one using the Analitics but, once our Analitcs database is update once a day this information is not reliable during the current query day.

Part of my Analitcs query is shown below. Where can I find the equivalent tables in Health Share? Any help is appreciate.

Tanks In Advance.

--Analitcs Query

1 6
0 400

A very simple SQL

SELECT field1 F1, count(field2) CntF2
FROM GCT.PA_Data
where field1 is not null
group by field1

gives an error back:

[SQLCODE: <-461>:<Communication link failure>]

[%msg: <Connection 'TRAKCARELIVE': Execute: SQLState: (HY000) NativeError: [10053] Message: [Cache ODBC][State : HY000][Native Code 10053] [c:\intersystems\ensemble\bin\cache.exe] Software caused connection abort> ]

0 6
0 468
Question
· May 7, 2019
Linked Tables and Dialects

Hi! I've been fiddling with linked tables to get data from other servers, and I encountered a problem that I'm curious about. Maybe I'm not using these tools as intended or there's more going on, so I'm asking here.

I'm running a query on linked table A, something simple like this:

select name from A where id = 5983658923646

And I get this error:

[SQLCODE: <-400>:<Fatal error occurred>]

[%msg: <>]

0 6
0 504

I know that you can use Do $SYSTEM.SQL.Schema.ImportDDL() to insert sql files into IRIS however I was wondering if there is a way that I can upload .sqlite files into iris? I have about 20 .sqlite files that I need to get into my database. I tried using the ImportDDL method but it said "SKIPPING non-SQL SOURCE:"

0 6
0 68

Let's imagine you have only SQL. You need some table where you should increment value in some property when you update this line.

We have the table

CREATE TABLE "test" (
  "identifier"   VARCHAR(200) NOT NULL,
  "value"        INTEGER,
  PRIMARY KEY ("identifier")
)

1 6
0 652
Question
· Mar 12, 2019
String to date

I am trying to convert a string to date but can not get it to work I have function that I would like to take in a date string and covert it to date object

here is the ezample so far can not get it to work any help appreciated 

set p="12/03/2019"
 
w $System.SQL.TODATE(p,"YYYY-MM-DD")
 
<ILLEGAL VALUE>todate+32^%qarfunc

if I try this still get the wrong value returned

set p="12/03/2019" 
w $ZDATE(p,3)
1841-01-12
0 6
0 2.9K
Question
· Mar 23, 2023
Saving data into SQL table

Hi Community,

I am trying to save data into SQL table but each entry getting saved twice in the SQL table. Is there any reason data is saving Twice in the SQL table

I have created a %Persistent class for the fields

Please find the business operation below

Class Patient.DBOperation Extends Ens.BusinessOperation

{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

0 6
0 248

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?

0 6
0 1.1K

This applies to embedded and dynamic SQL queries in Caché ObjectScript. If I attempt to terminate the process via Management Portal, nothing happens. Neither does Ctrl C nor closing the terminal window in which my program is running, nor setting a stop flag in a global which is read by the program on each loop iteration. The only way to stop the query appears to be restarting the Caché server (which is running locally on my PC).

0 6
0 340

Hello,

searching messages in our Message Bank is quite slow, often runs into timeout.

I wanted to perform a tune table on Ens_Enterprise_MsgBank.MessageHeader because this apparently has not been done yet - the Tune Table utility shows no entries for selectivity, etc.

I tried

 w $SYSTEM.SQL.Stats.Table.GatherTableStats("""Ens_Enterprise_MsgBank"".MessageHeader")

and got this error message

1 6
1 424

Hi Everyone,

I am trying to built a SSIS package thru BIDS Visual Studio 2013. My Datasource is a InterSystems Cache Database, I wanted to Import Tables records from the Datasource to MS SQL Server 2014.

As a Sanity check. I only created one Package to import one Table to MSSQL Server to try out. The connection to the InterSystems Cache Database was successful. The DSN for the InterSystems Cache Database is created in the System for 32 and 64 Bit.

0 6
0 1.9K

Hello all,

I have a Recordset object which contains data from a table "XYZ".

Currently i use this object to extract data using %Get(COL1,COL2...) in a loop and than pass it to a function which inserts the data into another dynamically created Table "ABC" for each record. This takes a lot of time when 100's of records.

Is there a way i can directly copy a RecordSet to a dynamic table without looping through..?

Something like copy Recordset (COL1,COL2..)--> "ABC"

Thanks,

Jimmy

0 6
0 771