Problems using SET QUERY_BAND from ODBC

Connectivity

Problems using SET QUERY_BAND from ODBC

I am having problems using the SET QUERY_BAND via ODBC. I need to use it followed by a SELECT statement in a production context. I am not at liberty to change the SQL code nor post the actual code, but I have created a very basic equivalent to illustrate the problem.

If I use the following code

SET QUERY_BAND='pattern=PR%;' UPDATE FOR SESSION;

SELECT COUNT(*) FROM DBC.TABLESV

WHERE TABLENAME LIKE GETQUERYBANDVALUE(0, 'pattern');

then it works fine in Teradata SQL Assistant, but chokes in ODBC with the message "Only an ET or null statement is legal after a DDL Statement". OK, fine, whatever, I then changed the code to the following

BEGIN TRANSACTION;

SET QUERY_BAND='pattern=PR%;' FOR TRANSACTION;

SELECT COUNT(*) FROM DBC.TABLESV

WHERE TABLENAME LIKE GETQUERYBANDVALUE(0, 'pattern');

END TRANSACTION;

Again, works fine in Teradata SQL Assistant but when using it via ODBC I get the message "SET QUERY_BAND for TRANSACTION must be the first statement in a multi-statement request". Which is baffling me. Is anyone able to help me out here?

9 REPLIES
Senior Apprentice

Re: Problems using SET QUERY_BAND from ODBC

Can you check if your ODBC session is connected using ANSI transaction mode?

Re: Problems using SET QUERY_BAND from ODBC

Just checked the Teradata ODBC Driver Options panel via the 32-bit ODBC Administrator and the Session Mode option is Teradata.

Senior Apprentice

Re: Problems using SET QUERY_BAND from ODBC

You can also set the session mode in your ODBC connection string.

Which tool are you using for submitting the queries?

Fan

Re: Problems using SET QUERY_BAND from ODBC

Dieter,

You mentioned setting the session mode query_band in the ODBC connection string? Any example of that would be awesome

Thanks

SvenD

Re: Problems using SET QUERY_BAND from ODBC

I am trying to read data from Teradata DB in a MapReduce job using the JDBC Driver. I get the ... Anyone had this problem and know how to fix it?   http://goo.gl/atzlahhttp://goo.gl/QP7ADP

Teradata Employee

Re: Problems using SET QUERY_BAND from ODBC

it works fine in Teradata SQL Assistant, but chokes in ODBC with the message "Only an ET or null statement is legal after a DDL Statement".

Are you attempting to execute a Multi-Statement Request? 

Did you try "Execute Parallel" in SQL-A?

SQL-A "Execute" will parse the Query and send each statement (Single Statement Request) separately to the Teradata Database. Whereas "Execute Parallel" will send the entire query as a multi-statement request to the Teradata Database.

Re: Problems using SET QUERY_BAND from ODBC

Dieter, sorry in the delay in getting back to you (other work intervened). I am using just ADODB in VBScript in a Windows 7 64-bit environment. the relevant code (minus declarations and error handling) is shown below:

sWalletRef = "MyTDPassword"

sDB = "...name of database..."

sSQL = "...SQL to execute as above..."

Set objNet = CreateObject("WScript.Network")

sCommand ="DSN=Teradata_Production;Databasename=" & sDB & ";UID=" & objNet.UserName & ";PWD=$tdwallet(" & sWalletRef & ")"

Set objConn = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.Recordset")

objConn.Open sCommand

objConn.CommandTimeout = 100

Set objRS = objConn.Execute(sSQL)

objRS.MoveFirst

MsgBox "Query returned " & objRS.Fields(0).Value & " records", vbInformation, "Result"

Teradata Employee

Re: Problems using SET QUERY_BAND from ODBC

So sSQL contains both the SET QUERY_BAND and the SELECT, i.e. it's a multi-statement request?

Then don't include BEGIN TRANSACTION / END TRANSACTION. Just use SET QUERY_BAND ... FOR TRANSACTION and SELECT.

Or send a SET QUERY_BAND ... FOR SESSION and SELECT as two separate requests.

Re: Problems using SET QUERY_BAND from ODBC

Thanks for the suggestions Fred! The first idea didn't work; the query failed to return any rows. However, I split the query into two separate requests (i.e. two separate calls to the Execute method) and that achieved the desired result.