Using a PERCENT SIGN or SYMBOL in ADO MySQL query using LIKE

emulsified

I have a form that uses the MySQL ODBC driver v3.51 via Microsoft ActiveX Data Objects 2.8 using ADO to lookup and display information in a text box for my items on my Item card using the Item."No. 2" (my UPC barcode number)

All is well with the implementation except when I try to build a query using LIKE '%845670123562%'

My Query is like this:

MySQLQueryString := 'SELECT * FROM overstock WHERE `UPC` LIKE %845670123562%';

How do I pass the % sign/symbol in the C/AL string using ADORecSet.Open(MySQLQueryString,ADOConnection,OpenMethod,LockMethod); when I cannot get the % symbol into the C/AL string. How do I get the % (percent) symbol/sign into the C/AL string so that it gets passed into ADORecSet.Open correctly?

I tried using MESSAGE(MySQLQueryString); to see the output of my constructed query string but it chops off a bunch of numbers and the % symbol doesn't show up.

I get this from MESSAGE: SELECT * FROM overstock WHERE `UPC` LIKE 845670123562 (the % signs go missing)

I then receive a generic message: "This message is for C/AL programmers: The call to member Open failed. Microsoft OLE DB Provider for ODBC Drivers returned the following message: ODBC driver does not support the requested properties."

If I leave out the % symbols it works and acts just like as if I did `UPC`= 845670123562 but I need to account for the fact that sometimes the other database has a preceding zero in front of the UPC thus why I want to use LIKE '%845670123562%'

](*,) I've searched and searched and cannot find the answer to this.

Can anyone lend a hand here? :?

ara3n

Add a global text constant set the value to %

Text constant Value
Percent %


Use it in your code

MySQLQueryString := 'SELECT * FROM overstock WHERE `UPC` LIKE ' + Percent + '845670123562' + Percent;

emulsified

Thanks finally an answer to the magical % character issue.

I actually ended up rewriting my query to read:

MySQLQueryString := 'SELECT * FROM overstock WHERE `UPC` = '845670123562' OR `UPC` = '0' + '845670123562';

For some reason an EAN13 or UPC when scanned adds a zero to the beginning so I had to ensure my bases were covered if someone actually has 845670123562 or 0845670123562 in my NAV field I was using.

Thanks a bunch I will probably change my code and also use your solution in future coding.

Thank you.

emulsified

UPDATE - I had to finally use a LIKE %% query and had some problems but they are now solved!

I have tried using the % sign in my query string via a TEXT CONSTANT called Percent but that still does not work.
I kept getting ODBC driver errors about not being supported. The MESSAGE box I used to display my query string shows how the Percent TEXT CONSTANT was breaking the string.

I have solved my problem by creating a STORED PROCEDURE in the MySQL database and simply passing the UPC string to it as a variable using the MySQL statement like this:

MySQLString := 'CALL GetUPCRecords(upc)';

This way the actual SELECT statement with the LIKE %upc% portion takes place in MySQL and just returns the records as would normally happen if I did the ODBC ADO call from C/AL with a SELECT statement.

Stored Procedures are my new trick and I'm converting everything I use in Navision as ODBC ADO calls to those accordingly. They also run much faster.

Thanks for the help.

ferrysb

Hi All,

I'm also trying to build query using percent symbol like above.
Is there anyway beside using stored procedure ?
I also tried using text constant with % as value, but can't worked.

I want to build a query like
SELECT * FROM TableName WERE Field1 LIKE '%abcd%'

I tried using, which Percent is text constant (%), also can not worked :

Statement1 :=
'SELECT * FROM PEPCI.V_POSI_MEMBER_PROFILE WHERE EMAIL LIKE ''' + Percent + 'abcd' + Percent + '''';

Please advise...

Thanks,
Ferry

emulsified

Unfortunately the percent (%) symbol in Navision is a very special character. I too have tried all of the methods you have to no avail. The stored procedure is actually faster anyway and was the perfect solution to that problem.

Here is my stored procedure:

Stored Procedure Name: GetUPCRecords

BEGIN
DECLARE varUPC VARCHAR(255);
SET varUPC = CONCAT('%',findUPC,'%');
SELECT *
FROM overstock
WHERE upc LIKE varUPC;
END


You call the MySQL procedure from Navision just like you build and call your MySQL statements.

Example: Query := "CALL GetUPCRecords('12345')";

Where "12345" is what I want to search for in the MySQL database table. MySQL interprets this call as " SELECT * FROM overstock WHERE upc LIKE '%12345%' ".
You will want to replace the "12345" with whatever you want to query. I don't have an example but I know in my C/AL code in Navision I am using STRSUBSTNO to fill in that value for me.

Good luck.

ferrysb

Thanks for your reply.

I have problem creating the stored procedure, because I can't touch their DB, and client won't do it for me. And also I'm connecting to Oracle DB, not sure also if it's same with SQL...

So, stored procedure may be my very very last option ....

Please advise if anyone have idea about this.

Many thanks for your help,
Ferry