Solved

Problem with a LIKE clause  with a "*" inside in VB.Net accessing a DB2/400 table

Posted on 2016-10-07
7
92 Views
Last Modified: 2016-11-04
Hi

I get an error with a SQL statement getting data in a DB2/400 database when the WHERE clause contains a LIKE clause where the content of the LIKE includes the star character, "*", and this in a VB.Net application.
When my SELECT statement looks like this:

SELECT ... FROM MyTable WHERE Field1 LIKE 'ABC* %'

i.e. when the content of the like clause includes a star character.  The content of the LIKE clause is actually the value of  a field from an other table, so I do not control the presence or absence of the star character.

I get the error: "Error in Like operator: the string pattern 'ABC* %' is invalid."

The connection is done over IBM's own ODBC driver for the iSeries.

I *guess* it's because the star character is the wild card character in the Microsoft world, and the ODBC driver gets confused with 2 wild characters, maybe ? This is not an IBM DB2 error message, therefore probably a Microsoft error message.

Has anybody had this combination and knows how to avoid the error ?

Thanks for help on this
Bernard
0
Comment
Question by:bthouin
7 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 41833720
Please show your code.

Not a fix but a workaround: Field1 LIKE 'ABC* %'  is functionally equivalent to LEFT(Field1,4) = 'ABC* '
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41833721
Since you are using ODBC you could do a test by using Ms Access(if you know/have) to establish connection to your DB2 and perform the query (pass through)
0
 
LVL 1

Author Comment

by:bthouin
ID: 41833770
@Gary
My code:

Dim dsCT As New DataSet
Dim selRow as DataRow()
selRow = dsCT.Tables("MyTable").Select("Fild1 = 'Bla' " & _
                                                                              "AND Field2 LIKE '" & MyTextWithStar & " %' " & _
                                                                              "AND Field3 = 'Blo'")

Open in new window


if MyTextWithStar contains a "*", I get the error. If there is no star, it works fine.

@John
I frankly don't see how Access is going to help. I'm developing also with Access and VBA, and I am accessing the same tables in the DB2/400 with Access, so that's not a problem to try, but what's the goal ?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 13

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 250 total points
ID: 41833832
Can you please check if this applies to your situation .
0
 
LVL 27

Accepted Solution

by:
tliotta earned 250 total points
ID: 41833926
John Tsioumpris essentially has what could be the answer. You should try enclosing the asterisk in square brackets. However, that probably requires validating any MyTextWithStar value to see if it contains an asterisk (or a percent sign, "%", since both have special meaning in a LIKE clause in your case).

You'll want to try replacing every embedded instance of "*" (or "%") with "[*]" (or "[%]") in MyTextWithStar before concatenating it into your LIKE clause.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41833929
There is probably an issue with in between characters (the * char)  thats why i pointed the Ms Article in order to be precise...
0
 
LVL 1

Author Comment

by:bthouin
ID: 41836772
Ah, OK, I can easily enclose the * in square brackets as I already detect it and replace it with nothing for the moment to avoid the error. Let's see what happens then.

And to be honest, the MS article is so longwinded and precise that even if I had looked at it (how should I know that I should search for data column expressiion ?..), I would have given up looking long before finding the one reference to the bracket stuff...

Thanks, and I'll revert as soon as I can test the change.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question