This looks like it is just a bug.  I reproduced this in InterSystems IRIS latest.  Looking at the generated code we are able to resolve the the index global in other places, but not in this one case.

I will report the bug to development to get it fixed.

My question is what do you plan to use this index for?

Couple of this this could be.

The storage def is using SQL field names, so {SQLUser.Parent.IdAProp} might fix the error.

Do you have an IDKey index defined in User.Parent on both IdAProp and IdBProp?

OK so I would try:  SELECT PROE,ID... and SELECT DESP,ID... to figure out which rows is causing the problem.

I would then look at how that %String is defined and then look at the data returned in the portal or shell to make sure the data matches how the property is defined. 

I have seen problems with some client side tools where they want to treat something we think is a simple VARCHAR as a CLOB.  If your %String has a large MAXLEN size then maybe this is what is going wrong?  To get to the bottom of that we would need to get an CacheODBC.log file ( or IRISODBC.log) and dig through the HEX dump.

What are the datatypes for the Properties in the SELECT list?  This error is coming from VB and most likely is caused by the data not matching up with the datatype def. of the Property.  Doing a quick google search for this error the first three hits were related to Streams.

When you run a query in the portal we do not do any type validation so if you have a MAXLEN on a %String set we are not going to check that, we assume it passed on data input.  External tools might be typed and can fall over if a string is longer than we said it was.

Limiting the fields in the SELECT list to figure out what data type is causing the problem is step 1.  Step 2 would be limiting the rows to figure out which one is producing the error and then looking at that data to see why it might be causing the problem.

If you load a new version of your classes, or compile your classes on the system that will also purge Cached Queries so you would want to run the method then as well.

If you want to be sure your customer will get the same generated code you tested in house you could freeze all the plans after running GenerateEmbedded():  Do $SYSTEM.SQL.FreezePlans(1,1,,.Errors)

To generate the Cached queries for your testing and after installing your product on a customer system you can call:  d $SYSTEM.OBJ.CompilePackage("%sqlcq")

These 3 steps should get you back the behaviors you liked about Embedded SQL.

So the issue with Inheritance and Cache SQL Storage is you can't modify the storage to add new properties in the sub class.  For you that does not matter, you just want to change the global name.  So if there was some way we could write the code so that it would generate a different global name for the different classes you would be all set.  I tried using $parameter($classname,"MyParameter") but could not get it to work.  One issue, I think, is when we are compiling this code we are in a Cached Query class, not the class I want to be referencing.  

The best I could do is hard code it to the current class.  So if you have 2 classes with the info below the only change you need to make is to change the parameter value from A to B.

Class Mapping.SuperClass Extends %Persistent [ Language = objectscript, StorageStrategy = NewStorage1 ]
{ Parameter GloParameter = {"A"}; Property GlobalName As %String; Property Sub1 As %Integer; Property Data1 As %String; Index Master On (GlobalName, Sub1) [ IdKey ]; Storage NewStorage1
<SQLMap name="Map1">
<Data name="Data1">
<Subscript name="1">
<NextCode> i {L1}="" s {L1}=##class(Mapping.SuperClass).#GloParameter QUIT
 s {L1}=""
<Subscript name="2">
} }

I guess Should have read the last post.

Cache SQL Storage does not work with inheritance.

If you use the example I have and instead of having the first subscript level loop over different values you could set the value there.  You could try using it with a class parameter, I have not tried that but it should work.


You can have one class look at multiple globals by defining the first subscript level to be of type Global and the global name for the map would be *

I have an example of this in the Mapping zip file that is part of the SQL storage series I did a couple of years back.  Mapping.TwoGlobals.cls

In that class, I have 2 globals:  Member and Provider.

/// ^Member(1,"08/24/2005 11:39",4)=One Memorial Drive
///                              5)=3rd Floor
///                              7)=Cambridge
///                              8)=MA
///                              9)=02142
/// Array: ^Provider
/// ^Provider(1,"08/24/2005 11:43",10)=
///                                11)=15 Cliff Dr
///                                12)=
///                                13)=
///                                14)=Plainville
///                                15)=MA
///                                16)=02762

Theglobal and first subscript looks like:

<Subscript name="1">
<NextCode> i {L1}="" s {L1}="Member" QUIT
 i {L1}="Member" s {L1}="Provider" QUIT
 s {L1}=""

The class has an extra twist in that the two globals are not exactly the same so the fourth subscript level needs to deal with the data being in different places.

you can get all my mapping examples from here

Well, that is strange.   I don't know of any reason ODBC would behave differently for those kits.