All
Accepted answers
Robert Cemper · Apr 25, 2018 go to post

Salva, that's Great!

without having to change the class definition

I like this.  yesyes

Robert

Robert Cemper · Apr 25, 2018 go to post

The the description is somewhat vague:

Exclusive LOCK happens on process basis not by user !
It's not clear where you run "export from the production". Portal,Terminal, Studio, ...

if you are in Studio and run "export from the production" in MgmtPortal or Terminal then you have 2 processes.
1 has the lock the other can't get it.
 

take a look in MgmtPortal >  System Operation > Locks  > View Locks

Now you should see who is owning your lock 

Robert Cemper · Apr 24, 2018 go to post

BTW. Class %ZEN.Component.querySource  still does it as iit did in past.

For more tricky manipulation you have

/// (optional) Name of Server-side callback method to call to create
/// a <class>%ResultSet</class> object.<br>
/// This must be the name of a server-only method in the page
/// class that contains this component.
Property OnCreateResultSet As %ZEN.Datatype.delegator(FORMALSPEC = "*tSC:%Status,pInfo:%ZEN.Auxiliary.QueryInfo", RETURNTYPE = "%ResultSet");
/// (optional) Name of Server-side callback method to call to execute the
/// <class>%ResultSet</class> object.<br>
/// This must be the name of a server-only method in the page
/// class that contains this table pane.
Property OnExecuteResultSet As %ZEN.Datatype.delegator(FORMALSPEC = "pRS:%Library.ResultSet,*tSC:%Status,pInfo:%ZEN.Auxiliary.QueryInfo", RETURNTYPE = "%Boolean");
 

Robert Cemper · Apr 24, 2018 go to post

yeah, table navigators chop the original resultset.

but with the absolute rowID (%vid) and the pageSize (pS)  you should get pageNum= %vid\pS+1
and relative rowID= %vid#pS

Or directly in SQL  with pageSize = 12

SELECT *, %vid AbsRowID,
                    CEILING(%vid/12) PageNum,
                   {fn MOD(%vid,12)} RelRowId from (
      SELECT name,home_city TOWN FROM Sample.Person
) WHERE TOWN %STARTSWITH 'DENVER'

you will keep the * away as you don't need the content

It's all based on the fact that the 'base'query always generates the same sequence of rows 
Just dynamic filters may break this. 
For paging also tablePane has to use the same mechanics. 
 

Robert Cemper · Apr 24, 2018 go to post

Hi Chip,

If I understand it correct you have a value and look for the related rowID in your tablePane.

tablePanes get typically feeded by a well know SQL SELECT and it numbers its rows sequentially.

For ease of explanation,  I use  SELECT name,home_city TOWN FROM Sample.Person  to fill your tablePane

Let's look for TOWN = 'Denver'

SELECT *, %vid rowID from ( 
      SELECT name,home_city TOWN FROM Sample.Person
) WHERE TOWN %STARTSWITH 'DENVER'

Of course, there is still arithmetic required if you have several sections to calculate.
This just gives you the 'absolute' rowID, not the relative.
HTH, Robert

Robert Cemper · Apr 24, 2018 go to post

the DSN is used when you set up your SQLgateway connection.

MgmtPortal > System > Configuration > SQL Gateway Connections

 There all information  on the DSN is stored.

Next if you Link the external table this gateway entry is used and stored for access.

MgmtPortal > System Explorer > SQL > Wizards > LinkTable

Now you bind a Class in a Namespace to a Gateway to a DSN


  

So you are fixed for THIS namespace. This is static.
If you change the Gateway entry or the DSN in some essential way
it could happen that you have to do this again.

Similar for a different target table you have to do the Link again.

A different issue could be that you might just have read access to the foreign table.
Then I'd expect some kind of error message.
At least it is something the partner side has to manage.

Robert Cemper · Apr 24, 2018 go to post

so either you or someone else just has it opened in a different Studio / Session.

Robert Cemper · Apr 24, 2018 go to post

doc for ORA-01861
describes the TO_Date issue very detailed

https://www.techonthenet.com/oracle/errors/ora01861.php

Your problem seems to be strictly related that the value you use and its description in TO_DATE don't match.

So TO_DATE(?,'YYYYMMDD') in Prepare
should work with 
    s result=obj.%Execute(20180425,par2,par3,par4)

It's a little bit less transformations

Robert Cemper · Apr 24, 2018 go to post

Through terminal couldn't make it work no mater what i tried, not when it is %Date and using $h without to_date nor when it is %String and translating the date to yyyy-mm-dd with or without to_date (at prepare or at execute).

Did you also try SQL shell from terminal ?  (it should act like SMP)
SAMPLES>Do $system.SQL.Shell()
SAMPLES>>INSERT INTO .......

Also: have you ever checked status  ?   to see if  %Prepare was OK ?

after:

s status=obj.%Prepare(statement)

do $system.OBJ.DisplayError(status)
It's hidden in your example

Robert Cemper · Apr 23, 2018 go to post

ok

did you try '2018-04-23' instead of $h ?

SELECT top 3 EFFECTIVE_START_DATE PACKAGE.SOME_TABLE_NAME

may show the expected default.

Robert Cemper · Apr 23, 2018 go to post

The change from %Date to %String influences image of your target table

and may affect implicit ODBC Data transmission that converts eg. +$h to YYY-MM-DD format !
once your data is a %Date again this should do it:

...... VALUES (?,?,?,?)

%Execute($zdh(somedateYYYYMMDD,8),par2,par3,par4)
If you test in SMP you have to check if you are in ODBC, Logical or Display mode.

with TO_DATE() you just overrule this as string are the same in any mode.
For ODBC the date is always 2018-04-25

For Display it's 04/23/2018 or 23.04.2018 depending on your local setting

For Logial it is always 64761 (+$h)

Robert Cemper · Apr 23, 2018 go to post

OK.
you got a reply of 149 characters!

And what was the result of ZZDUMP tText?

It should look similar to  this:

ZZDUMP tText
  
0000: 3F C1 0A C2 30 20 0A 3F 5F 65 E4 2C 63 D2 0A 9D         ?Á.Â0 .?_eä,cÒ..
0010: 27 3D 0A 2A 5E 3C 59 D9 46 A1 ED 24 ED BC C8 DE         '=.*^<YÙF¡í$í¼ÈÞ
0020: DD 74 20 0A 6F C9 F7 FD 24 3F 7C 0A A4 31 A1 7B         Ýt .oÉ÷ý$?|.¤1¡{
0030: 44 E2 68 E0 B0 DD 3F D6 F9 29 32 F3 73 3F 3F 52         Dâhà°Ý?Öù)2ós??R
0040: 3F B8 BC 3F BA 64 3F 24 2C F1 44 2B BB 3F 3F 0A         ?¸¼?ºd?$,ñD+»??.
0050: 3F 20 0A D4 BA 51 5A E9 FD 4E D5 20 56 7B 43 3F         ? .ÔºQZéýNÕ V{C?
0060: F3 F2 3F 62 A2 3F E9 CD 20 29 24 20 0A B2 35 C5         óò?b¢?éÍ )$ .²5Å
0070: 3F 77 45 EB 3F 20 3F 3F A9 74 D6 E3 31 7A D7 32         ?wEë? ??©tÖã1z×2
0080: 46 EB CA 6E F4 65 CB 20 E6 3F 3F 5D 5A DF 71 20         FëÊnôeË æ??]Zßq
0090: DC BA 3F E1 0A                                          Üº?á.

I filled in the marked characters to get to your received size. The real value could help as there is a bunch of controls that could trigger a newline.

The thing looks somehow encoded / encrypted or just binary.
Do you have information on source system ?   (the url is definitely faked)

  

Robert Cemper · Apr 22, 2018 go to post

Classes and Methods forever!

#1) for documentation
#2) for all the possibilities and structural controls of  OO development.
 

#3)
.mac & .int is a left over from a previous millennium,
a (failing) attempt to mimic OO with the mindset of procedural methodology.

I'm personally disappointed that Atelier still supports mac.

It was a historical requirement. Accepted. For last millennium. Eventually still for some internals.
Definitely not for public use.

Robert Cemper · Apr 20, 2018 go to post

you didn't get me. The browsers make something unreadable and useless out of it.
That's just rubbish

 ?Á
Â0 
?_eä,cÒ
'=
*^<YÙF¡í$í¼ÈÞÝt 
oÉ÷ý$?|
¤1¡{Dâhà°Ý?Öù)2ós??R?¸¼?ºd?$,ñD+»??
? 
ÔºQZéýNÕ V{C?óò?b¢?éÍ )$ 
²5Å?wEë? ??©tÖã1z×2FëÊnôeË æ??]Zßq ܺ?á

pls do:

SET tText = tHttpResponse.Data.Read()
ZZDUMP tText
WRITE tHttpResponse.Data.Size

So we see the real HEX_content , eventual control characters, ...  and the received number of characters
Similar to your request the response is encrypted. Eventually,  it can be decoded. 

Robert Cemper · Apr 20, 2018 go to post

where does Answer in Ensemble: come from ??

pls add:
write tHttpResponse.Data.Size,!

ZZDUMP tText

Robert Cemper · Apr 20, 2018 go to post

I had that feeling that it might be more.
And took a look to  ##class(%ZEN.Auxiliary.jsonProvider).%WriteJSONStreamFromObject() as it is visible.

It's nothing I would like nor to touch nor ever to maintain over future releases in parallel to ISC.   

Robert Cemper · Apr 20, 2018 go to post

Following your code example, this might be the quickest workaround

 TEST>set x = ##class(test.msg.struct.TestXML).%New()
 TEST>set x.statusId = "111"
 TEST>set x.service = "222"
 TEST>do ##class(Ens.Util.JSON).ObjectToJSONStream(x, .obj1, "aelotu")
 TEST>write $REPLACE(obj1.Read(),"statusId","status_Id")
{       "status_Id":"111",       "service":"222"}

Not to pretty but useful.
 ​

Robert Cemper · Apr 19, 2018 go to post

very short:

.NET binding in Caché projects Caché Object classes to .NET with properties and methods
and services them with a private protocol. It is dependent on synchronized objects at both ends.

you may know this picture:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GBMP_intro

A RESTful API does something total different.

You define request (for data or actions)  and sent the request to Caché.
And you get it back preferred in JSON style expressed.
Your requests are straight HTTP with someWebServer in between on your way to Caché.

SOAP does something similar. But it has an incredible overhead of XML.
And as with .NET binding you need predefined data structures to be serviced.
In extrem:  With REST you just get back a string / stream and it is up to you to decide what this means:
No predefinded structures. It is all your task to organize yourself.
For .NET end I found this link:
https://docs.microsoft.com/en-us/aspnet/web-api/overview/advanced/calling-a-web-api-from-a-net-client
 

For Caché side there are lots of useful articles in the forum already and examples in SAMPLES.

Simple summary:

For RESTapi you will not need 
InterSystems.Data.CacheClient.dll nor generated proxy classes using the Caché Object Binding Wizard for .NET
You are the owner of the exchanged data structures.
 

Robert Cemper · Apr 18, 2018 go to post

Are looking for this?

SELECT JSON_OBJECT('ID':ID,'Column1':COLUMN1,'Column2':COLUMN2,'DataID':DataID) FROM  SQLUser.SampleData

Cache for Windows (x86-64) 2017.2.1 (Build 801U)

Sorry I don't have your data to show your result

somewhat similar. 

SELECT top 3 JSON_OBJECT('name:':Name,'home':HOME_CITY,'dob':DOB) FROm Sample.Person

{"name:":"Smyth,Valery B.","home":"Hialeah","dob":"2001-01-06"}
{"name:":"Jones,Valery E.","home":"Islip","dob":"1959-11-11"}
{"name:":"Newton,David H.","home":"Youngstown","dob":"1969-12-01"}

Or all together:

SELECT '{"result":'||List(xx)||'}' FROM
(SELECT top 3 JSON_OBJECT('name:':Name,'home':HOME_CITY,'dob':DOB) xx
FROM Sample.Person)

{"result":
  {"name:":"Smyth,Valery B.","home":"Hialeah","dob":"01/06/2001"}
 ,{"name:":"Jones,Valery E.","home":"Islip","dob":"11/11/1959"}
 ,{"name:":"Newton,David H.","home":"Youngstown","dob":"12/01/1969"}
}
 

The difference in DOB is just display mode vs. ODBC mode 

Robert Cemper · Apr 17, 2018 go to post

Sorry you went to CLASSES   not to SQL !!! 
These are different worlds with different rules and syntax.

And while your classname is WINSURGEDMP.WINSURGERESULTFACT

I expect your TABLE to be named WINSURGE_DMP.WINSURGE_RESULT_FACT

It is just by accident if TABLEs and CLASSes have the same name !!!!!
Especially if you refer to an EXTERNAl TABLE in a different (non Caché) database

Robert Cemper · Apr 17, 2018 go to post

Recompile deletes all Cached Queries !
See Dmitry's comment.

and Mgmt Portal > System Explorer > SQL > Actions > Purge...

Robert Cemper · Apr 16, 2018 go to post

#1) check that there exists a valid class WINSURGE*...   

#2) in Mgmt Portal > System Explorer > SQL you should see as Table WINSURGE_DMP.WINSURGE_RESULT_FACT

#3) #SQLCompile Path=cancreg 
seems the real source of your problem as it sets a default package cancreg  wherever that may come from

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GCOS_macros#GCOS_macros_mpp_lbSQLCompile_Path

Eliminate it as your table is already full qualified  WINSURGE_DMP.WINSURGE_RESULT_FACT

In addition :  - VALUES :FIELD()
do you really write to ALL fields of the external table ??? 

Hint:

try to execute your SQL statement first from Mgmt Portal using dummy values before coding with strange macros directives

start with a simple statement to see if the connection works as expected:

SELECT COUNT(*) FROM WINSURGE_DMP.WINSURGE_RESULT_FACT

then try:

INSERT INTO WINSURGE_DMP.WINSURGE_RESULT_FACT VALUES (1,2,3,4,5,6)   -- whatever it needs

Robert Cemper · Apr 16, 2018 go to post

I like that !  yes

BUT: as in the original request I seem to miss the file.Read()  to fill Line somewhere in the loop.  wink

Robert Cemper · Apr 16, 2018 go to post

Enable long strings in System Mgmt Portal and get strings up to 3.4 MB

System > Configuration > Memory and Startup​

 ​ ​