New post
AllAccepted answers
Brendan Bannon · Jul 11, 2022

Craig

If you are defining a Parent-Child Relationship with Default Storage the global structure of the child with change from ^child(id) to ^child(parentid,id) so you will not see any of the old data, no hint will fix that issue, sorry.

If your current child data is valid and you want to keep it I would suggest defining a Foreign Key constraint with Cascade on Delete.  This will keep the global structure the same but let you enforce your relationship.

Brendan Bannon · Nov 28, 2020

So there is a zip file attached to this article that contains a bunch of examples.  the Class Mapping.ChildPiece should be close to what you need.  the global for that class Parent Child looks like this:

/// ^Par(1)=Parent1\Child11#child12#child13
///      2)=Parent2\Child21#child22
///      3)=Parent3
/// 

and the mapping for the child is:

<SQLMap name="Map1">
<Data name="ChildData">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^Par</Global>
<RowReference>$P($P(^Par({L1}),"\",2),"#",{L2})</RowReference>
<Subscript name="1">
<Expression>{Mapping.ParentPiece.Sub1}</Expression>
</Subscript>
<Subscript name="2">
<AccessType>Piece</AccessType>
<Delimiter>"#"</Delimiter>
<Expression>{PieceCounter}</Expression>
<Invalidcondition name="1">
<Expression>$P(^Par({L1}),"\",2)=""</Expression>
</Invalidcondition>
</Subscript>
<Type>data</Type>
</SQLMap>

If you can get your working let me know and I can make an example for your global.

Brendan

Brendan Bannon · Nov 25, 2020

Hi Limyandi

I am not a fan of creating List of Objects as it make it hard to query the data.  I would rather expose this data as a child class somehow so SQL can be used to look at the data.  I would look at doing something along the lines of what is in Mapping.ChildPiece.

But you ask for an ugly List of Lists so that is what I did. 

The Serial class is just a normal one:

Class Mapping.Address2 Extends %SerialObject [ ClassType = serial, ProcedureBlock ]
{

Property RoadName As %String(TRUNCATE = 1);

Property SuburbanName As %String(TRUNCATE = 1);

Property StreetNumber As %String(TRUNCATE = 1);

Storage Default
{
<Data name="Address2State">
<Value name="1">
<Value>RoadName</Value>
</Value>
<Value name="2">
<Value>SuburbanName</Value>
</Value>
<Value name="3">
<Value>StreetNumber</Value>
</Value>
</Data>
<State>Address2State</State>
<StreamLocation>^Mapping.Address2S</StreamLocation>
<Type>%Library.CacheSerialState</Type>
}

}

Then for the Person class I needed to write Retrieval code that would get the different address out of the global and put them into the Nested $LIST() that the class is expecting.

Class Mapping.Person2 Extends %Persistent [ StorageStrategy = NewStorage1 ]
{ Property Name As %String;

Property Addresses As list Of Mapping.Address2;

Property Sub1 As %Integer;

Index Master On Sub1 [ IdKey ]; Storage NewStorage1
{
<ExtentSize>100</ExtentSize>
<SQLMap name="Map1">
<Data name="Addresses">
<RetrievalCode>
      set string=$P(^Person({L1}),"|",2)
      for i=1:1:$L(string,"^") {
           set address=$P(string,"^",i)
           set $LIST({*},i)=$LISTFROMSTRING(address,"~")
      }
 </RetrievalCode>
</Data>
<Data name="Name">
<Delimiter>"|"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^Person</Global>
<Structure>delimited</Structure>
<Subscript name="1">
<Expression>{sub1}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mapping.SimpleS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}

Brendan Bannon · Jun 23, 2020

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.

Brendan Bannon · Jun 23, 2020

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.

Brendan Bannon · Jun 10, 2020

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.

Brendan Bannon · May 8, 2020

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">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^*</Global>
<Subscript name="1">
<AccessType>Global</AccessType>
<Expression>{GlobalName}</Expression>
<NextCode> i {L1}="" s {L1}=##class(Mapping.SuperClass).#GloParameter QUIT
 s {L1}=""
</NextCode>
</Subscript>
<Subscript name="2">
<AccessType>Sub</AccessType>
<Expression>{Sub1}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mapping.SuperClassS</StreamLocation>
<Type>%Storage.SQL</Type>
} }

Brendan Bannon · May 7, 2020

Arto

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:

<Global>^*</Global>
<Subscript name="1">
<AccessType>Global</AccessType>
<Expression>{GloType}</Expression>
<NextCode> i {L1}="" s {L1}="Member" QUIT
 i {L1}="Member" s {L1}="Provider" QUIT
 s {L1}=""
 </NextCode>
</Subscript>

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

Brendan Bannon · Mar 4, 2020

Not sure if you contacted the WRC or not, but if not here is what I would try.

1)  Can you use a different ODBC tool?  Our fav in support is WinSQL.  If this works then we know we are dealing with an ADO issue, if it fails then most likely we have an ODBC bug.

2)  Turn on the ODBC client logging. If you are using the ODBC Data Source Manager then there is a checkbox ODBC logging.  You will need to stop and start your ADO client to pick up the flag, reproduce the problem and then look for the irisODBC.log file in C:\Users\Public\Logs

Brendan Bannon · Jan 29, 2020

Jeffery

If you don't use that link and first log into the WRC application:  https://wrc.intersystems.com/wrc/enduserhome.csp

Can you then go to:  https://wrc.intersystems.com/wrc/coDistribution2.csp

Then select Preview?  Some customers have had problems with the distib pages because their site restricts access to some JS code we get from a third party.

Brendan Bannon · Jan 17, 2020

Neerav

When the optimizer is looking at a query it does not look at the parameter values, in fact, they are removed before the query gets to the optimizer, so it sees:  WHERE Tad1.Id  > ?  Because the value is removed the optimizer can only guess at how restrictive the condition is, and it is an optimist so it assumes it will be restrictive.   That is why the Relative Cost changed so much.  The optimizer thinks this query will return only a small number of rows.

Range conditions that are not restrictive can cause performance problems if there is an index on the property involved.  Since the optimizer thinks the condition is restrictive it might choose to use the index and end up doing a table scan.  If you have a range condition that you know is NOT restrictive you might want to put the %NOINDEX hint in front of the condition so the optimizer knows not to use it.

WHERE %NOINDEX Tad1.Id > ?

Brendan Bannon · Nov 13, 2019

Hello

I was able to get this to work.  I think the only chagne I made was to add the SQLName to the query

Here is my class with your code

Class DC.ProcClass [ Abstract ]
{ Query ClosedFutures(FromDate As %Date, ToDate As %Date) As %Query(ROWSPEC = "FundCode:%String, Asset:%String, TradeDate:%Date,BuySell:%String, Price:%Numeric, Quantity:%Numeric, CumulativePosition:%Numeric, PreviousCumulativePosition:%Numeric, ClosingTrades:%Integer, ClosingTradesInPeriod:%Integer, ClosedTradeValueInPeriod:%Numeric, NetQuantityInPeriod:%Integer, WeightedClosedAverageTradePriceInPeriod:%Numeric, InitialTradeDate:%Date, InitialTrade:%Integer, LastInPeriod:%Integer") [ SqlName = ClosedFuturesProc, SqlProc ]
{
} ClassMethod ClosedFuturesExecute(ByRef qHandle As %Binary, FromDate As %Date, ToDate As %Date) As %Status
{
// Contains complex implementation including setting ^||justClosedTrades
} ClassMethod ClosedFuturesClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
kill ^||justClosedTrades
Quit $$$OK
} ClassMethod ClosedFuturesFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
set nextid=$order(^||justClosedTrades(qHandle),1,rec)
if $length(nextid) { set Row=$listbuild($piece(rec,"~",1),$piece(rec,"~",2),$piece(rec,"~",3),$piece(rec,"~",4),$piece(rec,"~",5),$piece(rec,"~",6),$piece(rec,"~",7),$piece(rec,"~",8),$piece(rec,"~",9),$piece(rec,"~",10),$piece(rec,"~",11),$piece(rec,"~",12),$piece(rec,"~",13),$piece(rec,"~",14), $piece(rec,"~",15), $piece(rec,"~",16)),qHandle=nextid }
else { set AtEnd=1,Row="" } Quit $$$OK
} }

and here is the view using my schema and SQLName

Class DC.NewClass1 [ ClassType = view, ViewQuery = {
SELECT * FROM DC.ClosedFuturesProc(65257,65286) --'2019-09-01','2019-09-30'
} ]
{ Parameter READONLY = 1; }