Sybase query returns blank/empty for char fields
-
For many years, I have been successfully using MS Access VBA to import data from a Sybase ASE server through direct queries. Several years ago, it was necessary to locate and install new drivers when I upgraded from XP to Win7 64-bit. All was working well until I got a new Win10 PC. I fought with installing the same drivers I used with the Win7 machine but was finally able to make a successful connection. The recordsets that are returned contain empty character fields. Numeric and date fields display fine. For example,
SELECT 123 FROM someTable
Returns: 123
SELECT '123' FROM someTable
Returns: I tried different values for the CharacterSet parameter in the connection string but it made no difference. I also googled... There was some information regarding empty strings for formatting purposes but none that I found that would explain the above results.I can't figure out why only char fields are affected. I would expect all or nothing if it were a driver issue but the same drivers work in Win7. Any thoughts??
-
For many years, I have been successfully using MS Access VBA to import data from a Sybase ASE server through direct queries. Several years ago, it was necessary to locate and install new drivers when I upgraded from XP to Win7 64-bit. All was working well until I got a new Win10 PC. I fought with installing the same drivers I used with the Win7 machine but was finally able to make a successful connection. The recordsets that are returned contain empty character fields. Numeric and date fields display fine. For example,
SELECT 123 FROM someTable
Returns: 123
SELECT '123' FROM someTable
Returns: I tried different values for the CharacterSet parameter in the connection string but it made no difference. I also googled... There was some information regarding empty strings for formatting purposes but none that I found that would explain the above results.I can't figure out why only char fields are affected. I would expect all or nothing if it were a driver issue but the same drivers work in Win7. Any thoughts??
Presuming you are not hiding/eating errors then it has nothing to do with connections nor general calling semantics. However it very likely has to do with HOW you are calling it which you did not specify. But I will note that in the first case I would expect a caller to be defined to expect a numeric value. While the second would require a caller to expect a textual value and probably a 'CHAR' type with a specific size (3 or greater) specified.
-
Presuming you are not hiding/eating errors then it has nothing to do with connections nor general calling semantics. However it very likely has to do with HOW you are calling it which you did not specify. But I will note that in the first case I would expect a caller to be defined to expect a numeric value. While the second would require a caller to expect a textual value and probably a 'CHAR' type with a specific size (3 or greater) specified.
The call for the Win 10 machine is below. This is the same as it is in the Win 7 machine. Sensitive information masked
Public Sub Doit()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim sSql As String
Dim sConnStr As StringsSql = "SELECT '123' FROM someTable" sConnStr = "Provider=ASEOLEDB.1;Password=\[password\];Persist Security Info=True;User ID=\[user\];Data Source=\[ip\_address\]:\[port\];Initial Catalog=xyz" sConnStr = Replace(sConnStr, "\[password\]", "\*\*\*\*\*\*") sConnStr = Replace(sConnStr, "\[user\]", "\*\*\*\*\*\*") sConnStr = Replace(sConnStr, "\[ip\_address\]", "\*\*\*\*\*\*\*") sConnStr = Replace(sConnStr, "\[port\]", "\*\*\*\*\*\*") cn.Open sConnStr rs.Open sSql, cn Debug.Print rs.GetStringEnd Sub
-
Presuming you are not hiding/eating errors then it has nothing to do with connections nor general calling semantics. However it very likely has to do with HOW you are calling it which you did not specify. But I will note that in the first case I would expect a caller to be defined to expect a numeric value. While the second would require a caller to expect a textual value and probably a 'CHAR' type with a specific size (3 or greater) specified.
Well, It appears as if it is a driver compatibility issue. I downloaded the Devart driver and string fields are returned as expected. Further research shows that there are newer drivers in the SDK but it is no longer available to common folks like myself. I suppose I will have to weigh the cost of a third party driver versus redesigning and distributing the app. Unless anyone knows an alternate location for ASE drivers compatible with Windows 10??
-
Presuming you are not hiding/eating errors then it has nothing to do with connections nor general calling semantics. However it very likely has to do with HOW you are calling it which you did not specify. But I will note that in the first case I would expect a caller to be defined to expect a numeric value. While the second would require a caller to expect a textual value and probably a 'CHAR' type with a specific size (3 or greater) specified.
This issue has been resolved. After days of searching, I found an evaluation version of SAP Adaptive Server Enterprise 16. This package contained updated drivers that work with Windows 10.