SQL query is not working for Binary data!!

[David Joseph Bonnici]

Hi, I am writing an app in VB. I need SQL and so I am using Jet 4.0

In short, I am having trouble with an SQL query that involves data of
type BINARY in the WHERE statement.

Here's a resume. To show you my point pls download the small sample
database I have created in MSACCESS from
http://www.geocities.com/netspiri/db1.mdb

I have first created a small table named [sample_table] with the
following fields.

names of type text (50)
id of type longInteger
description of type text (50)

I have fired an alter statement to change the names data type to
binary, so as to able to do case sensistive queries.

The alter statement is the following
ALTER TABLE sample_table ALTER COLUMN names BINARY (100);
and till now its fine.

When I fire the query
SELECT [sample_table].* INTO [result] FROM [sample_table] WHERE
(names='MARY');

nothing is returned, and this is getting me really mad.

When then in SQL view I fire the query

SELECT sample_table.* INTO result FROM sample_table WHERE ([names]
LIKE 'MARY');

the function works. When I try to execute it in VB it however does not
work.

Has someone ever encountered this? Can someone help me? All sorts of
comments are appreciated.

Many Thanks and
Kind Regards
David

 

[Sylvain Lafontaine]

First, you should ask your question in a more appropriate newsgroup. This
one is about Access DAP project linked to SQL-Server and has nothing to do
with the syntax of SQL queries under Jet 4.0. Instead, you should ask you
question in the newsgroup m.p.access.queries. The newsgroup about MySQL is
also irrelevant.

Second, you should use a binary field to store string variables like names.
For example, your Select doesn't work because a Binary is not the same thing
as a varbinary. The name 'MARY' has a length of 4 caracters and hence, will
never equate a binary string of 100 cars. (There is also the problem of
code page and the translation from ASCII to UNICODE but I will left this
aside.)

The solution to your problem probably involve the use of something like the
option OPTION COMPARE BINARY but I cannot tell you the exact syntax to use
at the moment. You should find the required info in the proper newsgroup.

S. L.

 

[Sylvain Lafontaine]

Oups; I wanted to say: « ... you shouldn't use a binary field to store
string variables like names... ».

S. L.

First, you should ask your question in a more appropriate newsgroup. This
one is about Access DAP project linked to SQL-Server and has nothing to do
with the syntax of SQL queries under Jet 4.0. Instead, you should ask you
question in the newsgroup m.p.access.queries. The newsgroup about MySQL
is also irrelevant.

Second, you should use a binary field to store string variables like
names. For example, your Select doesn't work because a Binary is not the
same thing as a varbinary. The name 'MARY' has a length of 4 caracters
and hence, will never equate a binary string of 100 cars. (There is also
the problem of code page and the translation from ASCII to UNICODE but I
will left this aside.)

The solution to your problem probably involve the use of something like
the option OPTION COMPARE BINARY but I cannot tell you the exact syntax to
use at the moment. You should find the required info in the proper
newsgroup.

S. L.

Hi, I am writing an app in VB. I need SQL and so I am using Jet 4.0

In short, I am having trouble with an SQL query that involves data of
type BINARY in the WHERE statement.

Here's a resume. To show you my point pls download the small sample
database I have created in MSACCESS from
http://www.geocities.com/netspiri/db1.mdb

I have first created a small table named [sample_table] with the
following fields.

names of type text (50)
id of type longInteger
description of type text (50)

I have fired an alter statement to change the names data type to
binary, so as to able to do case sensistive queries.

The alter statement is the following
ALTER TABLE sample_table ALTER COLUMN names BINARY (100);
and till now its fine.

When I fire the query
SELECT [sample_table].* INTO [result] FROM [sample_table] WHERE
(names='MARY');

nothing is returned, and this is getting me really mad.

When then in SQL view I fire the query

SELECT sample_table.* INTO result FROM sample_table WHERE ([names]
LIKE 'MARY');

the function works. When I try to execute it in VB it however does not
work.

Has someone ever encountered this? Can someone help me? All sorts of
comments are appreciated.

Many Thanks and
Kind Regards
David