Oracle AddInParameter with Like % Wildcards

Topics: Data Access Application Block
May 3, 2007 at 6:18 PM
I'm connecting to an Oracle 10g database through the DAAB 3.0 and have ran into an issue that I cannot reproduce in SQLPlus. The purpose of the code is to query our database and find columns (I cut it down to birthdate for ease of code readibility) where an individual (student in this case) has a wildcard match. So, if they put in "Smi" for the last name variable, it'd return Smith, Smiths, Smithy, etc. The problem lies in the % wildcards OR missing single quotes around each. I've attempted every permutation that I can come up with and continue to get an ORA-00911 error. I cannot come up with any code samples that use LIKE and variables at the same time...

 
Database db = DatabaseFactory.CreateDatabase("Oracle_SIS");
string sql = "SELECT id, first_name, last_name, birth_date FROM " + Views.MasterRoster + " WHERE last_name LIKE %:lastName% AND first_name LIKE %:firstName%";
 
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, ":lastName", DbType.String, lastName);
db.AddInParameter(cmd, ":firstName", DbType.String, firstName);
DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
 
StudentCollection studentList = new StudentCollection();
foreach (DataRow studentRow in dt.Rows)
{
     studentList.Add(BuildStudentRecord(studentRow));
}
 
return studentList;
 

So, my questions:

1. Any ideas on the proper syntax to pass between the DAAB and Oracle?

2. Somewhat OT: Is there a way to see the EXECUTED SQL statement that is passed directly to the parser? I simply see the value of my 'sql' variable when I look at the command object...

Thanks in advance!

- David

David Longnecker
Web Developer
http://blog.tiredstudent.com
May 3, 2007 at 8:00 PM
There are a couple of problems with your code.

Database db = DatabaseFactory.CreateDatabase("Oracle_SIS");
string sql = "SELECT id, first_name, last_name, birth_date FROM " + Views.MasterRoster + " WHERE last_name LIKE :lastName AND first_name LIKE :firstName";
 
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "lastName", DbType.String, string.Format("%{0}%", lastName));
db.AddInParameter(cmd, "firstName", DbType.String, string.Format("%{0}%", firstName));
DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
 
StudentCollection studentList = new StudentCollection();
foreach (DataRow studentRow in dt.Rows)
{
     studentList.Add(BuildStudentRecord(studentRow));
}
 
return studentList;

1. The percent sign wildcards go around the values, not the parameters.
2. The colons are unnecessary in the AddInParameter line. They are added for you by the DAAB.
3. You didn't post the stored proc code, but you may have to watch your variable types to make sure you are using the correct DbType. VARCHAR2, for example, maps to DbType.AnsiString, while NVARCHAR2 maps to DbType.String.

As far as spying on the executed SQL statement, I presume you are talking about seeing the SQL with the parameters replaced by their values. Although I believe it could be done, I don't know of any existing facility in the EntLib to spit that out. So you'd have to write your own add-in.

HTH

__________________________________________
Matthew Noonan
EasyObjects.NET -- The O/RM for the Enterprise Library
May 4, 2007 at 2:24 PM
Matthew-

Thanks for the response. You're quite right (and now I'm going back and tweaking some other code too), the correct DbType was AnsiString, not String; however, that nor the Parameter changes seem to fix the issue; it seems to be getting hung up on the fact there are no single quotes around the string values.

I did a test injecting the information directly into the SQL statement:

string sql = "SELECT id, first_name, last_name, birth_date, " +
"WHERE UPPER(first_name) LIKE UPPER(*'%*" + firstName + "*%'*) AND UPPER(last_name) LIKE UPPER(*'%*" + lastName + "*%'*)";

That works like a champ (I added the UPPERs just to ensure case wasn't an issue), so I modified the AddInParameter to:

db.AddInParameter(cmd, "lastName", DbType.AnsiString, string.Format("'%{0}%'", lastName));

Unfortunately, it doesn't work. It's not throwing any errors; it simply isn't returning any query results. I'll dink around and see if I can find a good way to expose the executed sql statement... the joys of odd quirks; I'm sure it's something simple in the syntax.

Oh, and one thing: this isn't hitting a stored procedure (our accounts are query only; we cannot create stored procedures on the Oracle box--it's a boxed product); the only query is that which is specified in the SQL string.

TIA!

-dl
May 4, 2007 at 3:39 PM

Oh, and one thing: this isn't hitting a stored procedure (our accounts are query only; we cannot create stored procedures on the Oracle box--it's a boxed product); the only query is that which is specified in the SQL string.

Yes, I got ahead of myself there. I was thinking you needed to check the parameter datatypes against the procedure datatypes.

I'm not sure why your query isn't working, it should have worked as I posted it (once you corrected the datatypes). I'd hate for you to get away from parameterized queries, because the alternative opens you up to SQL injection attacks.
May 4, 2007 at 3:43 PM
Hello,

I can only speak from MSSQL.

It does seem that you have a problem with the quotes.
I would try to set your paramvalue to %value% (right now you have tried it with the quotes around it). Normally when you pass your values as parameters and it's a string, the qoutes should be handled. This means that you don't have to do it explicitly.

Anyhow, I have not tested this, but I think that is the problem

So
db.AddInParameter(cmd, "lastName", DbType.AnsiString, string.Format("'%{0}%'", lastName));
Should be
db.AddInParameter(cmd, "lastName", DbType.AnsiString, string.Format("%{0}%", lastName));

Best regards,
Ike
May 4, 2007 at 6:31 PM
It is true that the quotes should not surround the parameter value, but just for the record, the quotes were not included in my response either.
May 4, 2007 at 6:48 PM
Ike-

Yes, I added the quotes in for testing; just trying to find a solution. Leaving the quotes out (as Matthew noted) doesn't seem to fix the problem (which is why I was interested in seeing the parser level SQL code being passed).

For the record, you are correct, Ike, from a MSSQL POV, this is non-point. I can take the database, make a copy of a set of rows on MSSQL and run the same query and it works like a champ. It seems to be something arcane with Oracle and querying string values. I removed the LIKE and tried for exact comparison with ='s, and it still fails. /boggle.

I'll keep dinking with it; as you note, I hate to move from using the parameters...

May 4, 2007 at 7:23 PM
Could it be a problem with case sensitivity? Or localization settings? Just reaching here...

It's hard to provide a solution without access to the database or the code. Maybe you could post a CREATE TABLE statement and I could run some tests here.
May 4, 2007 at 8:32 PM
I can provide the DESC for those fields I'm looking at.

ID                                          NUMBER(9)                                                                                                                                                                                     
LAST_NAME                            VARCHAR2(30)                                                                                                                                                                                  
FIRST_NAME                           VARCHAR2(30)                                                                                                                                                                                  
BIRTH_DATE                            DATE                                                                                                                                                                                          

I don't have a CREATE TABLE statement (it's a materialized view anyway) as the actual access to the DDL isn't available to me. The base is setup to Western 8-bit (not Unicode), but AnsiString should take care of that...

Regarding case sensitivity, I've been converting everything using UPPER just incase. As I said earlier, the injected SQL (that I posted prior) works without a hitch.

I ran a few tests and I cannot query, using AddInParameter, any string value on the Oracle side. Numbers, dates, boolean--not a problem. Strings don't seem to return ANYTHING... even when making a query as simple as:

string sql = "SELECT * FROM table WHERE last_name = :lastName";
db.AddInParameter(cmd, "lastName", DbType.AnsiString, "Smith");

Running that query against the table raw ( SELECT * FROM table WHERE last_name = 'Smith' ) returns thousands of records.

MSSQL doesn't have any issues, but the lack of single quotes around the string value seems to really be throwing Oracle off. I'm not sure if that's EntLib's issue of not putting them in when it sends it to the parser, or if there's a syntax issue.

Truly boggled.
May 4, 2007 at 9:35 PM
I'm not an Oracle guy, so I can't promise you any miracle solutions ;-), but the fact that your query only fails on strings is probably a clue. Just to make sure I understand you correctly, does this code also fail?

Database db = DatabaseFactory.CreateDatabase("Oracle_SIS");
string sql = "SELECT id, first_name, last_name, birth_date FROM " + Views.MasterRoster + 
        " WHERE UPPER(last_name) LIKE UPPER(:lastName) AND UPPER(first_name) LIKE UPPER(:firstName)";
 
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "lastName", DbType.String, string.Format("%{0}%", lastName));
db.AddInParameter(cmd, "firstName", DbType.String, string.Format("%{0}%", firstName));
DataTable dt = db.ExecuteDataSet(cmd).Tables[0];

The quotes should not be an issue, I have never had to add them in my Oracle access code, so unless there is something in the connection string or configuration info that would make them required? Or perhaps inadvertently turning on case sensitivity? (again, not an Oracle guy (except when forced!)) I do have one app setup under 9i which has been running for over a year without change, but the configuration pretty much just the basics (i.e. nothing special was done).

After that, the only thing I can think of that would cause this behavior is permissions. If you are logging into SQL-Plus under a different account than what the query is running under, then perhaps the data cannot be accessed under the second account because of permissions.

After that, ya got me! :)
May 4, 2007 at 11:13 PM
Matthew-

Yeah, that snippet of code fails when executed...

The connection string that the application is using is just snipped from the TNSNAMES.ORA file that powers my SQL Developer and SQLPlus applications. I'll break the connection strings down and see, but there isn't anything (that I can think of) that is setting any environmental variables on connection. Everything is using the exact same account (we have a general purpose query account); and I can copy that SQL string, paste it into the SQLPlus window, replace :lastName with, for example, '%longnecker%', and :firstName with '%%' (emulating a blank value) and it works like a champ.

If I leave the single quotes OUT, it does fail in SQLPlus, but returns an error (invalid character)--the EntLib is simply returning an empty DataSet object.

And yes, after battling this for the past few days, I'll gladly go back and hug my SQL Servers.

Thanks all and if/when I find something, I'll post it up.

-dl