Problem with EXECUTE SCRIPT via ODBC

Contributor

Hi.

We're trying to call lua scripts via ODBC, but can't get it to work.

 

Script creation works without a problem:

 

CREATE OR REPLACE SCRIPT TOOLS.myscript() AS
 function get_users()
  return {'John', 'Jane'}
 end
 output(table.concat(get_users(), ", "));

 

Call:

execute script TOOLS.myscript() with output;

 

Error Message:

dbms execute failed (return code -1) for db statement:
script TOOLS.myscript() with output
ODBC Error
SQLCODE: -6811776
SQLSTATE: 42000
MESSAGE: [EXASOL][EXASolution driver]syntax error, unexpected '{', expecting FOR_ or LOOP_ or WHILE_ or REPEAT_ [line 1, column 1] (Session: 1679881068812482705)

If we execute the very same "EXECUTE SCRIPT" statement via exaplus oder DBEAVER (jdbc) it works as expected.

 

It seems as if the ODBC driver recognizes the "EXECUTE" as keyword in itself and expects the rest of the statement to be a stored procedure or something like that. Probably the ODBC driver just lacks the support for "EXECUTE SCRIPT". Can anyone confirm this problem and/or has an idea how we can work around this issue? (or just tells us how to do it correctly in case we screwed it up 😉 ).

Using exaplus or jdbc instead is obviously an alternative but requery some other nasty workarounds in our setup, because to the we're using relies on ODBC drivers.

 

1 ACCEPTED SOLUTION

Contributor

Hi.

We found the problem on the application side in Ab Initio, so the following explanation might not help lots of people, but I will tell anyways ;).

While examining this issue further we did set the ODBC log level to VERBOSE and dug through the log files of the ODBC driver. What we found is, that the ODBC driver received the following statement:

{ call script TOOLS.myscript() with output }

Instead of the statement we entered:

EXECUTE SCRIPT TOOLS.myscript() with output

Solution

So there had to be some kind of rewritting the query on the client side. We spoke to the Ab Initio support and as it turned out, Ab Initio does the translation by itself. Whenever you enter "EXECUTE SCRIPT" it gets translated to "{ CALL SCRIPT ... } which seems to be valid ODBC syntax in general and the better option in Ab Initios Opinion. Obviously do other DBMS support this syntax, but Exasol does not.

To stop Ab Initio from translating we need to need to use a little hack and just add a comment to the beginning of the statement. This way the rewriting is prevented. A real switch in the application with follow in the future.

/* some random comment */ EXECUTE SCRIPT ....;

View solution in original post

6 REPLIES 6

Contributor

Hi.

We found the problem on the application side in Ab Initio, so the following explanation might not help lots of people, but I will tell anyways ;).

While examining this issue further we did set the ODBC log level to VERBOSE and dug through the log files of the ODBC driver. What we found is, that the ODBC driver received the following statement:

{ call script TOOLS.myscript() with output }

Instead of the statement we entered:

EXECUTE SCRIPT TOOLS.myscript() with output

Solution

So there had to be some kind of rewritting the query on the client side. We spoke to the Ab Initio support and as it turned out, Ab Initio does the translation by itself. Whenever you enter "EXECUTE SCRIPT" it gets translated to "{ CALL SCRIPT ... } which seems to be valid ODBC syntax in general and the better option in Ab Initios Opinion. Obviously do other DBMS support this syntax, but Exasol does not.

To stop Ab Initio from translating we need to need to use a little hack and just add a comment to the beginning of the statement. This way the rewriting is prevented. A real switch in the application with follow in the future.

/* some random comment */ EXECUTE SCRIPT ....;

View solution in original post

Community Manager
Community Manager

Thanks for the update! 

Maybe one other help for you: Instead of changing your code with the comment before hand, you could also build a pre-processor scripts which transforms all "call script" statements back to "EXECUTE SCRIPT". 

But it leads to kind of a funny behavior...

  1. User writes EXECUTE SCRIPT
  2. Client converts EXECUTE SCRIPT to call script
  3. call script sent to database over the driver
  4. database converts call script back to EXECUTE SCRIPT
  5. results sent to client.

Anyway, just another solution that could work if you don't want to add a comment in front of everything 🙂 

 

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Contributor

@mwellbro @exa-Nico Thx for your replies. 

No further code to be shown. We're using Ab Initio and just put this code into their "run_sql" components. They have a component that awaits a result set and another that doesn't. Already tried all combinations using those components and Functions that return an output (like the example above) or not (simple Function just updating a row). Always returns the same error. My first guess was a problem with the ODBC driver, because Abinitio in general just passes through those error messages not doing anything special in between.

FYI: We're using ODBC driver 6.1.7 atm, but as you guys already tried 6.0.x and 7.x sucessfully I think it's more likely there's an issue on the application side. So I will dig deeper into that. Thx for your help so far, I'll keep you updated if I find something out.

Community Manager
Community Manager

I ran a quick test as well and connected Excel with my local database (by clicking on Data -> Get Data -> ODBC) and then sending the same EXECUTE SCRIPT statement that you did and the ODBC driver did not have any issues with the execute command and sent the results (even when specifying output).

Maybe it would help to show the code that you are running which is making the ODBC call? Or is it some application that is running it? 

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Community Manager
Community Manager

Also this was using the 7.0 ODBC driver for completeness 😁

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Xpert

Hi @Geipman ,

I think the problem is a conceptual one: what you perceive to be an SQL-result is actually some "piped-out output" from
the script, not a real result set.

But using non-UDF-scripts isn´t my strong suit , so someone else can probably give you a better answer - I can however at least verify that if I call it via an ODBC-based application, I get the correct entries in the exa_dba_audit_* tables ( showing that indeed ODBC was used and ROW_COUNT=1 ) , but my application ( in this particular case an InformaticaPowerCenter SQL-Transformation) does not receive any result row - also doesn´t throw an error ).

Was tested with an 6.0.14 driver, will see if I get around to test 6.2.x - but probably someone else will have answered your question by then 😉

Cheers,
Malte