JDBC - Problem calling macro with result-set

Database

JDBC - Problem calling macro with result-set

I have a macro, defined something like this (I've tried to anonymize things a little):

[code]REPLACE MACRO xxx
(
INbruker_id CHARACTER(7)
,INselskap_id INTEGER
,INresponse_dt DATE FORMAT 'YYYY-MM-DD'
,INresponse_tm CHARACTER(8)
,INnext_step_dt DATE FORMAT 'YYYY-MM-DD'
,INkunde DECIMAL(11,0)
,INpart_ref CHAR(15)
,INfornavn VARCHAR(256)
,INetternavn VARCHAR(256)
,INtelefon VARCHAR(64)
,INepost VARCHAR(256)
,INadresse VARCHAR(256)
,INpostnummer INTEGER
,INfreetxt VARCHAR(2048)
,INfrist_kl INTEGER
,INeksref VARCHAR(64)
,INaccount_id INTEGER
)
AS (
ABORT 'Unknown postal code'
WHERE :INpostnummer NOT IN
(
SELECT
postnummer
FROM
xxx
)
;

INSERT INTO xxx
( pers_id
,kunde
,account_id
,brannkasse
,bruker_id
,response_dt
,response_tm
,next_step_dt
,frist_kl
,part_ref
,fornavn
,etternavn
,telefon
,epost
,adresse
,postnummer
,freetxt
,eksref
,lastedato_ts
)
SELECT
:INkunde
,:INkunde
,:INaccount_id
,PnrLnk.brannkasse
,:INbruker_id
,:INresponse_dt
,:INresponse_tm
,:INnext_step_dt
,:INfrist_kl
,:INpart_ref
,:INfornavn
,:INetternavn
,:INtelefon
,:INepost
,:INadresse
,:INpostnummer
,:INfreetxt
,:INeksref
,CURRENT_TIMESTAMP(0)
FROM
xxx AS PnrLnk
WHERE
postnummer = :INpostnummer
;

UPDATE xxx
SET
kunde = pers_id
WHERE
kunde IS NULL
;

SELECT
pers_id
FROM
xxx
WHERE
kunde = :INkunde
OR (fornavn = :INfornavn AND etternavn = :INetternavn AND postnummer = :INpostnummer
AND response_dt = :INresponse_dt AND response_tm = :INresponse_tm AND next_step_dt = :INnext_step_dt
AND eksref = :INeksref)
;
);[/code]

As you see we have a small select at the end of the macro. It works in SQLAdmin with ODBC connection. When run through JDBC connection however, I get the following error:

When run with execute() or executeUpdate() it does work. But I don't get any result back.

I have macros working from JDBC both as simpler selects and pure updates. But this macro combining update and select seems to be a bit more complicated..

Can anyone help?
5 REPLIES

Re: JDBC - Problem calling macro with result-set

Seems i don't have access to edit my own post..

The error message looks like this:

Re: JDBC - Problem calling macro with result-set

[NCR] [Teradata JDBC Driver] : executeQuery() cannot be used when there is no result set expected; use executeUpdate() or execute()

(is it just me or do most of the markup options just leave white space?)
Teradata Employee

Re: JDBC - Problem calling macro with result-set

This is just a guess; Add Select DATE to the very begining of the macro.

I think the JDBC driver is looking at the very first Activity Type as oppose to scanning for data retuning Activity Types.
Enthusiast

Re: JDBC - Problem calling macro with result-set

You cannot make use of executeQuery() because the macro modifies data. the executeUpdate() API is not designed to return resultsets either.

You should use execute() and make use of the getResultSet() and getMoreResultSets() api calls to go through the "multiple" result sets (some could be null, like for example for the update statement) when getMoreResultSets() returns false, you don't have any more resultsets to go through.

Refer the Sun Java documentation for the class java.sql.PreparedStatement for more details on these API methods.

Re: JDBC - Problem calling macro with result-set


Thanks. Figured out I was trying to get a ResultSet from the update results.. Code fixed now to use getMoreResults(), and wait for it to be true :)