ADO recordset problem

[neil]

Help!

Firstly, I'm a complete Progress novice so please excuse my igonrance - my background is mainly MS Sql Server and Oracle with Visual Basic frontends!

Well, here goes - I'm connecting to our Progress v9 database using Visual Basic 6.0 via an ADO connection that uses the Microsoft OLE DB Provider for ODBC datasources. Everything is fine and dandy, I can retrieve records etc but I can't update records. I can populate an ADO recordset with a record from the Progress DB but I can't update it. I get an ODBC error message that cryptically tells me I have a Syntax Error! This occurs when I issue the .update command against the recordset. If I issue an SQL UPDATE statement directly (in the form ADOConnection.Execute(SQL Statement) everything is fine and the record is duly updated. I can get away with using this methodology of updating the records but I really wanted to use an ADO recordset because I wanted to use proper record locking etc.

Does anybody have any experience with ADO, Merant ODBC and Visual Basic???? Any help would be *greatly* appreciated!

 

[Unregistered]

Oh I feel your pain. Moving from SQL and MS development to Progress is painful, especially dealing with ADO or any concept of ODBC connectivity, DSN etc. I have heard problems occur with Progress fieldss that contain word index.

Its a jump back and they'll be some limitations.

 

[neil]

Sample VB Code

Here's some sample VB code to establish an ADO connection to Progress. I've had to set up a DSN because I couldn't get a DSNless connection to work.

Dim ADOConnection as ADODB.Connection
Dim sConnect as String

sConnect="" & _
"Provider=MSDASQL;" & _
"DSN=MyProgressDSN;" & _
"UID=ProgressUserName;" & _
"PWD=ProgressPassword"

Set ADOConnection=new ADODB.Connection
With ADOConnection
.ConnectionString=sConnect
.Open
End With

I'm using the Merant 3.6 ODBC driver. When you set the ODBC DSN up you need to set an Isolation Level which is implemented when you read records. If you want to update the Progress database you'll need to set the Isolation Level of your DSN to 'READ COMMITTED'. If you want just read-only access to the Progress data then set the Isolation Level to 'READ UNCOMMITTED'.

When using an ADODB.Recordset you cannot issue the ADO Update command ie

ADORecordset("Column_Name")="New Value"
ADORecordset.Update

I've had to read the record I want to update into a locked ADO recordset and then create an SQL update command eg

sSql="UPDATE Table SET column_name=new value"
Then release the locked ADO record and immediately issue the SQL update command eg

ADORecordset.Close
ADOConnection.Execute(sSql)

This method isn't ideal but nothing else seems to work. I've also noticed when issuing SQL against the Progress database within VB you need to enclose the Progress column names in double quotes eg SELECT * FROM Table WHERE "column-name"='Value'

This code should work using VBScript in an ASP page but you'll need to modify it slightly ie use the Server.Create("ADODB.Connection") instead of set ADOConnection=new ADODB.Connection.

Hope this helps...Progress seems to be very very quirky and proper OLEDB would be nice. If Oracle can do it why not Progress?

 

[Garyk]

I found many bugs in the Progress/Merant 3.6 driver. Mainly when trying to create or update records. The errors were rarely helful. You can go into the control panel and turn on logging. The messages in the log file tell you much more than the ones returned to VB. Because of all the bugs, I was forced to switch to another method. Merant has a product Sequel Link ADO which has a Progress server component. I just switched to this right before the hollidays. So far all the bugs that I was seeing with the Progress ODBC work with the Sequel Link ADO. Same VB program, just switched data sources! The scariest part of the Progress driver was it's inconsistency. Some times it would work and other times it would fail. Makes debugging impossible.

 

[neil]

-----------------------------------------------------------------------
Okay - here's how I set the connection object up
-----------------------------------------------------------------------

Dim oConn As ADODB.Connection
Dim sConnectString As String

Set oConn = New ADODB.Connection

sConnectString = "" & _
"DSN=myDSN;" & _
"PROVIDER=MSDASQL;" & _
"UID=<db username>;" & _
"PWD=<db password>;" & _
With oConn
.ConnectionString = sConnectString
.Open
End With

Note - I'm using the Merant 3.60 ODBC driver and SQL92 at the Progress end. In the advanced tab of the driver
settings in ODBC manager you may need to check the default isolation level. Read Uncommitted means you can't
update records (read only!). Read Committed means any records read are exclusively locked when read meaning you can
update then.

-----------------------------------------------------------------------
How to read records using the above connection object
-----------------------------------------------------------------------

When reading records you need to put double quotes around the Progress column names eg
SELECT "column name" FROM owner.tablename. I've had to write a function in VB to take
a progress column name (passed as a string) and put double quotes around it. It goes like
this:

Function ProCol(inColumnName as string) As String
proCol=chr(34) & inColumnName & chr(34)
End Function

This means I can do this:

dim oRS as Adodb.Recordset
Dim sSql as string

set oRs=New ADODB.Recordset
sSql="SELECT " & proCol("columnName") & " FROM TableOwner.TableName"
'note - the sSql string now reads SELECT "columnname" FROM TableOwner.TableName

set oRs=oConn.execute(sSql)



Hope this helps and please, if anybody wants to add to this please do!

 

[Garyk]

Neil,

I am doing OK with connecting and updating Progress via Merant.
However there are some major bugs IMHO.
Here is a sample question that I posted in an ADO forum

I tried to optimize my select statement and wound up changing records
that I did not expect to change.
Is this a BUG of an ADO FEATURE???

#1) sql_cmd = "SELECT * FROM time_log WHERE time_log.emp_id = 11"
#2) sql_cmd = "SELECT stop_time FROM time_log WHERE time_log.emp_id =
11"
#3) sql_cmd = "SELECT stop_time,emp_id FROM time_log WHERE
time_log.emp_id = 11"

prod_time.CursorType = adOpenStatic
prod_time.LockType = adLockPessimistic
prod_time.Open sql_cmd, conn_tc, , , adCmdText

If Not prod_time.EOF Then
rc = prod_time.RecordCount
prod_time!stop_time = "Test"
prod_time.Update
prod_time.Close
End If

a) In both cases (sql_cmd 1 & 2) the record count = 2
b) Running the program using sql_cmd #1 updates 1 record.
c) Running the program using sql_cmd #2 updates ALL records in the
database!!!
d) Running the program using sql_cmd #3 updates 2 record.
e) Running with sql_cmd #1 Then sql_cmd #2 only has the one record
revised!!


In my mind, I am not looping through the recordset so I only expect
one record to be updated. In any event, if the recordcount = 2, then
why would ALL the records in the database be updated?
This is all very counter-intuitive.

Another question: Do I need emp_id in the SELECT if the only reference
to it is the WHERE in the SELECT statement?

---------------------------------
The above happens with Merant ODBC and Sequel Link ADO.

 

[seraphina]

Help me!!!... T.T

1) DBServer
- OS : AIX 4.3
- Progress DB 9.1B

2) WebServer
- Windows 2000 Server
- Client Networking Setup
- Merant 3.7 ODBC Setup


3) ODBC Driver
- Merant 3.7 ODBC Driver

4) ASP Source

<object runat=server progid=ADODB.Connection id=conn></object>
<object runat=server progid=ADODB.Recordset id=rs></object>
<%
sConnect = "Provider=MSDASQL;DSN=TEST;UID=;PWD=" 
conn.ConnectionString=sConnect 
conn.Open
 
sql = "select * from pt_mstr"
rs.Open sql, conn, 1
Response.Write "GoodsCd&nbsp;GoodsNm"
while not rs.eof
   Response.Write rs("pt_part") & "&nbsp;" & rs("pt_desc1") & "<br>"
   rs.movenext
wend
rs.close
set rs = nothing
%>

5) Error Message

conn.Open Error...

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[MERANT][ODBC PROGRESS driver]Optional feature not implemented.

/test.asp, line 6

 

[seraphina]

Re: Help me!!!... T.T

Originally posted by seraphina
1) DBServer
- OS : AIX 4.3
- Progress DB 9.1B

2) WebServer
- Windows 2000 Server
- Client Networking Setup
- Merant 3.7 ODBC Setup


3) ODBC Driver
- Merant 3.7 ODBC Driver

4) ASP Source
<object runat=server progid=ADODB.Connection id=conn></object>
<object runat=server progid=ADODB.Recordset id=rs></object>
<%
sConnect = "Provider=MSDASQL;DSN=TEST;UID=;PWD="
conn.ConnectionString=sConnect
conn.Open

sql = "select * from pt_mstr"
rs.Open sql, conn, 1
Response.Write "GoodsCd&nbsp;GoodsNm"
while not rs.eof
Response.Write rs("pt_part") & "&nbsp;" & rs("pt_desc1") & "<br>"
rs.movenext
wend
rs.close
set rs = nothing
%>


5) Error Message

conn.Open Error...

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[MERANT][ODBC PROGRESS driver]Optional feature not implemented.

/test.asp, line 6

 

[neil]

I can't see the sample ASP script that you posted. Can you repost it please? Also, have you established that you can connect to your Progress DB using ODBC?

Regards

 

[NeHe]

Error Message

I receive the exact same error message as you do. I have tried everything humanly possible to resolve this issue, but have not found a solution.

The only documentation I have found in regards to failed ASP connections said to make sure the following environment variables were set in SYSTEM

DLC
IDLC
PROMSGS
IPROMSGS
PROOIBRK
PROOIDRV

On my system, all of these are set and I still can't connect to progress with ASP.

The Test connection works every time with the ODBC driver... and I have flaky connections to the progress database using the 3.6 driver with Microsoft Access...

A working ASP connection would be GREATLY appreciated...

 

[neil]

Hi Nehe / Seraphina,

Can you post the connection string you are using to attempt the
connection to the Progress database? It may help shed some light on your problem.

I would also like to point out that Progress has been the worst behaved database that I've ever tried to interface to using ODBC and/or ADO....the stuff of nightmares indeed.

Regards