topic Re: Error for JMP 15 users that JMP 16 PRO users don't have with querying a table with JMP addin Script in Discussions

Error for JMP 15 users that JMP 16 PRO users don't have with querying a table with JMP addin Script

bjbreitling — Fri, 09 Jun 2023 16:57:57 GMT

I have JMP 16 but and have no issues with my JMP addin query working. JMP 15 users have an error when running the below and don't get the table.

Open Database(
    "DSN=PI SQL Connection Lab;Description=PI SQL Connection Lab;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=LabData",
    "select t.* from (select t.*, row_number() over (partition by SiteID, TimeStamp order by (select null)) as seqnum FROM dbo.CornOilManualEntryDataAvgsAll t WHERE t.\!"TimeStamp\!" >= DATEADD(d, -365, getdate())) t where seqnum = 1",
    " Year dbo.CornOilManualEntryDataAvgsAll ORDER BY dbo.CornOilManualEntryDataAvgsAll.TimeStamp, dbo.CornOilManualEntryDataAvgsAll.SiteID",
    "Year Manual Data"
);
// Change column info: TimeStamp
Data Table( "Year Manual Data" ):TimeStamp << Data Type(
    Numeric,
    Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ),
    Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
    Format( "m/d/y h:m", 19 )
) << Set Modeling Type( "Continuous" );

Re: Error for JMP 15 users that JMP 16 PRO users don't have with querying a table with JMP addin Script

Mark_Bailey — Fri, 29 Apr 2022 12:05:36 GMT

I am surprised that this function call ever works. It takes 3 arguments, not 4 arguments. The first argument is for the connection. The second argument is the SQL. The third and optional argument is the name of the resulting data table.

Re: Error for JMP 15 users that JMP 16 PRO users don't have with querying a table with JMP addin Script

jthi — Fri, 29 Apr 2022 17:48:24 GMT

Seem very weird that error has table name as All Manual Data and script is using Year Manual data. Year somehow gets replaced with All in JMP15?

Based on documentation (jmp.com) there seems to be two optional arguments, visibility and table name.

Open Database(dataSourceName|"Connect Dialog", "SELECT ..."|"SQLFILE..."|tableName, <"Invisible"|"Private">, <outputTableName>)

Re: Error for JMP 15 users that JMP 16 PRO users don't have with querying a table with JMP addin Script

bryan_boone — Tue, 03 May 2022 19:13:51 GMT

Open Database says it returns a DataTable.

That is, in JMP 15 can you do:

dt = Open Database(
    "DSN=PI SQL Connection Lab;Description=PI SQL Connection Lab;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=LabData",
    "select t.* from (select t.*, row_number() over (partition by SiteID, TimeStamp order by (select null)) as seqnum FROM dbo.CornOilManualEntryDataAvgsAll t WHERE t.\!"TimeStamp\!" >= DATEADD(d, -365, getdate())) t where seqnum = 1",
    " Year dbo.CornOilManualEntryDataAvgsAll ORDER BY dbo.CornOilManualEntryDataAvgsAll.TimeStamp, dbo.CornOilManualEntryDataAvgsAll.SiteID",
    "Year Manual Data"
);
// Change column info: TimeStamp
dt:TimeStamp << Data Type(
    Numeric,
    Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ),
    Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
    Format( "m/d/y h:m", 19 )
) << Set Modeling Type( "Continuous" );