cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
bjbreitling
Level IV

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

bjbreitling_0-1651172780710.png

 

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" );
3 REPLIES 3

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

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.

jthi
Super User

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

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>)


 

-Jarmo

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

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" );