All Forums Connectivity
opolm 2 posts Joined 09/12
05 Sep 2012
Calling a macro via JDBC

Hi,

 

I'm trying to call a Teradata macro via JDBC. I'm using the following code

sql.append("execute dbp.special_macro(123,'1','20120901000000',' 20120904130000','20120801000000');");
PreparedStatement stmt = conn.prepareStatement(sql.toString());

ResultSet result = null;
if (stmt.execute(sql.toString())) {
   result = stmt.getResultSet();

When the code reaches the line with the execute - Statement,  I always get an exception like:

[Teradata JDBC Driver] [TeraJDBC 14.00.00.08] [Error 1094] [SQLState HY000] The use of this method is not valid

I also tried with executeQuery but it was the same result.

I did neither find any hints in google for this kind of exception nor for the error code 1094.

Can anyone give me some advise, what's going wrong here?

Thank you for your help

Oliver

 

 

Tags:
ulrich 816 posts Joined 09/09
05 Sep 2012

From the JDBC documentation

 

Teradata Database Macros

A Teradata Database macro consists of one or more SQL statements. Macros can be executed using Statement.execute() method. The application can retrieve the result using Statement.getResultSet() or Statement.getUpdateCount() methods and in case multiple results are returned, then the application must use Statement.getMoreResults() method to iterate through these results.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

tomnolan 594 posts Joined 01/08
05 Sep 2012

Ulrich is correct that the execute method must be used for executing a macro, not the executeUpdate or executeQuery method.

However, those methods are all overloaded, meaning multiple method definitions with different argument lists, and your problem is due to using the wrong overloaded version of the method.

Your problem is due to using the execute(String) method with a PreparedStatement. That is not valid. The execute(String) method is only for use with a non-prepared regular Statement.

With a PreparedStatement or CallableStatement, use the execute() method with no String argument.

opolm 2 posts Joined 09/12
11 Sep 2012

Oh my god, that was easy :)). It works like a charm. I copied the code from another class, but I overlooked the String parameter.

 

Thank you very much for your help.

 

Oliver

LUCAS 56 posts Joined 06/09
31 Jul 2015

About SQL PRPEPARE and MACRO:
i had in mind that macros allow one parsing step only (with the first execution) , avoiding parsing at each execution of the request.
I have no idea of what SQL PREPARE does vs Macros and i am not familiar with JDBC:
Is SQL PREPARE equivalent to EXEC MACRO in term of parsing, avoiding parsing in each request ?
Thanks for sharing,

Pierre

tomnolan 594 posts Joined 01/08
31 Jul 2015

Preparing a SQL request is a completely different operation from executing an EXEC macro SQL request.
 
For the Teradata Database, parsing can and often does occur when preparing a SQL request, and when executing an EXEC macro SQL request.
 
 
The only time that the Teradata Database avoids parsing is when the parsed SQL request has been previously cached, and all the session and request attributes for the current request match up with the cached request. In this situation, the parsed info is obtained from the cache, and parsing can be skipped.

LUCAS 56 posts Joined 06/09
10 Aug 2015

So, when a parameterized macro is submitted many times from the same session (in a pool of sessions) and parameters are used to pass a date value always filtering on the same column , can we expect an economy of the parsing step after the first execution is done ?
Same logic in case of PreparedStatement ?
Thanks,

tomnolan 594 posts Joined 01/08
10 Aug 2015

Yes, exactly.

LUCAS 56 posts Joined 06/09
14 Aug 2015

Thanks Tom,
when looking for docs about cached plans i just found this page:
http://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/Database_Management/B035_1094_109A/ch17.19.35.html
It's a little bit old (V13) and possibly incomplete, where it refers to macros only.
Is there any complete doc somewhere ?
Pierre

tomnolan 594 posts Joined 01/08
14 Aug 2015

I can't find that section in newer versions of that documentation.
 
This is the Connectivity forum, so it's not the best place for questions about the Teradata Database statement cache.
 
Please start a new forum thread in the Database forum to ask your question, and perhaps Carrie or someone else who is knowledgeable about that topic can answer your question. Thanks.
 

LUCAS 56 posts Joined 06/09
20 Aug 2015

you are right,
A new "connectivity" question about sending properly a "data parcel" with parameter values before an "exec macro" order via JDBC to ensure that the query plan will be kept in query cache (as far as it is eligiblle to caching): what is the good syntax to do that or similar method to get caching with parameters in Java environment ? 
I read the first question above and your answer, but a template could help.
Thanks.

tomnolan 594 posts Joined 01/08
20 Aug 2015

Lucas, please start a new forum topic. Please do not add more questions to this thread. Thanks.

You must sign in to leave a comment.