All
Accepted answers
Jean Millette · Sep 15, 2021 go to post

Hello Rochdi,

Have you resolved this? If not, are you using a report that is derived from %ZEN.Report.reportPage? If you are, you can use the "GenerateReport()" instance method to create an "xlsx" Excel file, which can then be exported to ".csv" after opening it from Excel.

https://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls…

Alternatively, the following code highlights how to generate a CSV file containing the results of a query:

ClassMethod ExportCSV(/* some args */) As %String [ ZenMethod ]
{
/* get a results set named "rs" from a query (sql statement, or, in this example a query class):
Set rs=##class(%ResultSet).%New("<some query class")
Do rs.Prepare()
Do rs.Execute(/*some args*/)
*/
// Define the delimeter to be used in CSV file (usually comma)
Set Delim = ","

// Define the file and its name
Set File = ##class(%FileCharacterStream).%New()
Set Filename = "filename.csv"
Set File.BOM = $C(239,187,191)
Set File.Filename = "C:\temp\"_Filename 
Set File.TranslateTable = "UTF8" 
// Define the names of the columns (should match up with the values in each row of the result 
// set. In this example, there are 3 columns
Set FileHeader = $LB("Name","Rank","","SerialNumber")

Do File.WriteLine($ZCVT($LTS(FileHeader, Delim), "O", "UTF8")) 
While rs.%Next()
{
    Set Row = ""
    For i = 1:1:$LL(FileHeader) 
    {
        Set col = $LG(FileHeader, i) 
        Set Data = rs.%Get(col)
        // Replace characters that may mess up the CSV file (e.g., embedded commas in a data field)
        Set Data = $REPLACE(Data, $C(13), " ")
        Set Data = $REPLACE(Data, $C(10), " ")
        Set Data = $REPLACE(Data, """", "'")
        Set:(Data [ ",") Data = """"_Data_"""" 
        Set Data = $ZCVT(Data, "O", "UTF8") 
        // Add the datum to the row's list
        Set Row = Row_$LB(Data)
     } 
     // Each element in the "row list" is written, separated by "Delim" (comma) on one line 
     // in the file 
     Do File.WriteLine($LTS(Row, Delim))
} 
// Set attributes of the file for easier reading by the right apps later.
Do File.SetAttribute("ContentType","application/octet-stream; charset=utf-8")
Do File.SetAttribute("ContentDisposition","attachment; filename="""_Filename_"""")
Do File.SetAttribute("Expires",600)
    Do File.SetAttribute("Content-Length",File.Size)
        
    Do File.%Save()
 // Return the name of the full path to the CSV file
 Quit File.Filename
}

Please confirm if this is helpful or if you have other questions.

Thank you 

Jean Millette · May 10, 2021 go to post

The above solution is great if you’d like to keep the list in each record of one table. Depending on the nature of the data in your application, another option is to “normalize” the data a bit and create a separate table for the “numDossiersMER” values and link them back to the original “Titre” table as follows.

Convert the planned updated table from this:

User.TestList.Data.Titre

numTitre

millesime

codeProduit

numDossiersMER (list)

1

2021

X

1  2  3

2

2021

X

4  5  6

3

2021

X

4  2  3

4

2022

X

2  5  7  8

To the following 2 normalized tables

User.TestList.Data.TitreNew

Id (IRIS)

numTitre

millesime

codeProduit

1

1

2021

X

2

2

2021

X

3

3

2021

X

4

4

2022

X

User.TestList.Data.DossierMER

Id (IRIS)

titreID

numDossierMER

1

1

1

2

1

2

3

1

3

4

2

4

5

2

5

6

2

6

7

3

4

8

3

2

9

3

3

10

4

2

11

4

5

12

4

7

13

4

8

The “id (IRIS)” in each table is the “ROWID” assigned by IRIS as each entry is created in the table.

Using these two tables, the following “JOIN” query will get the results to be formatted as you like:

select numTitre, millesime, codeProduit, numDossierMER from User_TestList_Data.TitreNew t JOIN User_TestList_Data.DossierMER d on d.TitreId = t.id

Please note that this “normalized” solution is great if the “numDossierMER” values can be shared among various “Titre” records as shown in my made-up example above.

Sample code here:

Class User.TitreNew Extends (%Persistent, %Populate)
{ 
Property numTitre As %Integer; 
Property millesime As %Integer; 
Property codeProduit As %String; 
Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ]; 
}
Class User.DossierMER Extends (%Persistent, %Populate) 
{ 
Property titreID As %Integer; 
Property numDossierMER As %Integer; 
}

(Please excuse the formatting)

Jean Millette · Oct 7, 2020 go to post

Thank you Evgeny and Stefan, very helpful. A minor note: There is a typo at the end of the control panel url (cspv->csp).

Jean Millette · Oct 8, 2019 go to post

Hi Ting,

I haven't "Viewed Other Code" in Eclipse, but in Studio, Control-G will bring up the "Go To" dialog box.

You can click the "Tag or Label" option and type in zProcessRequest+8 into the text field and it should take you to the line in question. (If there is a similar feature in Eclipse)

Jean

Jean Millette · Sep 24, 2019 go to post

Hello Gunwant,

Can we assume that you've set up a Web Application named "/widgetsdirect/rest" via the Management Portal?

I've compiled your code and tested it and it works for me:

Best,

Jean

Jean Millette · Aug 23, 2019 go to post

Bonjour Michel,

Thank  you for your response. I'm not sure how one would delete query plans. Furthermore, I'm not sure how one would identify the query plans that used a new index.

35 seconds were indeed very important, especially for a REST service because the user needed to wait that long for the browser to respond.

The code that benefited from the new index looks at two tables of similar size, about 160K records. The method needs to look at every record in the first table to find records that match certain criteria. Then for each of those matching records, visit every record in the second table to find records that match other criteria (Order N-squared).

Adding the index to the 2nd table brought the number of checks down to "Order N".

After the change, the wait for a response at the user's browser is negligible.

Jean Millette · Aug 23, 2019 go to post

You're welcome Alex. I had no experience purging cached queries, so I ran a little experiment and confirmed that purging cached queries does NOT unfreeze query plans:

1. I froze all the plans:

do $system.SQL.FreezePlans(1,1)

2. I confirmed query plans were frozen using the Management Portal:

3. I then purged the Cached Queries for the specific table (crm.Company):

do $system.SQL.PurgeForTable("crm.Company")

4. And confirmed that the Cached SQL Queries were gone, again, using Management Portal:

5. Checking the frozen plans again post-purge, we see the query plans are still there, still frozen:

 

Hope this helps.

Jean Millette · Feb 12, 2018 go to post

Congratulations Robert! I'm always learning from you and looking forward to more opportunities to do so on the DC. All the best to you!

Jean Millette · Jan 31, 2018 go to post

Ah, ok thanks for the feedback Pravin. Does the new GUID need to be different than the GUIDs for the records being joined? If the new GUID is not to be persisted, could you pick the GUID from one of the records being joined?

Jean Millette · Jan 31, 2018 go to post

How about adding an SQL method to one of the classes being joined? The method would call %SYSTEM.Util's "CreateGUID" method and the field associated with that method would be included in the join.

Jean Millette · Dec 15, 2017 go to post

Hello Kenneth,

Have you figured out how to get the embedded HTML (the "<i>italics</i>") to appear in your second result? I have a feeling that this parser can't return embedded HTML like that because it handles text and tags separately (and only expects to return text).

Please update if you have gotten past this with this or another approach (maybe the approach suggested by John H. below?)

Much appreciated,

Jean

Jean Millette · Oct 31, 2017 go to post

You're welcome Pravin! I really like what you found i thought there might be a way to access the HTML element/attribute via javascript but couldn't figure it out. Your sample code will be very helpful to me, especially when debugging ZEN pages using browser developer tools. Thanks!

Jean Millette · Oct 30, 2017 go to post

Hi Pravin,

I'm 98% sure that a custom component that uses the %ZEN textarea as a superclass, adds a "maxlength" Property, and overwrites the %DrawHTML() method is the best (only?) way to go. Here's a code smippet that worked (but you likely already have such code :-)).

Class ZenApp.Component.TextArea Extends %ZEN.Component.textarea 
{
........

/// Maximum number of characters allowed within the textarea control.
Property maxlength As %ZEN.Datatype.integer(MINVAL = 0);
Method %DrawHTML()
{

.....

&html<<textarea id="#(..%MakeId("control"))#" class="#(..controlClass)#" #(..%Attr("title",..title))# #(..%Name())# #(..%Attr("cols",..cols))# #(..%Attr("maxlength",..maxlength))# #(..%Attr("rows",..rows))# #(disabled)# #(ro)# #(spellcheck)# #(tStyle)# #(..%Attr("tabindex",..tabIndex))# #(..%GetEventHandlers())#>#(tValue)#</textarea>>
}
 

 

Hope this helps.

Jean

Jean Millette · May 12, 2017 go to post

Hi Alex,

What happens if you call %BuildIndices for the "descriptionIndex" of Utility.contactTypes class after adding those 2 entries in DataSetup? I had an issue with a index "Find" call that went away after %BuildIndices on the index. Of course, I wouldn't expect that we'd need to build indices after each time we create a new object, so what I (and maybe we) found is a bug?

More investigation/diagnosis is needed for what I found; thought I'd bring it up in case it was related to what you found.

Thank for posting,

Jean

Jean Millette · Apr 28, 2017 go to post

My apologies, I realize that I answered the wrong question. 

When we created a system from a backup and wanted the new instance to have a different instanceGUID from the original system, we used the following command to give a copy of the system a new instanceGUID:


%SYS>s ^SYS("INSTANCEGUID")=##class(%SYSTEM.Util).CreateGUID()

Jean Millette · Apr 28, 2017 go to post

The system that I work on used to use InterSystems' Object Synchronization feature quite heavily to keep multiple servers around the world in sync. There was one main server with about 5 "satellite" servers. The InstanceGUID of the main server was used to support Object Synchronization. When we had to clone the main server, in order to maintain proper syncing with the "satellite" servers, we reset the cloned instance with the instance GUID of the original system.

To quote a colleague at ISC:

The instance guid is used for more than one purpose but
it is supposed to be unique to the instance. Its probably best
after cloning an instance to set this to a new value (assuming the
cloned instance isn't supposed to replace the old instance).

</quote>

Since our clone was replacing the old one, we used the steps below to set the instanceGUID of the new instance with the GUID of the old one (and retired the old system).

The original purpose of this document was to list the steps to create an exact copy of the original Hermes machine, including its role as the object-synchronization partner with other servers  around the world. In order to fill this role, some additional steps were needed and these are listed here:

  1. Get the unique “InstanceGUID” of the LEGACY instance of Caché by running the following command on LEGACY. Save the GUID that results from the command; it will be used later:
  • HERMES>w ##class(%SYS.System).InstanceGUID()
  • 34411A13-6CF9-4511-AC7D-49C2CEC6DB5B
  1. Assign this value to the new Hermes instance on NEW by issuing the following commands in the Caché Terminal on NEW:
    • s ns = “^^”_$zu(12)
    • s ^[ns]SYS(“INSTANCEGUID”)=”<Guid from Step 3 above>”
    • w ##class(%SYS.system).InstanceGUID()

Note that this is  something that should be done unless really necessary, but it is possible.

Jean Millette · Apr 20, 2017 go to post

Thank you Evgeny; I was looking for something that was built into Atelier, but this Cache Web Terminal looks like a tool I can use too.

Jean Millette · Apr 20, 2017 go to post

Hi Nicole,

This is perfect! Thank you. The step I was missing was finding/including the Caché JDBC driver jar files. I've got it up and running and all looks good.

Thanks again for the prompt reply.

Best,

Jean

Jean Millette · Apr 7, 2017 go to post

I selected the "Contact all update sites during install to find required software" checkbox from "Available Software" page, with "Atelier IDE" and "Atelier IDE HDPI Support" selected, and the "Remediation Page" no longer appears. Proceeding with the rest of the installation works well and I have Atelier in my instance of Eclipse!

Thanks all for your help (especially Jamie!).

 

Best,

Jean

Jean Millette · Apr 7, 2017 go to post

Thank you Jamie, your suggestion has helped me make progress in getting the Atelier plugin into my Eclipse Neon instance on WIndows 7 machine. After clicking "Select All" to get "Atelier IDE" and "Atelier IDE HDPI Support" from the "Uncategorized" tree of Available Software and clicking "Next",  I now see an "Install Remediation Page" with 3 options; any suggestions as to what is best?

I can't seem to insert a JPEG file or paste a clipboard image into this editor, but the 3 options from the Page are:

"Keep my installation the same and modify the items being installed to be compatible."

"Update my installation to be compatible with the items being installed"

"Show original error and build my own solution"

Thanks again.

Jean

Jean Millette · Apr 7, 2017 go to post

Hello Ronaldo and Atelier Community,

I am having a different issue installing Atelier as an Eclipse plugin and, instead of creating a new question, I am adding to this existing thread.

The main issue is the plugin installation does not report any errors or warnings, but I don't see the plugin listed on the "Installation Details" dialog in Eclipse nor do I see the "Atelier Project" as an option for new project.

I used the following page as a guide, but I wasn't quite sure of the steps for using the "auto-update framework":

https://download.intersystems.com/download/atelier.csp#plugin

Here are the steps I took to install the plugin; perhaps there is some user error.

1. I downloaded and installed Eclipse Neon:

Eclipse IDE for Java Developers

Version: Neon.3 Release (4.6.3)
Build id: 20170314-1500

2. Start Eclipse Neon.

3. Select Window->Preferences.

4. From the Preferences window, click Install/Update.

5. Click the “Add…” button on the right

6. in the subsequent "Add Site" dialog box:

Enter "Atelier" as the Name in the dialog box

Enter the following as the Location:

https://atelier.artifactoryonline.com/atelier/updates/stable/1.0/

7. Click OK.

8. Select Help->Check for Updates, which ran with no errors.

9. Select Help->Installation Details and look at the "Plug-ins" tab. I don't see any Atelier plugins not other plugins listing InterSystems as the provider.

Here are some lines from the Help->Installation Details->Configuration output, showing that some Atelier-relation software was recognized by Eclipse (I can dump the whole Configuration output if it's helpful):

/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/http\:__download.eclipse.org_technology_epp_packages_neon/type=org.eclipse.equinox.internal.p2.metadata.repository.LocalMetadataRepository
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/http\:__download.eclipse.org_technology_epp_packages_neon/uri=http\://download.eclipse.org/technology/epp/packages/neon/
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/http\:__download.eclipse.org_technology_epp_packages_neon/version=1.0.0
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/enabled=true
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/isSystem=false
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/name=repository
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/nickname=Atelier
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/suffix=content.xml
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/type=org.eclipse.equinox.internal.p2.metadata.repository.LocalMetadataRepository
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/uri=https\://atelier.artifactoryonline.com/atelier/updates/stable/1.0/
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.metadata.repository/repositories/https\:__atelier.artifactoryonline.com_atelier_updates_stable_1.0/version=1.0.0
/profile/file\:\\2fC\:\\2fUsers\\2fjmillett\\2f.p2\\2f/_SELF_/org.eclipse.equinox.p2.ui.sdk.scheduler/autoUpdateInit=true

 

Any help will be much appreciated.

Jean Millette · Jul 26, 2016 go to post

Thanks Michael, this is a very promising approach. Looking forward to having the related prodlog addressed.

Best,

Jean