Description:
This issue occurs when connecting to a MYSQL database using ODBC driver version 3.51.6.0 through either ADO or DAO, with slightly different results. Though my example below is based on a temp table, the issue is the same with a regular table.
DAO (3.6)
When I execute a "select distinct trim(fieldname)" on a table where fieldname is a varchar, fieldname in all returned rows is padded with trailing spaces such that they all have the length of the longest row.
ADO (2.5)
When I execute a "select distinct trim(fieldname)" on a table where fieldname is a varchar, fieldname in all returned rows is not trimmed at all. If it is a varchar (100), all data passed to the ado recordset is padded to 100.
How to repeat:
3 queries to execute:
create temporary table acme_temporary.indGroups(grpid char(4), indgroup varchar(100))
insert into acme_temporary.indGroups values ('1010','Energy'),('1510','Materials'),('2010','Capital Goods'),('2020','Commercial Services & Supplies'),('2030','Transportation'),('2510','Automobiles & Components'),('2520','Consumer Durables & Apparel'),('2530','Consumer Services'),('2540','Media'),('2550','Retailing'),('3010','Food & Staples Retailing'),('3020','Food, Beverage & Tobacco'),('3030','Household & Personal Products'),('3510','Health Care Equipment & Services'),('3520','Pharmaceuticals & Biotechnology'),('4010','Banks'),('4020','Diversified Financials'),('4030','Insurance'),('4040','Real Estate'),('4510','Software & Services'),('4520','Technology Hardware & Equipment'),('4530','Semiconductors & Semiconductor Equipment'),('5010','Telecommunication Services'),('5510','Utilities')
select distinct trim(ig.indgroup) from acme_temporary.indGroups ig
VBA Code used in excel:
Public Sub GatherDataDAO()
Dim strSql As String ' duhh
Dim intCnt As Integer ' loop through sql statements
Dim intRow As Integer ' row counter
Dim wrkODBC As DAO.Workspace ' DAO workspace
Dim conPubs As DAO.Connection ' DAO connection
Dim qdfTemp As DAO.QueryDef ' DAO query
Dim rstTemp As DAO.Recordset ' table of data
On Error GoTo errme
' set up the database connection - acme_data
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "ssdev", "", dbUseODBC)
strSql = "ODBC;DSN=acme_data;DB=acme_data;SERVER=moad01.server.starmine.com;UID=webdev;PWD=bally1Hoo;PORT=;OPTION=514;STMT=;"
Set conPubs = wrkODBC.OpenConnection("Connection1", dbDriverNoPrompt, , strSql)
conPubs.QueryTimeout = 360
' get the data
With ThisWorkbook.Sheets("SQL")
.Select
For intCnt = 2 To .Range("E1").Value
strSql = .Cells(intCnt, 1).Value
' if 'work' in D, only execute
If .Cells(intCnt, 4).Value = "work" Then
conPubs.Execute strSql
Else
Set qdfTemp = conPubs.CreateQueryDef("", strSql)
Set rstTemp = qdfTemp.OpenRecordset(dbOpenSnapshot)
' print out the data
rstTemp.MoveFirst
intRow = 10
Do
.Cells(intRow, 2).Value = rstTemp.Fields(0).Value
.Cells(intRow, 3).Value = Len(rstTemp.Fields(0).Value)
intRow = intRow + 1
rstTemp.MoveNext
Loop While Not rstTemp.EOF
rstTemp.Close
End If
' disconnect the query
Set rstTemp = Nothing
Set qdfTemp = Nothing
Next intCnt
End With
conPubs.Close
exitme:
On Error Resume Next
Set rstTemp = Nothing
Set qdfTemp = Nothing
Set conPubs = Nothing
Set wrkODBC = Nothing
Exit Sub
errme:
MsgBox ("ERROR" & vbCrLf & Err.Number & " " & Err.Description), vbCritical, "ERROR!"
Err.Clear
GoTo exitme
End Sub
Public Sub GatherDataADO()
Dim adoconnect As ADODB.Connection
Dim adocommand As ADODB.Command
Dim adorecordset As ADODB.Recordset
Dim intCnt As Integer
Dim intRow As Integer
Dim strSql As String
On Error GoTo errme
Set adoconnect = New ADODB.Connection
adoconnect.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=moad01.server.starmine.com;DATABASE=acme_data;UID=webdev;PWD=bally1Hoo;OPTION=514"
' get the data
With ThisWorkbook.Sheets("SQL")
.Select
For intCnt = 2 To .Range("E1").Value
strSql = .Cells(intCnt, 1).Value
' if 'work' in D, only execute
If .Cells(intCnt, 4).Value = "work" Then
adoconnect.Execute strSql
Else
Set adocommand = New ADODB.Command
Set adocommand.ActiveConnection = adoconnect
adocommand.CommandText = strSql
Set adorecordset = New ADODB.Recordset
Set adorecordset = adocommand.Execute
adorecordset.MoveFirst
intRow = 10
Do
.Cells(intRow, 2).Value = adorecordset.Fields(0).Value
.Cells(intRow, 3).Value = Len(adorecordset.Fields(0).Value)
intRow = intRow + 1
adorecordset.MoveNext
Loop While Not adorecordset.EOF
adorecordset.Close
End If
' disconnect the query
Set adorecordset = Nothing
Set adocommand = Nothing
Next intCnt
End With
adoconnect.Close
exitme:
On Error Resume Next
Set adoconnect = Nothing
Set adocommand = Nothing
Set adorecordset = Nothing
Exit Sub
errme:
MsgBox ("ERROR" & vbCrLf & Err.Number & " " & Err.Description), vbCritical, "ERROR!"
Err.Clear
GoTo exitme
End Sub
Description: This issue occurs when connecting to a MYSQL database using ODBC driver version 3.51.6.0 through either ADO or DAO, with slightly different results. Though my example below is based on a temp table, the issue is the same with a regular table. DAO (3.6) When I execute a "select distinct trim(fieldname)" on a table where fieldname is a varchar, fieldname in all returned rows is padded with trailing spaces such that they all have the length of the longest row. ADO (2.5) When I execute a "select distinct trim(fieldname)" on a table where fieldname is a varchar, fieldname in all returned rows is not trimmed at all. If it is a varchar (100), all data passed to the ado recordset is padded to 100. How to repeat: 3 queries to execute: create temporary table acme_temporary.indGroups(grpid char(4), indgroup varchar(100)) insert into acme_temporary.indGroups values ('1010','Energy'),('1510','Materials'),('2010','Capital Goods'),('2020','Commercial Services & Supplies'),('2030','Transportation'),('2510','Automobiles & Components'),('2520','Consumer Durables & Apparel'),('2530','Consumer Services'),('2540','Media'),('2550','Retailing'),('3010','Food & Staples Retailing'),('3020','Food, Beverage & Tobacco'),('3030','Household & Personal Products'),('3510','Health Care Equipment & Services'),('3520','Pharmaceuticals & Biotechnology'),('4010','Banks'),('4020','Diversified Financials'),('4030','Insurance'),('4040','Real Estate'),('4510','Software & Services'),('4520','Technology Hardware & Equipment'),('4530','Semiconductors & Semiconductor Equipment'),('5010','Telecommunication Services'),('5510','Utilities') select distinct trim(ig.indgroup) from acme_temporary.indGroups ig VBA Code used in excel: Public Sub GatherDataDAO() Dim strSql As String ' duhh Dim intCnt As Integer ' loop through sql statements Dim intRow As Integer ' row counter Dim wrkODBC As DAO.Workspace ' DAO workspace Dim conPubs As DAO.Connection ' DAO connection Dim qdfTemp As DAO.QueryDef ' DAO query Dim rstTemp As DAO.Recordset ' table of data On Error GoTo errme ' set up the database connection - acme_data Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "ssdev", "", dbUseODBC) strSql = "ODBC;DSN=acme_data;DB=acme_data;SERVER=moad01.server.starmine.com;UID=webdev;PWD=bally1Hoo;PORT=;OPTION=514;STMT=;" Set conPubs = wrkODBC.OpenConnection("Connection1", dbDriverNoPrompt, , strSql) conPubs.QueryTimeout = 360 ' get the data With ThisWorkbook.Sheets("SQL") .Select For intCnt = 2 To .Range("E1").Value strSql = .Cells(intCnt, 1).Value ' if 'work' in D, only execute If .Cells(intCnt, 4).Value = "work" Then conPubs.Execute strSql Else Set qdfTemp = conPubs.CreateQueryDef("", strSql) Set rstTemp = qdfTemp.OpenRecordset(dbOpenSnapshot) ' print out the data rstTemp.MoveFirst intRow = 10 Do .Cells(intRow, 2).Value = rstTemp.Fields(0).Value .Cells(intRow, 3).Value = Len(rstTemp.Fields(0).Value) intRow = intRow + 1 rstTemp.MoveNext Loop While Not rstTemp.EOF rstTemp.Close End If ' disconnect the query Set rstTemp = Nothing Set qdfTemp = Nothing Next intCnt End With conPubs.Close exitme: On Error Resume Next Set rstTemp = Nothing Set qdfTemp = Nothing Set conPubs = Nothing Set wrkODBC = Nothing Exit Sub errme: MsgBox ("ERROR" & vbCrLf & Err.Number & " " & Err.Description), vbCritical, "ERROR!" Err.Clear GoTo exitme End Sub Public Sub GatherDataADO() Dim adoconnect As ADODB.Connection Dim adocommand As ADODB.Command Dim adorecordset As ADODB.Recordset Dim intCnt As Integer Dim intRow As Integer Dim strSql As String On Error GoTo errme Set adoconnect = New ADODB.Connection adoconnect.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=moad01.server.starmine.com;DATABASE=acme_data;UID=webdev;PWD=bally1Hoo;OPTION=514" ' get the data With ThisWorkbook.Sheets("SQL") .Select For intCnt = 2 To .Range("E1").Value strSql = .Cells(intCnt, 1).Value ' if 'work' in D, only execute If .Cells(intCnt, 4).Value = "work" Then adoconnect.Execute strSql Else Set adocommand = New ADODB.Command Set adocommand.ActiveConnection = adoconnect adocommand.CommandText = strSql Set adorecordset = New ADODB.Recordset Set adorecordset = adocommand.Execute adorecordset.MoveFirst intRow = 10 Do .Cells(intRow, 2).Value = adorecordset.Fields(0).Value .Cells(intRow, 3).Value = Len(adorecordset.Fields(0).Value) intRow = intRow + 1 adorecordset.MoveNext Loop While Not adorecordset.EOF adorecordset.Close End If ' disconnect the query Set adorecordset = Nothing Set adocommand = Nothing Next intCnt End With adoconnect.Close exitme: On Error Resume Next Set adoconnect = Nothing Set adocommand = Nothing Set adorecordset = Nothing Exit Sub errme: MsgBox ("ERROR" & vbCrLf & Err.Number & " " & Err.Description), vbCritical, "ERROR!" Err.Clear GoTo exitme End Sub