[RESOLVED] [VB6] Jet and Ace OLEDB text drivers ... case-sensitive or not?

[LaVolpe]

I was testing something and wasn't confident about the results. I assumed that SQL statements sent to OLEDB were case-insensitive. I've seen posts elsewhere & one in this forum (connection to DBASE III) that returns case-insensitive results.

I am not emotionally attached to the Jet or ACE OLEDB drivers; just comfortable with them.

So the question... Does anyone have any links that definitively says whether the OLEDB drivers are case-sensitive or not. If it depends on what is being connected to, I'm specifically interested in the text drivers, i.e., where sSrcPath in the following connection strings is a folder that contains csv files.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sSrcPath & ";Extended Properties='text;HDR=YES;FMT=CSVDelimited'"
"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & sSrcPath & ";Extended Properties='text;HDR=YES;FMT=CSVDelimited'"

Note that case-sensitivity is what I am after and the results seem to prove that, but don't know if it is specific to my pc or not. For example, a simple, one-column/field, csv with the following lines of text:

ClassName
ATM_Port
atm_Port
ATM_Port

I've tested several different queries and all return case-sensitive results. Those queries included using joins on the same 'table' and simple WHERE clauses similar to: ClassName Like '%atm_port%' Or ClassName = 'atm_port'

P.S. I know I can use, UCASE, LCASE, and StrComp() within a query passed to the OLEDB drivers, but prefer not to add that overhead if it isn't necessary.

[digitalShaman]

i do not know much about these text drivers but usually it depends on the database behind the OLEDB driver if its case sensitive or not. most can be configured as needed. so i would tend to think it is a feature of the text driver that OLEDB uses and you cant be sure that on a different pc a different driver/version behaves different. unless you find some official documentation which seems to be hard.

[LaVolpe]

... it is a feature of the text driver that OLEDB uses and you cant be sure that on a different pc a different driver/version behaves different.

That statement is my only concern. There were various versions of the drivers over time and it could very well be that case-sensitivity may have changed among them. Likewise, no guarantee that future versions won't change this feature.

I have had no luck landing on any sites that provide any definitive answers or whether there is a registry setting or connection property that acts as an override.

I'll keep this open, hoping someone does provide links. Otherwise, my options to ensure case-sensitivity (especially when JOINs are used) may be to use StrComp(binary compare) or ditch OLEDB, and csv, using SQL server temp table with the COLLATE statements in SQL, as that is available to me for this task.

[LaVolpe]

I'll mark this as resolved. Though I haven't found a way to override case-sensitivity within the ADO connection object, I do have a workaround

1. Determine if case-sensitivity is enforced. Use a query to test it, i.e., ... WHERE UCASE('a')='a'
If the recordset EOF is true, case-sensitive else not

2. Based on case-sensitivity adjust query strings as needed

FYI: On my pc, that test query returned case-sensitive for csv files, but not for Excel files, using the same OLEDB driver