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.
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 (' || ? || ' ' || ? || ')')) < ?";
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.
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?
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
Also, sample program T21900JD.java illustrates how to use SET QUERY_BAND:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/samplePrograms.html
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.
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.