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);
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.
I'm not an expert in java/JDBC but :
The single quotes are considered LITERALS. You need to construct the string with concatenations.
String sql =
"SELECT <columns....> "
" FROM <table/views ....> "
"WHERE a.GEOA_SPATIAL.ST_SPHERICALDISTANCE (NEW ST_GEOMETRY('POINT ("+
")')) < "+
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 (' || ? || ' ' || ? || ')')) < ?";
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 ;-)
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...
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?
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:
Also, sample program T21900JD.java illustrates how to use SET QUERY_BAND:
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?
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.