SQL query question & recommendation?

[fangfoo]

I have a few books on SQL 2000 administration but they are light on working with queries. Can anyone recommend a good SQL query oriented book. Doesn't have to be MS centric jsut the language, Amazon had a few readers recommended but all were 2001/2002 printings and I imagine there might be something released more recently that is more up to date?

Until I can go get a book, how can I change this statement to have it return a broader range of data? I mean if I type in just a 's' I want all records with an 's' where it now returns no records unless it only finds an 's'. Thanks.


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Con1", "username", "password");
PreparedStatement stat = con.prepareStatement("select soStatus, soDate, asset, tenant, contactName, contactEmail from fritco where soStatus = ?");
stat.setString(1, tenant);
ResultSet result = stat.executeQuery();

[mmetzger]

It depends on what you mean, whether you want anything starting with an s, ending with an s, or has an s in it.

For just starting with - 's%'

Ending - '%s'

Containing '%s%'

[fangfoo]

Mmm I wasn't clear sorry, though that helps with another bit I was writing.

The code I wrote that goes way off the page is taking a user inputed variable (in this case soStatus) and looking in the DB for it.

What I want to do is have it return anything in that column that contains whatever soStatus is, whether an S or Sin or Sino Bar. Can I do that easily?

Could I do this (well it didn't and I tried a few variations)?

%soStatus%

Any other ideas? Thanks.

[fangfoo]

tried a like but it doesn't like the syntax.

played with:

SELECT stuff_here FROM cia
WHERE soStatus LIKE '%' = ?

[erickwidya]

i think what mmetzger means is
SELECT stuff_here FROM cia WHERE soStatus LIKE 's%' -- soStatus that begin with 's'

[fangfoo]

Yeah as I indicated the like didn't work. It works from a sql query window but not when executed with a variable passed from a form by a user to the jsp apge that executes the command.

I've taken to writing out the value of the variable that is getting passed to the perparedStatement for debugging and in theory it should work (plus I know exactly what is being sent); alas it doesn't it simply returns nothing.

I've tried concatinating the % signs to the variable value post receiving it from the form but that doesn't work either.

Whenever I substitute an exact match for the % it returns the correct record, I just can't get it to return all records, or a subset of all records that share the same few letters.

Any other ideas?

[srinika]

I think fangfoo is confused with the syntax of 'like' in a query.

Follow exactly what mmetzger & ericwidya has given.

fangfoo's syntax is :
SELECT stuff_here FROM cia WHERE soStatus LIKE '%' = ?

The correct Syntax is :

String pStr;
// variable letter from which thewords that u want to search begins with.
pStr = txtSearchText
// txtSearchText is the textbox where u key in the Serching letter(s)

In the prepared satement:
"SELECT stuff_here FROM cia WHERE soStatus LIKE '%" & pStr & "'"

[fangfoo]

thanks for all the replies, they were very helpful.

It doesn't work though I imagine it's more of a problem with the JSP interpreting the passed variable and not properly putting together the SQL query statement. I'll continue to tinker with it and post the final working code when I get it right.

Found this on Sun's java reference site and I think it might point in the final right direction:


PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

[srinika]

Create the query seperately and assign it to a String variable.

In the Prepared statement use that variable (SQL Query)

Better to check whether the created query, ie the content in the variable, is correct.

String pStr;
pStr = txtSearchText ;

String pSQLStr;
pSQLStr = "SELECT stuff_here FROM cia WHERE soStatus LIKE '%" & pStr & "'"

/* Check by means of a lable or some such, whether the SQL query is correct - Copy the label content & paste in Query analyzer and check for the results
*/

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection
("jdbc:odbc:Con1", "username", "password");
PreparedStatement stat = con.prepareStatement(pSQLStr);
stat.setString(1, tenant);
ResultSet result = stat.executeQuery();

[fangfoo]

Interesting idea. I'll give it a go. You still drop the ? which is kinda required for the preparedStatement to work but otherwise I see what you saying.

Is there also a way to just get all records from a column? I think I'd like to make the default search return everything unless the user chooses to add a filter criteria. Thanks.

doh!
SELECT stuff_here FROM fritco

[srinika]

For ur both concerns:

Create the SQL String as follows

if criteria1 has a value
{
s1 = "and field1 = criteria1 "
}

if criteria2 has a value
{
s2 = "and field2 = criteria2 "
}
......
.......

SQLStr = "SELECT stuff_here FROM fritco where 1 = ?" + s1 + s2 ....

[exterminator]

The basic trick is what srinika already stated when he/she told that you could run your code in a debug mode and then copy the prepared SQL query string (what is in the prepared statement, just before calling the .executeQuery() method) and run it in the query analyzer of the database. If that runs well, then the code would work fine and if it doesn't then may be you need to play with the language syntax a little bit. That's it.

Also, note that - putting a LIKE with anything other than "xyz%" (trailing wild characters only), for example "%xyx" or "%xyz%" will make the query fail to make use of any indexing that might have had been done on the column(s) mentioned in the WHERE conditions. This can easily affect performance in the wrong sense if the table(s) are containing huge amounts of data. On a side note - the same performance hit is obtained if you try using any scalar functions (for example - any string functions) on the columns in the WHERE clause provided the columns are indexed (be it clustered or non-clustered, obviously on Sybase or MS SQL server; Oracle has probably different kinds of indexes than these two and I am not sure if same effect is there on the efficiency of the select query). This is an important tip as far as query optimization goes. Hope this helps. Regards.

[fangfoo]

Finally got my head wrapped around all the comments. Thanks again.


<%
String selectX = request.getParameter("choice");
String pSQLStr = "SELECT asset, tenant FROM fritco WHERE asset ='";
...
ResultSet result = stmt.executeQuery(pSQLStr + selectX +"'");

[srinika]

To get ur desired result [as per ur original post ]
The following


<%
....
String pSQLStr = "SELECT asset, tenant FROM fritco WHERE asset ='";
....
ResultSet result = stmt.executeQuery(pSQLStr + selectX +"'");
....


should be changed to

<%
....
String pSQLStr = "SELECT asset, tenant FROM fritco WHERE asset Like '";
....
ResultSet result = stmt.executeQuery(pSQLStr + selectX +"%'");
....


:D