New post
Question
· May 7, 2019

Linked Tables and Dialects

#Databases #ObjectScript #SQL #Caché

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: <>]

If id, or anything comparison, is something smaller like 4345, it works just fine. It's only when the where reaches a certain length, not just id, that it fails.

After switching Dialects from CACHE to MSSQL, it works! Does anyone know the reason for this? Is the linked table connection using something the cache dialect doesn't understand?

Thank you! smiley

Discussion (6)1
Log in or sign up to continue
David Crawford · May 8, 2019

Thanks for the insight, there must be some nuance with this table's integer fields. I linked another table and I can query an integer field with a large number, not just id, and they all have the same property definitions. This must be a problem specifically with the one table, although I wonder why id works fine on another linked table if it's projected through xDBC as integer.

0 0
Dan Pasco · May 10, 2019

System assigned id's are projected to SQL as an integer as previously mentioned. The name of the column projected to SQL from a system-assigned id is 'ID' by default but it isn't guaranteed to always be 'ID'. 

As for controlling how system-assigned ID's are defined - both name and type - I suggest using IDENTITY. I think your code will be happier. You can even name it 'ID' if you wish.

Class User.Person Extends (%Persistent, %Populate)
{

Property ID As %BigInt [ Identity ];

Property Name As %String;

}

And a sample run:

USER>set ^User.PersonD = 5983658923640                                                       

USER>d ##class(Person).Populate(50)                                                          

USER>do $system.SQL.Execute("select id,name from person where id = 5983658923646").%Display()

ID Name

5983658923646 Young,Imelda B.


1 Rows(s) Affected

The reason it works when you specify a different dialect is because the type mappings are different for different dialects.

0 0
Robert Cemper · May 8, 2019

If this is Caché standard ID it is projected to SQL as xDBC Type INTEGER
Caché SQL Reference - Data Types says:

INTEGER%Library.Integer (MAXVAL=2147483647, MINVAL=-2147483648)

so 5983658923646 is definitely out of range

xDBC Type :

BIGINT

%Library.BigInt

   represents a 64 bit integer value.

I'm not aware of any parameter to change the data type of the generated ID.
But this hack may do the trick. Just add this calculated Property to your class without affecting the storage.

Property myID As %BigInt [ Calculated, SqlComputeCode = { set {*}=%ID}, SqlComputed ];
 

4 0