#SQL

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?

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?

I am trying to replace one of our SQL Integration Service jobs with Ensemble and I am running into an issue executing a query against a MS SQL database using JDBC drivers.

My SQL Outbound code looks like this.

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

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.

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.

Problem to Solve: When an HL7 message hits a particular Process, I need to use the placer order number therein to query the messages that came in over the last 24 hours on a particular Service and grab discrete pieces of data that came in the latest message that has that placer order number.

The code I'm (currently) using looks like this:

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:

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?

Most of the databases with SQL access offers the DATE_TRUNC function or analogs. This helps to easily truncate a date by any part while keeping the DateTime in a valid format

I need to use a context variable in my code block for the table to query. When I use :content.table, it does not work. When using the literal, it does. How do I get the :context to work?

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:

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

Hi

We have multiple edge production namespaces and want to run query to get errors/alerts from Ens_Util.Log table across namespaces. is it possible?