Hi everyone.

I have a function that may end up being called from a number of transformations at the same time, and within the function there's some Embedded SQL to first check if a local table has an entry, and then adds the entry if it doesn't exist.

To prevent a race condition where the function is called by two transformations and they both end up attempting to insert the same value, I'm looking to use the table hint "WITH TABLOCK" on the insert, but this seems to be failing the syntax checks within vscode.

Are table hints supported with embedded sql?

0 4
0 147

Running TuneTable accounts among other the parameter named Block Count. In documentation, we see that this is an approximate numbers of 2K-blocks in which SQL-maps are stored. Databases in recent Cache doesn't support 2K-physical blocks so SQL-blocks are not physical blocks as it seems. So two questions:

- what are these blocks?

- how knowledge about count of blocks can help in SQL optimization?

0 4
0 553
Question
· Jun 8, 2017
SQL Connect

How to simply going to get the value of the system language from ^%z? Because I got a problem, U2 context isn't setup while connecting the SQL Connect.

0 4
0 410
Question
· Mar 20, 2019
Query Response from MSSQL

I am having an intermittent issue that when I make a call to MSSQL from a BPL that the response does not come back in the amount of time required. Since the call from the BPL is synchronous I tried changing the timeout to 60 but it has not helped (see below). Is there anyway to guarantee that the call waits long enough for a response before continuing on?

Thanks

0 4
0 426

I am trying to populate a table using the sql Data Import Wizard. The input file is a tab delimited text file. But the import keeps failing with a 104 error showing validation for the columns which use %Library.TimeStamp and %Boolean datatypes is failing. Yet when I insert values into the table through a SQL insert command, the values get saved correctly in the table.

For the TimeStamp format in the wizard form, I am choosing YYYY-MM-DD-HH:MI:SS because there was no option for this format: YYYY-MM-DD HH:MM:SS.

0 4
0 387
Article
· Sep 11, 2024 9m read
Dates with InterSystems

Do not let the title of this article confuse you; we are not planning to take the InterSystems staff out to a fine Italian restaurant. Instead, this article will cover the principles of working with date and time data types in IRIS. When we use these data types, we should be aware of three different conversion issues:

  1. Converting between internal and ODBC formats.
  2. Converting between local time, UTC, and Posix time.
  3. Converting to and from various date display formats.

14 4
5 534

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.

0 4
1 1.7K

Our team is reworking an application to use REST services that use the same database as our current ZEN application. One of the new REST endpoints uses a query that ran very slowly when first implemented. After some analysis, we found that an index on one of the fields in the table greatly improved performance (a query that took 35 seconds was now taking a fraction of a second).

3 4
0 502

Hello All,

what is the best method for search a portion of text in a non-indexed global?

I need to implement an autocomplete kind of search, in a global of >1M registers (text type, not $lb)

Maybe the best way would be use a SQL mapped class, with 'Bitmap' indexes?

Thanks in advance!

0 4
0 511

A quick question regarding to SQL Query Plan:
Why these two requests have different plans, in particularly, why second request needs temp file? To me, temp file is a bad thing which should be avoided, right?

  1. select * from Test.Log where cdate = 1
  2. select * from Test.Log where cdate > 1

Plan for 1:

Read index map Test.Log.cdateIndex, using the given %SQLUPPER(cdate), and looping on ID.
For each row:
Read master map Test.Log.IDKEY, using the given idkey value.
Output the row.

Plan for 2:

0 4
0 270

I noticed when creating Record Map's within Ensemble that it is creating a Persistent cache table as it translates the file and puts it into the Record Map data structure.

I was wondering if there was a way to add a column to the cache table that is Hidden from the record map, but is a calculated date value on when that record was inserted?

0 4
0 264

Background: We have our own SQL map that predates InterSystems'. A program writes an XML file for each table map class as $system.OBJ.Export would. $system.OBJ.LoadDir loads the XML files into .cls files.

The reason is a long story, but we need to update parameter EXTENTSIZE (only) in existing classes. This does not seem to happen. As a test I used $system.OBJ.Export to make an XML file and edited EXTENTSIZE in the two places it appears in the XML:

0 4
0 600

Earlier this year I announced availability of a VS Code extension for coding in ObjectScript, Embedded Python or SQL using the notebook paradigm popularized by Jupyter. Today I published a maintenance release to correct a "getting started" problem.

Here's a video of the installation steps from the extension's README:

7 4
0 408

In writing some code on:

Cache for Windows (x86-64) 2015.1 (Build 429U) Fri Feb 13 2015 14:37:23 EST

I noticed an unexpected "ROLLFAIL" error when a Unique Index fail was generated in a %Save() call.

The object SHOULD fail to save, since the Unique criteria is not met, but I didn't expect to see a ROLLFAIL error as the "Last Error".

When running the following class code:

0 4
0 1.1K
Question
· Mar 22, 2017
Query

Hi All, In Cache Table i have stored the data value as horlog format ,by query how to retrive the data when i give the data field as date format.

0 4
0 549
Question
· Jul 20, 2018
Oracle Transaction

Hi,

I have a question related with EnsLib.SQL.OutboundAdapter adapter.

Is there a way to open an oracle transaction and do either commit or rollback depending on the query result?

Is posible to keep open an oracle transaction to execute a lot of queries on different methods on the same business operation and do a commit when the business process finished?

Thanks and Best Regards

0 4
0 371
Question
· Aug 21, 2020
IRIS SQL Linked Table

Hi everyone

Anyone knows how can we add a column to an existing linked table in Cache to reflect the external SQL table?

I've tried different queries. It does not fail but does not add the column either...

0 4
0 548
Question
· Apr 25, 2022
Run multiple Update sql queries

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")

}

0 4
0 1.7K