SQL SELECT For Informix DB
I have the following code in an app that needs to do a SELECT on an Informix DB. I know the data is there as I can link the Inofrmix table to an MSAccess DB. The following code connects and I get an open status on the cnn1 but when I issue the SELECT I do not get an open. Is there a better way to run the SELECT or what is wrong with this?
Thanks
'
Dim cnn1 As New ADODB.Connection
Dim cnn_ssfactor_ar_altname_Tb As New ADODB.Recordset
strConnect = "DRIVER={INTERSOLV 3.10 32-BIT INFORMIX 9};" & _
"DATABASE=/factor/culp/factor;" & _
"HOST=sco;" & _
"SERV=sqlexec;" & _
"SRVR=sco;" & _
"PRO=onsoctcp;" & _
"UID=ssfactor;" & _
"PWD=menus;"
'MsgBox "Connection: " & strConnect
With cnn1
.ConnectionTimeout = 600
.CursorLocation = adUseServer
.ConnectionString = strConnect
.Properties("Prompt") = adPromptNever
.Open
End With
If cnn1.State = adStateOpen Then
strSQL = "SELECT * " & _
"FROM ssfactor_ar_altname " & _
"WHERE an_customer = " & Val(SavCustomerNumber)
cnn_ssfactor_ar_altname_Tb.Open strSQL, cnn1, adOpenStatic, adLockOptimistic
If cnn_ssfactor_ar_altname_Tb.State = adStateOpen Then
If cnn_ssfactor_ar_altname_Tb.EOF = False Then
[1555 byte] By [
Don] at [2007-11-11 7:35:51]
# 1 Re: SQL SELECT For Informix DB
Is there a reason why you're checking the State property of the Recordset? This isn't really necessary and unless there is a runtime error when you execute the query you can go ahead and check for EOF to determine whether any rows were returned.
# 2 Re: SQL SELECT For Informix DB
I changed the SELECT to this
strSQL = "SELECT * " & _
"FROM ssfactor_ar_altname "
cnn_ssfactor_ar_altname_Tb.Open strSQL, cnn1, adOpenKeyset, adLockOptimistic
to be sure the WHERE was not an issue. I took out the .State check and when I step thru I see this error...
Runtime error 3704
Operation is not allowed when the object is closed.
Which is why I did the .State check. If I mouse over the cnn1.State it still shows it is open.
Don at 2007-11-11 17:27:40 >
# 3 Re: SQL SELECT For Informix DB
Since there is a System DSN defined how can I make the connection without the strConnect?
Don at 2007-11-11 17:28:49 >
# 4 Re: SQL SELECT For Informix DB
Try changing the CursorLocation for the Connection object to adUseClient. I don't see any problems with the code so it may be that the driver doesn't support a server side cursor (which is the default).
Whether a DSN is used or not, a connection string is still required.
# 5 Re: SQL SELECT For Informix DB
I get an error "Identifier length exceeds the maximum allowed by this version of the server." with the following. I can connect to the DB (or at least no erros and the state = open). But I can not get the table to open and again the MSA DB I have that has linked tables works fine.
Dim cnn1 As New ADODB.Connection
Dim rst As New ADODB.Recordset
strConnect = "DRIVER={INFORMIX 3.32 32 BIT};" & _
"DATABASE=/factor/culp/factor;" & _
"HOST=sco;" & _
"SERV=sqlexec;" & _
"SRVR=sco;" & _
"PRO=onsoctcp;" & _
"UID=ssfactor;" & _
"PWD=menus;"
With cnn1
.ConnectionTimeout = 600
.CursorLocation = adUseClient
.ConnectionString = strConnect
.Properties("Prompt") = adPromptNever
.Open
End With
'
'with DSN
'cnn1.Open "CulpPetroleum", "ssfactor", "menus"
'
If cnn1.State = adStateOpen Then
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockReadOnly
strSQL = "SELECT * FROM ssfactor_ar_altname"
rst.Open strSQL, cnn1
Do Until rst.EOF
rst.MoveNext
Loop
rst.Close
End If
Don at 2007-11-11 17:30:45 >
# 6 Re: SQL SELECT For Informix DB
I don't see a problem with the code. Can you successfully query any other tables? I'm thinking there is an issue with the table name length or a column name. What version of Informix are you using?
# 7 Re: SQL SELECT For Informix DB
Thanks for helping with this. I got it corrected about 15 mins ago. It turns out that when linking via MSAccess the table name is full length like ssfactor_ar_altname but when accessing it directly with the connection and SELECT I had to use only the ar_altname. Something about how Informix controls users and the table I think.
Now my problem is afer I installed the driver and I can access the Informix data I can not hit my SQL server any more. The error is "Unable to load communication module. Driver has not been installed correctly." The code which has worked for a long time is as follows:
strConnect = "Provider=SQLOLEDB;" & _
"Data Source=" & Sav_SLD_Server & ";" & _
"Initial Catalog=SMARTLynx_V501;" & _
"User Id=SMARTLogixDBUser;" & _
"Password=Sm@rt!;"
'MsgBox "Connection: " & strConnect
cnn.Open strConnect
How do I re-install the SQL Server driver?
Don at 2007-11-11 17:32:47 >
# 8 Re: SQL SELECT For Informix DB
There are a few possible causes for this problem. First is that you don't have sufficient permissions to access the database engine files. The following article should help identify whether this is the case (you can probably ignore the part about ASP):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306216
You may also want to try re-installing or repairing the MDAC installation.
http://www.macropool.com/en/download/mdac_xp_sp2.html
# 9 Re: SQL SELECT For Informix DB
Will look at those. I have administrator rights so that should not be an issue. The PC is running Win2KPro SP4. I have been searching trying to find how to re-install the SQL Server 2000 Client but can not find anything that has the souce for it. I even reinstalled SP4 for Win2KPro but that did not help. I can not get the ODBC driver to work. Get an error code 126.
Thanks
Don at 2007-11-11 17:34:55 >
# 10 Re: SQL SELECT For Informix DB
Ok...I am dead in the water. None of apps will run in VB6 since I can not connect to the SQL Server which is on another box. I have re-installed the MDAC 2.6 and 2.6 SDK which did not help. Do I need to re-install VB6?
Don at 2007-11-11 17:35:55 >
# 11 Re: SQL SELECT For Informix DB
Here is a good one. I am now able to create a System DSN to the SQL DB on the other server and when I changed my code from a DSN-Less connection to the DSN it connects. Is there something wrong with SQLOLEDB?
'old
'strConnect = "Provider=SQLOLEDB;Data Source=" & Sav_SLD_Server & ";Initial Catalog=SMARTLynx_V501;User Id=SMARTLogixDBUser;Password=Sm@rt!;"
'new
cnn.Open "Test", "SMARTLogixDBUser", "Sm@rt!"
Don at 2007-11-11 17:36:54 >
# 12 Re: SQL SELECT For Informix DB
A System DSN uses ODBC so that would be difference. It sounds like there is a problem with the OLEDB library.
You might want to try the different SQL Server Network Libraries to see if any of them work:
How To Set the SQL Server Network Library in an ADO Connection String (http://support.microsoft.com/kb/238949/EN-US/)
INF: Reading ODBC SQL Server Driver Network Messages (http://support.microsoft.com/kb/137634/)
