• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

Using LIKE in prepared statement...

Hi World!

I have a program that uses the IBM CLI library. In one of my routines, I create a prepared statement that looks something like this:

SQLCHAR *stmt = "SELECT * FROM MY_TABLE WHERE MY_FIELD LIKE ?";

I then bind the parameter using a string like this:

SQLCHAR *data = "stuff%";

The return set is empty....

But when I run the select statement from the command line in AIX like this:

db2 "SELECT * FROM MY_TABLE WHERE MY_FIELD LIKE 'stuff%'"

I get lots and lots of stuff...  all kinds of stuff that starts with "stuff"...

Any ideas what is so special about using "LIKE" and these prepared statement calls?

-- Bubba
0
bganoush
Asked:
bganoush
  • 5
  • 2
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
Does it have anything to do with the single-quotes that should be around 'stuff%'?  They're probably not in your bind variable.

-- Dave
0
 
bganoushAuthor Commented:

No, when you bind with a variable, you can't put the quotes in, the CLI takes care of doing that and to escape any special characters.

-- Bubba
0
 
bganoushAuthor Commented:

I wonder if it's escaping the "%" sign?

-- Bubba
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
bganoushAuthor Commented:

Ok, this is what I think may be happening.. and now my question has to change...

The field I am looking at is a "TIMESTAMP" and you can't do a "LIKE" on a timestamp. The reason the command line was working is that I was looking at another table that uses a CHARACTER field for the date, go figure...

So now my question is.... can I convert the timestamp to a CHARACTER before comparing it with a "LIKE"?

-- Bubba
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
the following works for me:

select *
from myTable
where char(myColumn) like '2005-02-09%';

HTH,
DaveSlash
0
 
bganoushAuthor Commented:

Um... I solved it...

-- Bubba
0
 
bganoushAuthor Commented:

Yeah, that's what I did...

Thanks

-- Bubba
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now