We help IT Professionals succeed at work.
Get Started

MySQL Search syntax for Binary Strings from VFP

Mike Jacobs
Mike Jacobs asked
on
166 Views
Last Modified: 2020-03-15
Up to now in my Mysql searches, I've only performed searches for BIGINTs and INTs which behave as I expect. (with the minor quibble I raised a few days ago about why BIGINTs appear as string data not numeric)

Now I'm trying to perform a search for a binary string and I cannot get the syntax right

Sample Code

TARGHASH=HASH_SALTED
&& An SHA256 32 Byte value stored, locally, in a 32 byte VFP CHAR(BINARY) Field and remotely  in a 32 byte Mysql Varbinary column

I prepare a string to encode the SQSTRING I will use in the select statement thus:
success=SQLPREPARE(HND,SQSTRING,'ROWFOUND')

IF NOT CHECK_EXECUTE(SUCCESS,HND)  && a function which checks the syntax and displays any errors or goes on to execute it
      CANCEL
ENDIF
SUSPEND

with and without
CURSORSETPROP("MapBinary",.t.,0)

I have tried the following versions of SQSTRING :

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED="+TARGHASH
*anticipated this would work because the VFP field is CHAR(BINARY) and to the human eye the raw data in the vfp table looks identical to the raw data visible in the Mysql column

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED="+ENCODE(TARGHASH,'HEX')
*ENCODE IS A CALL TO A CHILCRYPT LIBRARY WHICH CONVERTS THE STRING TO HEX

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED=BINARY"+TARGHASH

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED=CONVERT("+TARGHASH+",BINARY)"

all are rejected with the standard mysql error

eg: "Connectivity error: .... check the manual to your mysql server version for the right syntax to use near ïÆÅ9q"¥Á5ºå;?      û#õ”úC

Can someone please put me out of my misery?
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE