Link to home
Create AccountLog in
Avatar of pumbaaca
pumbaacaFlag for United States of America

asked on

Showing more columns when querying VB.NET ODBC connection to Progress DB

I am trying to put together a simple query to a Progress Database using ODBC (Progress OpenEdge 10.2B Driver).  I can get the query to return rows of one column in the table, but any attempt to return more than one column leads to what appears to be a looped query that never returns.

I confirmed that the two test columns I am using do exist in the database. If I run the VBNET program for either of the columns by itself the query works. Put both columns in the query and the query never returns.

This is my first attempt to access the Progress DB.  I am sure I am just missing something simple, but cannot seem to find anything in EE nor on the Progress support groups to get me past this issue.

Attached is an excerpt of the program I am trying to run.


Friend Function GetTrucksTest() As Exception
        Dim cn1 As Odbc.OdbcConnection
        Dim cmd As Odbc.OdbcCommand
        Dim DRTrucks As Odbc.OdbcDataReader
        Dim strSQL As String

        Try

            strSQL = "SELECT truck.'truck-id', " & _
                            "truck.'description' " & _
                     "FROM SOCF_PRD.PUB.truck "

            strSQL = strSQL.Replace("'", Chr(34))

            cn1 = New Odbc.OdbcConnection(DB1ConnString)
            cn1.Open()
            cmd = New Odbc.OdbcCommand(strSQL, cn1)
            DRTrucks = cmd.ExecuteReader

            While DRTrucks.Read
                WriteToTextFile("modTest", "GetTrucksTest", "Truck is " & _
                        DRTrucks.GetValue(0) & "-" & DRTrucks.GetValue(1))
            End While

            cn1.Close()
            cmd = Nothing
            cn1 = Nothing

            Return Nothing

        Catch ex As Exception
            WriteToTextFile("DBCalls", "GetTrucks", ex.Message)
            Return ex
        End Try

    End Function

Open in new window

Avatar of Deepak Lakkad
Deepak Lakkad
Flag of India image

Hi,

I have tried your code using SQL Express - There is no error in the code.

However, i suggest you to make following changes

1. remove "-" from the field 'truck-id'.
2. use "DRTrucks.GetValue(0)" as "DRTrucks.GetValue(truckID).Tostring" where truckID is new name of "truck-id
3. same way do change for DRTrucks.GetValue(1).

May be this will solve your problem

- Deepak Lakkad
First of all using an Exception as a result type is most cases not appropriate. Then when you want to test something, use a Unit Test, see the attached snippet.


mfG
--> stefan <--
Imports System.Text

<TestClass()>
Public Class UnitTest2

    <TestMethod()>
    Public Sub TestMethod1()

        Const CONNECTION_STRING As String = ".."
        Const SQL_SELECT As String = "SELECT truck.'truck-id', truck.'description' FROM SOCF_PRD.PUB.truck"

        Dim connection As Odbc.OdbcConnection
        Dim command As Odbc.OdbcCommand
        Dim reader As Odbc.OdbcDataReader
        Dim sql As String

        sql = SQL_SELECT.Replace("'", Chr(34))

        connection = New Odbc.OdbcConnection(CONNECTION_STRING)
        connection.Open()
        command = New Odbc.OdbcCommand(sql, connection)
        reader = command.ExecuteReader

        Assert.IsFalse(reader.IsClosed, "Recordset is closed.")
        Assert.IsTrue(reader.HasRows, "No records returned.")
        Assert.IsTrue(reader.FieldCount = 2, "Incorrect number of fields.")

        If reader.Read Then
            Debug.WriteLine(String.Format("Truck is {0}-{1}", reader.GetInt32(0), reader.GetString(1)))
        End If

        connection.Close()
        command = Nothing
        connection = Nothing

    End Sub

End Class

Open in new window

Avatar of pumbaaca

ASKER

Deepak - I cannot change the name of the field as the database is managed by a 3rd party vendor.

Stefan -

I had to revise the Assert codes you provided to what I was able to come up with below (new stuff for me...Thanks for this new approach!):
        Trace.Assert(Not DRTrucks.IsClosed, "Recordset is closed.")
        Trace.Assert(DRTrucks.HasRows, "No records returned.")
        Trace.Assert(DRTrucks.FieldCount = 2, "Incorrect number of fields.")

The program "hangs" on the Trace.Assert(DRTrucks.HasRows, "No records returned.") line...as if it never returns from the ODBC. I killed the program and then tried the following:
        Trace.Assert(Not DRTrucks.IsClosed, "Recordset is closed.")
        Trace.Assert(DRTrucks.FieldCount = 2, "Incorrect number of fields.")
        Trace.Assert(DRTrucks.HasRows, "No records returned.")

with the above assert lines, the program again "hangs" on the Trace.Assert(DRTrucks.HasRows, "No records returned.") line. I killed the program.  I then tried again (to be sure it still worked) to submit a single column query (i.e. dropped the 'description' column) and the program works as expected.

Additional thoughts?
Thanks
 
hi,

run your query string

SELECT truck."truck-id", truck."description" FROM SOCF_PRD.PUB.truck

Open in new window


in a Progress query tool. Does it run there?

Also try it without the table names in the field list:

SELECT "truck-id", "description" FROM SOCF_PRD.PUB.truck

Open in new window


Maybe it is a identifier problem (2-part in the field list, 3-part in the FROM clause). Also try it with a table alias if it is supported:

SELECT A."truck-id", A."description" FROM SOCF_PRD.PUB.truck A

Open in new window


Another test would to use field alias names, also only if supported:

SELECT A."truck-id" AS "idTruck", A."description" AS "Desc" FROM SOCF_PRD.PUB.truck A

Open in new window



mfG
--> stefan <--
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Stefan -

I tried the variations you suggested, all produced the same result (hangs in same place).

I am not familiar with an Progress query tool (may have one but I dont see it).  For giggles, I created a MS Access database link to the progress database and ran the query (converted to MS Query, of course) and the query ran fine in MS Access.

I then changed my test program to use OLEDB and this new MS Access database.  I get hung in the Command.ExecuteReader step.  So, I believe that I have been able to successfully test the query outside the odbc and oledb connections in vb.net.

any other thoughts?
Thanks!!
pcelba - I tried your additional suggested SQL command variants...same result.

I will compile the program and try it on the server where ODBC is also installed to see if I get the same result there.  And then I will try to reinstall the ODBC on my workstation again.

Thanks
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
No, the connection string has to be correct if the ONE-column query works.

BTW, Postgre <> Progress.
While preparing to reinstall the ODBC driver, I discovered the more pointed issue to my problem.  The ODBC connection is having an issue working remotely (I was working from home). When I got into the office this morning, the ODBC began behaving as I expected.  That's what I get for attempting to work from home over a long holiday weekend!!!

I awarded the points to pcelba and ste5an for their efforts and keeping me sane on this end! This was indeed a connection issue with the ODBC driver, which I will save for another day since this connection wll not be used remotely and I must do my work "in the office" going forward!  Thanks!