DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: SQL SELECT For Informix DB

  1. #1
    Join Date
    Jul 2004
    Posts
    143

    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

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jul 2004
    Posts
    143
    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.

  4. #4
    Join Date
    Jul 2004
    Posts
    143
    Since there is a System DSN defined how can I make the connection without the strConnect?

  5. #5
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  6. #6
    Join Date
    Jul 2004
    Posts
    143
    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

  7. #7
    Join Date
    Dec 2003
    Posts
    2,750
    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?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  8. #8
    Join Date
    Jul 2004
    Posts
    143
    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?

  9. #9
    Join Date
    Dec 2003
    Posts
    2,750
    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...;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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  10. #10
    Join Date
    Jul 2004
    Posts
    143
    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

  11. #11
    Join Date
    Jul 2004
    Posts
    143
    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?

  12. #12
    Join Date
    Jul 2004
    Posts
    143
    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!"

  13. #13
    Join Date
    Dec 2003
    Posts
    2,750
    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
    INF: Reading ODBC SQL Server Driver Network Messages
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. SQL: Select last records of the year
    By John in forum Database
    Replies: 4
    Last Post: 08-27-2003, 11:29 AM
  2. Multiple SQL insert from select box...
    By Bob Gibilaro in forum ASP.NET
    Replies: 2
    Last Post: 08-25-2002, 10:08 PM
  3. Replies: 2
    Last Post: 07-17-2002, 06:45 PM
  4. Faster SQL Code
    By Edwin in forum Database
    Replies: 2
    Last Post: 03-06-2002, 12:58 PM
  5. Replies: 0
    Last Post: 06-22-2000, 07:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center