[ODBC-354] SELECT Query using VBA returning wrong results Created: 2022-02-24  Updated: 2022-05-16

Status: Open
Project: MariaDB Connector/ODBC
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Jehan Oré Assignee: Lawrin Novitsky
Resolution: Unresolved Votes: 0
Labels: ODBC, VBA, query
Environment:

Windows 10 - Microsoft 365


 Description   

Hi all,

I report a bug using ODBC connector through VBA. Assuming I have a table named 'companies' with 3 rows in there.
When doing this query:

sqlQuery = "SELECT DISTINCT idCompany" _
 
        & " FROM companies" _
 
rsDB.Open sqlQuery, conDB, adOpenDynamic

Then it returns the correct results:

idCompany
1
2
3

But when doing a query with more than one field, then

sqlQuery = "SELECT DISTINCT idCompany, name" _
 
        & " FROM companies" _
 
rsDB.Open sqlQuery, conDB, adOpenDynamic

The query returns this:

idCompany name
1 company 1
1 company 1
2 company 2

This means that any query with more than one field selected returns the first row twice, and doesn't show the last row. Even with more than 20+ rows in my table the result is the same...

Any idea of the origin of this bug?

Thanks a lot

– EDIT –
Here is the connection string used in VBA:

Set conDB = New ADODB.Connection
 
Set rsDB = New ADODB.recordSet
 
Set rsDBCol = New ADODB.recordSet
 
conDB.connectionString = "DRIVER={MariaDB ODBC 3.1 Driver};" _
 
    & "SERVER=server-1;" _
 
    & "DATABASE=db_pricing;" _
 
    & "PORT=3306" _
 
    & "UID=xuser;" _
 
    & "PWD=xxxxxxxxxxx;" _
 
    & "OPTION=3"
 
 
 
conDB.Open
 
'rsDB.CursorLocation = adUseServer
 
'rsDBCol.CursorLocation = adUseServer

I've figured out that the use of 'rsDB.CursorLocation=aduseServer' is the cause of the issue. When commenting these 2 lines, my requests seems to work well.

 Comments   
Comment by Jehan Oré [ 2022-02-25 ]

Actually figured out that conversely to other ODBC connections, I can't use 'recordset.cursorLocation=adUseServer', which relates to this issue...

Comment by Jehan Oré [ 2022-02-25 ]

Therefore, one should not specify any curcorLocation for a recordset, and then everything seems to work well.

I can't close the ticket :/

Comment by Lawrin Novitsky [ 2022-02-25 ]

Thank you for your report. I don't think it needs to be closed. This looks like pretty critical issue.

Comment by Jehan Oré [ 2022-02-25 ]

Actually, the solution seems not to come from removing the 'cursor' parameter of the recordset, but removing the 'adOpenDynamic' in the query.

With the 'adOpenDynamic', I have the issue, without it, it works perfectly.

Comment by Lawrin Novitsky [ 2022-04-25 ]

Could you please provide ODBC trace for the erroneous execution. I cannot repeat the problem so far, apparently I am doing something wrong or something important is different in my environment. Thank you in advance.

Comment by Lawrin Novitsky [ 2022-04-25 ]

Or maybe complete testcase code, i.e. also how the resultset is read and output, could also help. But odbc trace is preferable.

Comment by Lawrin Novitsky [ 2022-05-16 ]

Downgraded, as I could not repeat, and there is no feedback

Generated at Thu Feb 08 03:28:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.