I'm trying to ENCODE/DECODE a field in a MySQL database table using
ODBC in a Visual Basic 6 program. The encode seems to work ok, but the
decode does not return the original string - it returns garbage
(neither the original string nor the encoded string).

If I use the same SQL statements in both VB and directly in the MySQL
Query Browser (shown below), the Query Browser does it correctly but
the VB/ODBC version does not decode correctly.

Any ideas at what I'm doing wrong here? Shouldn't the ODBC version of
this do the same thing as working directly in MySQL?

Here's the code that I'm using in VB:
--------------------------------------------------------------------------
Open the connection to the MySQL database:
Dim X$

X$ = "Provider=MSDASQL.1;Password=;Persist Security Info=True;"
X$ = X$ & "User ID=;Extended Properties=" & DQ & "DRIVER={MySQL ODBC
5.1 Driver};"
X$ = X$ & "DESC=;DATABASE=maindatabase;SERVER=localhost; "
X$ = X$ & "UID=root;PASSWORD=admin;PORT=;OPTION=16387;STMT=; " & DQ

MySQLConnection.Open X$

--------------------------------------------------------------------------
Use this to encode the value (as keyed-in to Text1.Text):

SQL$ = "UPDATE Table1 SET FieldX=ENCODE('" & Trim$(Text1.Text) &
"','abcd') WHERE Field1='something'"
MySQLConnection.Execute SQL$

--------------------------------------------------------------------------
And then, decode the value:

Dim rsX As New ADODB.Recordset
Dim SQL$

SQL$ = "SELECT DECODE(FieldX,'abcd') AS FieldX FROM Table1 WHERE
Field1='something'"

rsX.CursorLocation = adUseClient
rsX.Open SQL$, MySQLConnection, adOpenStatic, adLockOptimistic,
adCmdText
If rsX.RecordCount > 0 Then
Text1.Text = rsX!FieldX
End If
rsX.Close


================================================== =================
Here is the MySQL Query Browser version:

UPDATE Table1 SET FieldX=ENCODE('martin','abcd') WHERE
Field1='something'


SELECT DECODE(FieldX,'abcd') AS FieldX FROM Table1 WHERE
Field1='something'

In article <qlfcq5dvlbq1894n2kt9skn0ruodl5q1gt (AT) 4ax (DOT) com>, Martin <ironwoodcanyon (AT) gmail (DOT) com> wrote:
It should -- as long as you're sending the same query...
Note that if the above command fails, your program continues executing anyway
without indicating the failure.

Display the contents of SQL$ here to make sure it contains what you expect it
to contain.

Note that if the above command fails, your program continues executing anyway
without indicating the failure.

Ummmm.... don't you need to MySQLConnection.Execute that query before you try
to retrieve results from it?

On Sun, 21 Mar 2010 17:26:48 GMT, spambait (AT) milmac (DOT) com (Doug Miller)
wrote:

That's what I thought - but it isn't.

Understood - see more below

It does.

Understood - see more below

No. The .Open command is executed when the form is loaded and the
connection is kept open all the time until the form is unloaded.

There is no problem regarding the various commands being executed. I
have single-stepped through the program and checked / verified that
SQL statements are correct and that responses are valid.

I've also done the "encode" step in the VB program and then checked
the value in the table using the Query Browser. And, I've done the
opposite. In every situation, the only thing not working corrrectly is
the "decode" in the VB code.

I'm wondering if there's some kind of problem with the ODBC driver
that I'm using. I got it from the MySQL website. It's version 5.1 -
the latest thing, I believe.

On Sun, 21 Mar 2010 21:46:29 GMT, spambait (AT) milmac (DOT) com (Doug Miller)
wrote:

The execution of SQL$ is done by the next statement after your
comment. This one:
rsX.Open SQL$, MySQLConnection, adOpenStatic, adLockOptimistic,
adCmdText

If I have encoded "qwerty", it decodes to "???"
If I encode "abcde", it decodes to "??e"

In article <5mocq5112dsj341uk9g8bitc3n2bcvfc0h (AT) 4ax (DOT) com>, Martin <ironwoodcanyon (AT) gmail (DOT) com> wrote:
Yes, of course the connection is kept open. But simply placing a query string
into the variable SQL$ doesn't cause that query to be executed.
And what is the response from the query containing the DECODE?

Right, because you never pass that query to the connection.

I've done a little more experimenting with this problem and have found
that AES_ENCRYPT and AES_DECRYPT behave exactly the same way as ENCODE
and DECODE.

Can anyone give a clue as to what's happening here?

Thanks.

On 22 Mar, 16:09, Martin <ironwoodcan... (AT) gmail (DOT) com> wrote:
One question occurs to me. How are you viewing the output? Is it
possibly just a character encoding problem?

Have you tried inserting the value from the QB and reading it from VB
and vice versa? If so what are teh results?

The problem is due to VB using wide character strings and DECODE returning data as BINARY instead of the original character set of the input given to ENCODE.

With the default MySQL character set conversion settings VB string input is converted to latin1 or utf8 before passed into ENCODE and so DECODE will return a one-byte-per-character representation of the original input. As DECODE results are represented using the BINARY character set no conversion happens on the way back to the client application though, and so data gets mangled as one-byte-per-charater data is stored into a two-bytes-per-character VB string.

Solutions: use "CONVERT(DECODE(...) USING utf8))" to convert the BINARY result into utf8 (or your character set of choice)