I didn't count the number of question marks but I assume you've verified that it matches the number of columns.

Also, there are limitations on the number of command line arguments you can pass but that number is rather high. I don't recall exactly what it is but it is certainly limited. At one time we established 16 as the maximum number of arguments. As I recall, we did nothing to impose an arbitrary limit but ObjectScript itself does have a limit.

My next questions are meant to establish where the error is reported. You can prepare a dynamic SQL statement and then execute it separately and multiple times. Is the error reported during prepare or during execute?

PS: (edited):

Sorry, I see now your code. You are using a HS Adapter to execute this statement. That leads me to a different question - can you prepare and execute this statement as a Dynamic SQL Statement?

set statement = ##class(%SQL.Statement).%New()

set status = statement.%Prepare(.tSQL)

If status is okay (1) then execute it - set result = statement.%Execute( <your parameter values go here>)

Then check result.%SQLCODE.

In the meantime, I will take a look at the Adapter (not my area but I can still look) to see if there is some limitation.

I agree with Herman - to a point. Leave the IDKEY index alone and just accept the system assigned idkey. In most cases this is the best policy. But - there are potentially several "candidate keys", all can be defined - or not, but one can be chosen to be the "primary key". In that case, define the key not as unique but as "primarykey". Some tooling works better with a defined primary key. It does not interfere with the IDKEY unless it is also defined as the "idkey".

Hi Jonathan,

I am very interested in this problem (I wrote the early version of dynamic statement parameter binding). My first question is simple - can you execute this statement using embedded SQL? If you can then we need to dig further to discover the correct error (some errors get masked as syntax when there is a different problem).

Thanks,

Dan

PS:

Of course, you will have to supply values for ? to test compile this as an embedded statement (:hv1, :hv2... can work) - or - can you try this using an SQL utility or as a ODBC/JDBC statement?

    set java = $system.external.getJavaGateway()
    do java.addToPath("/home/myhome/mariadb-java-client-2.7.2.jar")
    set mariads = java.new("org.mariadb.jdbc.MariaDbDataSource","jdbc:mariadb://myhost:3306/SAMPLES")
    set mariaconn = mariads.getConnection("myuser","secret")
    set pstmt = mariaconn.prepareStatement("select * from person limit 5")
    set jdbcresult = pstmt.executeQuery()
    set jdbcmetadata = jdbcresult.getMetaData()
    for i=1:1:jdbcmetadata.getColumnCount() {
        write jdbcmetadata.getColumnName(i),$char(9)
    }
    while jdbcresult.next() {
        write !,$increment(rowcnt),$char(9)
        for i=1:1:jdbcmetadata.getColumnCount() {
            write jdbcresult.getString(i),$c(9)
        }
    }
    write !!!,"OR I COULD JUST USE MY ALREADY IMPLEMENTED RESULT SET RENDERER FROM ANOTHER JAVA PROJECT",!!!
    do java.addToPath(..#EXTERNALLIBPATH)
    set ers = java.new("external.test.ExternalResult",pstmt.executeQuery())
    do ers.renderTable()
    do mariaconn.close()
    do java.disconnect()

And the results:

USER>do ##class(external.test.MariaJDBC).communityDemo()
name    ssn    dob    home_street    home_city    home_state    home_zip    
1    Basile,Molly M.    452-57-8033    1994-06-02    1153 First Street    Xavier    SD    98033    
2    Cooke,Howard F.    131-62-3894    2017-12-09    5172 Washington Place    Zanesville    NE    44980    
3    Donaldson,Phil R.    480-79-5019    1990-01-23    4429 Elm Street    Miami    WA    67638    
4    Eisenstien,Michael D.    655-11-6334    1948-08-14    4676 Elm Avenue    Reston    KY    52729    
5    Faust,Mo E.    772-42-3921    2018-01-10    826 Maple Avenue    Youngstown    OH    37180    

OR I COULD JUST USE MY ALREADY IMPLEMENTED RESULT SET RENDERER FROM ANOTHER JAVA PROJECT

┌─────────────────────┬───────────┬──────────┬─────────────────────┬──────────┬──────────┬────────┐
│name                 │ssn        │dob       │home_street          │home_city │home_state│home_zip│
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Basile,Molly M.      │452-57-8033│1994-06-02│1153 First Street    │Xavier    │SD        │98033   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Cooke,Howard F.      │131-62-3894│2017-12-09│5172 Washington Place│Zanesville│NE        │44980   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Donaldson,Phil R.    │480-79-5019│1990-01-23│4429 Elm Street      │Miami     │WA        │67638   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Eisenstien,Michael D.│655-11-6334│1948-08-14│4676 Elm Avenue      │Reston    │KY        │52729   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Faust,Mo E.          │772-42-3921│2018-01-10│826 Maple Avenue     │Youngstown│OH        │37180   │
└─────────────────────┴───────────┴──────────┴─────────────────────┴──────────┴──────────┴────────┘

Exactly - you haven't used the Gateways before. Why? IMO, the Gateways (mostly Java and DotNet) were a bit cumbersome to deal with. With ELS, we have default servers that can be easily managed and discovered, they require little or no configuration to get started (some ELS's do require some configuration if the language platform support is not discoverable by IRIS), and the interface is simple and direct.

One quick example, using Python, is to use the os module to get the current working directory:

USER>set python = $system.external.getPythonGateway()

USER>write python.invoke("os","getcwd")
/opt/intersystems/iris/xdbc/mgr

All that is required is a gateway connection to an external server, your code needs to be visible to that external server, either by direct placement into the default path for that language platform or by explicitly adding it by calling addToPath(), and public interfaces in your external code. By "external", I mean code that isn't written inside of the IRIS Server - ObjectScript.

When your external code writes to the "system output" device, that output is redirected to the IRIS current device. In my above example, the renderTable() function simply constructs a formatted string using the AsciiTable library (got it from GitHub) and writes it using System.out.println(formattedstring). I simply copied the output that was displayed in my IRIS session terminal window and pasted it in the original post. No extra work involved.

If your external code returns an object then you can indeed make use of that object as if it were a local IRIS Object - because it is. It is actually a network proxy object that communicates with the original external language object. That communication can actually be full duplex - your external code can talk to IRIS and IRIS can talk to your external code.

One really simple example that I think is quite profound is using JDBC in IRIS. This isn't JDBC Gateway - that still exists and it is what it is. I am talking about using a Java JAR file that contains a JDBC driver and you want to use it. I'll try a simple demo here. I have MariaDB installed on my system so I'll just use it to query a table I have defined there.

First - using mariaDB from the command line interface:

 ~ % mysql -u myusername -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.8-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| SAMPLES            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.010 sec)

MariaDB [(none)]> use SAMPLES
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [SAMPLES]> show tables;
+-------------------+
| Tables_in_SAMPLES |
+-------------------+
| person            |
+-------------------+
1 row in set (0.000 sec)

MariaDB [SAMPLES]> select * from person limit 5
    -> ;
+-----------------------+-------------+------------+-----------------------+------------+------------+----------+
| name                  | ssn         | dob        | home_street           | home_city  | home_state | home_zip |
+-----------------------+-------------+------------+-----------------------+------------+------------+----------+
| Basile,Molly M.       | 452-57-8033 | 1994-06-02 | 1153 First Street     | Xavier     | SD         | 98033    |
| Cooke,Howard F.       | 131-62-3894 | 2017-12-09 | 5172 Washington Place | Zanesville | NE         | 44980    |
| Donaldson,Phil R.     | 480-79-5019 | 1990-01-23 | 4429 Elm Street       | Miami      | WA         | 67638    |
| Eisenstien,Michael D. | 655-11-6334 | 1948-08-14 | 4676 Elm Avenue       | Reston     | KY         | 52729    |
| Faust,Mo E.           | 772-42-3921 | 2018-01-10 | 826 Maple Avenue      | Youngstown | OH         | 37180    |
+-----------------------+-------------+------------+-----------------------+------------+------------+----------+
5 rows in set (0.016 sec)

MariaDB [SAMPLES]>

Next, I'll follow up with an example of doing this from within IRIS.

You are right, of course. I was puzzled by the lack of documentation myself. I did some research and I've been told that there will be documentation available for the External Language Servers and $system.external.

The $system.external.Help() feature is something that has been part of the product for many years. It is not a substitute for proper documentation but serves to aid the command line user when questions regarding function names and arguments need a quick answer.

The ELS features are based on what is known as "Dynamic Object Gateways". There is extensive documentation available for them. That documentation is a bit dated in that it doesn't incorporate the new $system.external API's but still mostly accurate. Obtaining a gateway connection to an External Language Server by simply invoking $system.external.getGateway(<gatewayname>) is just one way we've simplified that interface.

There are default ELS's defined for Java, Python, DotNet, and R. Each is supported not only by the getGateway() that accepts the name of the ELS but also by get<Language>Gateway() functions for Java, DotNet, Python and R that return gateway connections to the default language servers.

If there is no storage definition then a storage definition whose type is %Storage.Persistent is created when compiling the class. This is done before the storage definition is compiled (every class member is compiled by the class compiler). Then, when the storage definition is compiled and if the type is %Storage.Persistent then the %Storage.Persistent.STORAGECOMPILERCLASS will generate a full storage definition for the current class definition.

What does that mean? Well - if this is the first time this storage definition has been compiled then it is most likely empty, other than the type class. The STORAGECOMPILERCLASS will generate a complete storage definition. But even if the storage definition is already defined, the STORAGECOMPILERCLASS still reviews that definition. If there are any changes detected to the class definition then the storage definition is updated to reflect those changes.

That means that the user can manually edit the storage definition at any time and the storage compiler will simply make sure that the definition is "complete" - no missing properties, no missing indexes, and so on.

Bottom line is that the user is free to make any desired changes, including deleting the storage definition completely.

Keep in mind that some changes can make the compiled class incompatible with existing data.

Class utility.StatementColumns Extends %SQL.CustomQuery
{

Parameter SQLNAME As String = "statement_columns";

Property columns As %Collection.ListOfObj [ Internal, Private ];

Property columnPtr As %String [ Internal, Private ];

Property atEnd As %Boolean [ Internal, Private ];

Property columnType As %String;

Property colName As %String(MAXLEN = 255);

Property ODBCType As %Integer;

Property precision As %Integer;

Property scale As %Integer;

Property isNullable As %Boolean;

Property label As %String(MAXLEN = 255);

Property tableName As %String;

Property schemaName As %String;

Property qualifier As %String;

Property isAutoIncrement As %Boolean;

Property isCaseSensitive As %Boolean;

Property isCurrency As %Boolean;

Property isReadOnly As %Boolean;

Property isRowVersion As %Boolean;

Property isUnique As %Boolean;

Property isAliased As %Boolean;

Property isExpression As %Boolean;

Property isHidden As %Boolean;

Property isIdentity As %Boolean;

Property isKeyColumn As %Boolean;

Property isRowId As %Boolean;

Property isList As %Boolean;

Property property As %Dictionary.CompiledProperty;

/// The objects type class
Property typeClass As %Dictionary.CompiledClass;

Property clientType As %Integer;

Method %OpenCursor(statement As %String) [ Private ]
{
    set ..columns = $system.SQL.Prepare(statement).%Metadata.columns
    set ..atEnd = 0
    set ..columnPtr = ""
}

Method %FetchCursor() As %Library.Integer
{
    set response = 0
    if '..atEnd {
        set next = ..columnPtr
        set column = ..columns.GetNext(.next)
        if next '= "" {
            set response = 1
            set ..columnPtr = next
            do ..mapColumnToRow(column)
        } else {
            set ..atEnd = 1
            set ..columnPtr = ""
            do ..clearRow()
        }
    }
    return response
}

Method mapColumnToRow(column As %SQL.StatementColumn)
{
    set ..columnType = "SQLRESULTCOL"
    set ..colName = column.colName
    set ..ODBCType = column.ODBCType
    set ..precision = column.precision
    set ..scale = column.scale
    set ..isNullable = column.isNullable
    set ..label = column.label
    set ..tableName = column.tableName
    set ..schemaName = column.schemaName
    set ..qualifier = column.qualifier
    set ..isAutoIncrement = column.isAutoIncrement
    set ..isCaseSensitive = column.isCaseSensitive
    set ..isCurrency = column.isCurrency
    set ..isReadOnly = column.isReadOnly
    set ..isRowVersion = column.isRowVersion
    set ..isUnique = column.isUnique
    set ..isAliased = column.isAliased
    set ..isExpression = column.isExpression
    set ..isHidden = column.isHidden
    set ..isIdentity = column.isIdentity
    set ..isKeyColumn = column.isKeyColumn
    set ..isRowId = column.isRowId
    set ..isList = column.isList
}

Method clearRow()
{
    set ..columnType = ""
    set ..colName = ""
    set ..ODBCType = ""
    set ..precision = ""
    set ..scale = ""
    set ..isNullable = ""
    set ..label = ""
    set ..tableName = ""
    set ..schemaName = ""
    set ..qualifier = ""
    set ..isAutoIncrement = ""
    set ..isCaseSensitive = ""
    set ..isCurrency = ""
    set ..isReadOnly = ""
    set ..isRowVersion = ""
    set ..isUnique = ""
    set ..isAliased = ""
    set ..isExpression = ""
    set ..isHidden = ""
    set ..isIdentity = ""
    set ..isKeyColumn = ""
    set ..isRowId = ""
    set ..isList = ""
}

}

I agree with Tim but I'll take it one step further. Foreign keys are much more useful than relationships. After all, a relationship is simply a foreign key that maintains references to instances of the related class in memory. The projection of a relationship to SQL is simply as a foreign key. The set of related objects is simply populated using an SQL query. The problem with relationships is that they are extremely sticky and that can cause large numbers of objects to be inadvertently swizzled into memory. With foreign keys you have no in-memory model. That means with a foreign key you have to manage desired swizzling. Some view that as a problem, I view it as an advantage.

Another advantage of using foreign keys is that you can define multiple foreign keys using the same key component properties. No need to define a direct reference.

Creative minds might come up with a calculated property whose value is derived from the foreign key components, adding property methods to manipulate the related object/objects. This calculated property could be the direct reference. Perhaps transient would be better as a transient property also has instance memory allocated for it.

Custom queries can also be instantiated without using SQL. Simply call %New and pass in the arguments that are defined by the %OpenCursor method. There is one difference here - the first argument of %New is the SELECTMODE and subsequent arguments correspond to the %OpenCursor arguments. Once instantiated, the interface is like any other %SQL.IResultSet.

USER>set result = ##class(example.custom.Query).%New(,"https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*&DATE_=7","Default")

USER>write result.%Next()
1
USER>write result.name
Alabama
USER>write result.population
4849377
USER>while result.%Next() { write !,result.name,": ",result.population }

Alaska: 736732
Arizona: 6731484
Arkansas: 2966369
California: 38802500

First, keep in mind that all implementations (faithful implementations that is) of %SQL.CustomQuery are also projected as table-valued functions. That means you can include the function in the FROM clause of a SELECT statement.

The process of implementing a custom query is simple. These steps are described in the %SQL.CustomQuery class documentation so I'll just summarize here.

  1. Define a new class that extends %SQL.CustomQuery;
  2. Override the SQLNAME parameter, assign a valid SQL identifier that is to be the name of the TVF;
  3. Define properties, in order, that are the columns of each row returned by this query. Let's call these "result columns". Each result column is defined as a non-private property;
  4. Define properties that you will need to maintain the source data, pointers, etc. that you will use to manage the data used to produce rows. These properties are defined as "private";
  5. Override %OpenCursor. Add parameters to this method override that correspond to the input parameters that will be passed when instantiating the custom query;
  6. Override %FetchCursor. In this method, check for end of data. If not at the end then populate all of the result properties with data and return 1 (true). Otherwise, clear all result properties and return 0;
  7.  Override %CloseCursor. In this override, release any resources acquired during instantiation and perform any necessary cleanup.

I won't post the version of the class that produces this output since the version of %Net.Http in current versions of CE/IRIS do not have a working GetJSON() method. The version of the class I'm posting simply passes in the raw JSON data as an argument.

The query:

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_query('https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*...','Default') 
ORDER BY population DESC

and the results:

stateCode name population
06 California 38802500
48 Texas 26956958
12 Florida 19893297
36 New York 19746227
17 Illinois 12880580

5 row(s) affected

Using this version of a custom query class:

 
Class example.custom.JsonQuery Extends %SQL.CustomQuery
{

Parameter SQLNAME As String = "sample_custom_json_query";

Property data As %Library.DynamicAbstractObject [ Private ];

Property iterator As %Iterator.AbstractIterator [ Private ];

Property atEnd As %Integer [ InitialExpression = 0, Private ];

Property stateCode As %String;

Property name As %String;

Property population As %Integer;

Method %OpenCursor(data As %String(MAXLEN="")) [ Private ]
{
    try {
        if $isobject(data) {
            set ..data = data
        } else {
            set ..data = [].%FromJSON(data)
        }
        set ..iterator = ..data.%GetIterator()
        if '..iterator.%GetNext(.key,.value) {
            set ..atEnd = 0
            set ..iterator = ""
        }
    } catch exception {
            // this is just a place holder, this method reports errors by throwing an exception
            // but a catch can allow the user to log errors or perform some self-healing action
        throw exception
    }
}

Method %FetchCursor() As %Library.Integer
{
    set response = 0
    if ($isObject(..iterator)) && ('..atEnd) {
        if ..iterator.%GetNext(.key,.value) {
            set ..name = value.%Get(0)
            set ..population = value.%Get(1)
            set ..stateCode = value.%Get(3)
            set response = 1
        } else {
            set ..atEnd = 1
            set ..iterator = ""
        }
    } else {
        set ..name = ""
        set ..population = ""
        set ..stateCode = ""
    }
    return response
}

Method %CloseCursor() [ PlaceAfter = %Next, Private ]
{
        // not really necessary as %OnClose will automatically close the cursor during destruction
        // but users can place code here to clean up other resources allocated for this query instance
        // that are external to the query instance. Like a temporary global.
    set ..iterator = ""
	set ..data = ""
}

}

and this query

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_json_query('[["STNAME","POP","DATE_","state"],["Alabama","4849377","7","01"],["Alaska","736732","7","02"],["Arizona","6731484","7","04"],["Arkansas","2966369","7","05"],["California","38802500","7","06"],["Colorado","5355866","7","08"],["Connecticut","3596677","7","09"],["Delaware","935614","7","10"],["District of Columbia","658893","7","11"],["Florida","19893297","7","12"],["Georgia","10097343","7","13"],["Hawaii","1419561","7","15"],["Idaho","1634464","7","16"],["Illinois","12880580","7","17"],["Indiana","6596855","7","18"],["Iowa","3107126","7","19"],["Kansas","2904021","7","20"],["Kentucky","4413457","7","21"],["Louisiana","4649676","7","22"],["Maine","1330089","7","23"],["Maryland","5976407","7","24"],["Massachusetts","6745408","7","25"],["Michigan","9909877","7","26"],["Minnesota","5457173","7","27"],["Mississippi","2994079","7","28"],["Missouri","6063589","7","29"],["Montana","1023579","7","30"],["Nebraska","1881503","7","31"],["Nevada","2839099","7","32"],["New Hampshire","1326813","7","33"],["New Jersey","8938175","7","34"],["New Mexico","2085572","7","35"],["New York","19746227","7","36"],["North Carolina","9943964","7","37"],["North Dakota","739482","7","38"],["Ohio","11594163","7","39"],["Oklahoma","3878051","7","40"],["Oregon","3970239","7","41"],["Pennsylvania","12787209","7","42"],["Rhode Island","1055173","7","44"],["South Carolina","4832482","7","45"],["South Dakota","853175","7","46"],["Tennessee","6549352","7","47"],["Texas","26956958","7","48"],["Utah","2942902","7","49"],["Vermont","626562","7","50"],["Virginia","8326289","7","51"],["Washington","7061530","7","53"],["West Virginia","1850326","7","54"],["Wisconsin","5757564","7","55"],["Wyoming","584153","7","56"],["Puerto Rico Commonwealth","3548397","7","72"]]') 
ORDER BY population DESC

produces the same result:

stateCode name population
06 California 38802500
48 Texas 26956958
12 Florida 19893297
36 New York 19746227
17 Illinois 12880580

5 row(s) affected

I am happy to post other examples if you wish.

Dan

Sure, Fab, but let's compare apples to apples. Your test takes the most highly optimized $list traversal ($listnext) and compares its performance to an iterator. An iterator is an instance of a class and you are invoking methods to iterate through a dynamic array which, by its very definition, shouldn't have holes in it. So let's compare direct iteration vs. object iterators. I populated array and list the same way you did in your class. Then a simple command line test. Not valid as a white room benchmark but still it gives us a sense of what can be. Try it. Dynamic array random access is much better than $list random access. As the third test shows. 

%SYS>set start=$zh for i=0:1:array.%Size-1 { set disregard=array.%Get(i) } w !,$zh-start        

.000019

%SYS>set p=0,start=$zh while $listnext(list,p,value) { set disregard=value } w !,$zh-start

.007311
%SYS>set start=$zh for i=1:1:$ll(list) { set disregard=$li(list,i) } w !,$zh-start

8.673268