Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Websocket API prepared statement column names are empty

bobozaur
Contributor

Hello everyone,

I'm the author of the exasol Rust library crate, a Rust database driver for Exasol implemented using the Websocket API. I recently worked on adding prepared statements, and while now I released the latest version by testing against the Exasol 7.1.8 Community Edition, I previously worked at a company that had an Exasol subscription (I believe they recently upgraded to Exasol 7.0 from 6.2).

My question revolves around the response for the createPreparedStatement request, which provides the columns list for the parameters needed for the said prepared statement. Almost everything is fine apart from the fact that all column names are empty string (""). I could swear this was not happening before, when I was testing against Exasol 7.0 or 6.2.

 

For reference, this is an example JSON request:

 

 

{
   "command":"createPreparedStatement",
   "sqlText":"INSERT INTO EXA_RUST_TEST VALUES(?, ?, ?)"
}

 

 

And this is the JSON response:

 

 

{
   "status":"ok",
   "responseData":{
      "statementHandle":1,
      "parameterData":{
         "numColumns":3,
         "columns":[
            {
               "name":"",
               "dataType":{
                  "type":"VARCHAR",
                  "size":50,
                  "characterSet":"UTF8"
               }
            },
            {
               "name":"",
               "dataType":{
                  "type":"VARCHAR",
                  "size":50,
                  "characterSet":"UTF8"
               }
            },
            {
               "name":"",
               "dataType":{
                  "type":"DECIMAL",
                  "precision":10,
                  "scale":0
               }
            }
         ]
      },
      "results":[
         {
            "resultType":"rowCount",
            "rowCount":0
         }
      ],
      "numResults":1
   }
}

 

 

 

The column names are needed in the driver I'm developing to match map-like structures to the actual columns. If this is some sort of limitation, that is fine, but I'd like to know so I can at least document it.

Am I going bananas and this was the behavior all along or is this a bug in the API?

Any insight into this would be appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions

exa-Aleksandr
Team Exasol
Team Exasol

Hi @bobozaur ,

Maybe previously you rather got names for SELECT results?

https://github.com/exasol/websocket-api/blob/master/docs/commands/createPreparedStatementV1.md?plain...

vs

https://github.com/exasol/websocket-api/blob/master/docs/commands/createPreparedStatementV1.md?plain...

BTW, you could also use our docker image for testing purposes, the latest 7.0 should be available there as well.

View solution in original post

4 REPLIES 4

littlekoi
Xpert

I do not think Exasol server ever returned column names in this context, and I am not sure if it is possible. What if someone adds a column between "prepare" and "execute" calls?

 

What you can do instead:

  1. Get column list from Map structure of first row.
  2. Dynamically generate SQL:
    INSERT INTO <table> (<col1>, <col2>, <col3>) VALUES (?, ?, ?);​
  3. Assign parameters in the order of Map structure instead of order of physical columns.

 

An example from pyexasol: https://github.com/exasol/pyexasol/blob/94114ec94670a10e914a6d3ea39ff31c85b9bf2a/pyexasol/ext.py#L36

Please note, you're most likely going to run into limitations and performance problems if you try to send a large amount of data using this method. Consider implementing HTTP transport and CSV streaming instead.

exa-Aleksandr
Team Exasol
Team Exasol

Hi @bobozaur ,

Maybe previously you rather got names for SELECT results?

https://github.com/exasol/websocket-api/blob/master/docs/commands/createPreparedStatementV1.md?plain...

vs

https://github.com/exasol/websocket-api/blob/master/docs/commands/createPreparedStatementV1.md?plain...

BTW, you could also use our docker image for testing purposes, the latest 7.0 should be available there as well.

bobozaur
Contributor

@exa-Aleksandr This was it, yeah. Great catch! So I'm not going crazy, I just misread the response at some point.

Thanks for the docker image tip, it should make it much easier to test.

bobozaur
Contributor

@littlekoi fair point about table definition changing. I suspect any such cases would result in the prepared statement failing on execution though, as some columns could also be removed in between "prepare" and "execute".

I want to avoid dynamically generating SQL because I don't want to limit prepared statements to just INSERTs. I think I'll implement a driver-only named parameter binding for prepared statements, something like:

 

INSERT INTO MY_TABLE VALUES (?col1, ?col2, ?col3);

 

Then the named part of the parameter can be simply ignored on sequence-like types, or used for mapping when using map-like types. And in the event that just "?" is provided, then the name is considered to be "".

HTTP transport is already planned :). Will be available in the next minor version.