Why is TeraData JDBC complaining about these parameters?

Connectivity
t_t
Fan

Why is TeraData JDBC complaining about these parameters?

I have a SQL such as this:

String sql = "SELECT <columns....> "+

  " FROM <table/views ....> "+

  "WHERE a.GEOA_SPATIAL.ST_SPHERICALDISTANCE (NEW ST_GEOMETRY('POINT (? ?)')) < ?";

PreparedStatement stmt = conn.prepareStatement(sql);

stmt.setString(1, obj.getLongitude());
stmt.setString(2, obj.getLatitude());
stmt.setString(3, obj.getRadius());

And then when I run this code it complains about:

Parameter index value 2 is outside the valid range of 1 through 1 teradata

I dont understand what it's complaining about, this is standard JDBC functionality....

If I actually substitute the ?'s with the string without using the setString to replace them then it works fine. It doesnt like JDBC doing the replace.

7 REPLIES
Junior Supporter

Re: Why is TeraData JDBC complaining about these parameters?

Hi.

I'm not an expert in java/JDBC but :

The single quotes are considered LITERALS. You need to construct the string with concatenations.

Something like

String sql = "SELECT <columns....> "+

  " FROM <table/views ....> "+

  "WHERE a.GEOA_SPATIAL.ST_SPHERICALDISTANCE (NEW ST_GEOMETRY('POINT ("+

   Longitude+

   " "+

   Latitude+

   ")')) < "+

   Radius+

   " ";

 

(Not tested)

 

HTH.

 

Cheers.

 

Carlos.

Teradata Employee

Re: Why is TeraData JDBC complaining about these parameters?

As Carlos said, the argument for the ST_Geometry constructor is a character literal.

Question marks in a character literal are simply question mark characters. Question marks in a character literal are not considered to be parameter markers.

You have two choices:

1. Compose the ST_Geometry constructor argument as a Java String within your Java application, as Carlos showed. (No parameter markers in this solution.)

2. Or use question mark parameters within a SQL character concatenation expression, as shown below:

String sql = "SELECT <columns....> "

+ " FROM <table/views ....> "

+ "WHERE a.GEOA_SPATIAL.ST_SPHERICALDISTANCE (NEW ST_GEOMETRY('POINT (' || ? || ' ' || ? || ')')) < ?";

Junior Supporter

Re: Why is TeraData JDBC complaining about these parameters?

Yeah!

It seems to me that choice num 2 is the good one (more elegant, and possibly more performant).

I said I'm not a java expert ;-)

Cheers.

Carlos.

t_t
Fan

Re: Why is TeraData JDBC complaining about these parameters?

Hey that works great using the 2nd method where I concatenate the values in using the SQL || syntax - thanks!

How can I accomplish this with the SET QUERY BANDING query?

I have a query like this:

SET QUERY_BAND ='PROXYUSER="+username+ "; ApplicationName="+appName+";' FOR SESSION;

Which works fine but I want to parametetize this...

I tried:

SET QUERY_BAND ='PROXYUSER=?; ApplicationName=?;' FOR SESSION;

with setStrings on the two parms which didnt work.

I even tried:

SET QUERY_BAND =? FOR SESSION;

where I did setString(1, "PROXYUSER="+username+ "; ApplicationName="+appName+";");

and that didnt even work.

What can I do here?

Teradata Employee

Re: Why is TeraData JDBC complaining about these parameters?

SET QUERY_BAND FOR SESSION is a DDL command, so a question-mark parameter marker is not permitted.

In contrast, SET QUERY_BAND FOR TRANSACTION is a DML command, so a question-mark parameter marker can be used.

The following sections of the Teradata JDBC Driver User Guide discuss how to use SET QUERY_BAND with the Teradata JDBC Driver:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGEGBBAA


http://developer.teradata.com/doc/connectivity/jdbc/ reference/current/ jdbcug_chapter_2.html#2403_2...


Also, sample program T21900JD.java illustrates how to use SET QUERY_BAND:


http://developer.teradata.com/doc/connectivity/jdbc/reference/current/samplePrograms.html

t_t
Fan

Re: Why is TeraData JDBC complaining about these parameters?

So can I just use for TRANSACTION instead of FOR SESSION? The way I use it is per a request on a web service, I set the query banding, run my query and send the response and as far as I'm concerned I am done. I reset the query banding and wait for next response which could be same guy or someone else.

So maybe I should be using FOR TRANSACTION?

Teradata Employee

Re: Why is TeraData JDBC complaining about these parameters?

Yes, you should consider the use of SET QUERY_BAND FOR TRANSACTION, which was designed for use cases like yours -- servlet requests, Web Service requests, etc.