PSQL 9.1 Problem 1

[zemp]

VB6 SP6
ADO 2.8
PSQL 9.1
winXP SP2 or win2000 SP4

I have an app that worked well with Pervasive 2000i through V8.7 with no major syntax changes required. I have upgraded my test machine to PSQL 9.1 and receive the following error.

-2147217887 Multiple Step operation generated errors. Check each status value.

This error seems to occur ONLY with disconnected recordsets and occurs anytime I try to assign a value to any field (data type is correct). Is there something I need to add to the recordset definition? Do disconnected recordsets work with PSQL 9.1?

Here is my recordset code

   Set rsDetail = New ADODB.Recordset
   rsDetail.CursorLocation = adUseClient
   l_strSQl = "SELECT * FROM Details WHERE (QuoteID =" & pQuoteID & ") ORDER BY Line"
   rsDetail.Open l_strSQl, qmCONN, adOpenStatic, adLockOptimistic, adCmdText
   rsDetail.ActiveConnection = Nothing

The .Addnew method works and the .recordcount increases but as soon as I assign a value the error occurs. As on the code below.

   rsDetail.Fields("Line") = 1

Any information would be great.

zemp

 

[mirtheil]

Are you using ODBC or OLEDB?
If it's ODBC,have you run an ODBC trace to see if there's a Pervasive error?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[zemp]

I am using ODBC. I will try the trace.

zemp

 

[zemp]

I could not see any meaniful errors in the ODBC trace. Especailly in the section of the program that was receiving the errors.

Any other suggestion?

zemp

 

[mirtheil]

Not without more error information. Specifically the PSQL error. Were there any errors in the trace?
I just did a qick test with your code (changed the SQL to "SELECT * FROM CLASS WHERE (ID > 10) ORDER BY ID"
and then set one of the fields after opening the recordset and it worked for me (no error).
My full code is:

Dim qmCONN As ADODB.Connection
Set qmCONN = New ADODB.Connection
qmCONN.ConnectionString = "DSN=DEMODATA"
qmCONN.Open
Dim rsDetail As ADODB.Recordset
Set rsDetail = New ADODB.Recordset
   rsDetail.CursorLocation = adUseClient
   l_strSQl = "SELECT * FROM class WHERE (id > 10) ORDER BY ID"
   rsDetail.Open l_strSQl, qmCONN, adOpenStatic, adLockOptimistic, adCmdText
   rsDetail.ActiveConnection = Nothing
   rsDetail.Fields("Name") = "MS"
   MsgBox "Done"

I'm using PSQL 9.1 NT Server Engine locally.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[zemp]

Problem Resolved.

After some testing I found that I could not edit the records in PCC with the same SQL statement in the first post.

SELECT * FROM Details WHERE (QuoteID =" & pQuoteID & ") ORDER BY Line

The PCC was locking the results as 'read only'. Why I am not sure.

It might have something to do with the composite primary key of the table (QuoteID and Line - both INTEGER). If I tried the SQL statement as this..

SELECT * FROM Details WHERE (QuoteID =" & pQuoteID & ") ORDER BY QuoteID

the results are not set as 'read only'.

So I tried using both fields in the order by clause like this,

SELECT * FROM Details WHERE (QuoteID =" & pQuoteID & ") ORDER BY QuoteID, Line

and the result set was not locked as 'read only'.

After making the adjustment in the VB Code the error was gone and everything was working properly.

One more note, in the PCC if the order by uses any other field the result set is locked as 'read only'. So there may be some issue with the 9.1 and order by clause with a composite primary key or maybe it might be with how my table is set up.

Hopefully this might save someone the headaches it caused me.

zemp

 

[zemp]

One other thing, that may be a factor, the field 'QuoteID' is a foreign key as well as a member of the composite primary key.

zemp

 

[Linda84]

If the engine builds a temp table to accomplish the ordering, the result set would be read-only. If it uses an existing index, it will be editable.

Linda
Pervasive Software

 

[zemp]

I tried creating an index on the 'Line' field only. Still didn't work. But I may not have created the index properly.

So far I don't find the 9.1 PCC very user friendly. That might just be the learning curve talking.

zemp

 

[Linda84]

The engine will optimize on either the Order By or the restrictions in the WHERE clause on a single table query like your examples. The choice depends on various factors the engine uses to decide which index will be more optimal for producing the result set. Using an index matching the Order By means the entire table has to be scanned and checked against the conditions. But, using the index on the restrictions means a temp table has to be built to satisfy the ordering. So, depending on the number of records in the table, the indexes available, and the anticipated size of the result set, the engine may choose one index over another.

Linda
Pervasive Software

 

[zemp]

Let me try to understand.

The index on my table is the CPK (both QuoteID and Line).

You mentioned in your first post that if the engine builds a temp table the results will be read only. So far so good.

In your second post you mentioned that "using an index on the restrictions (Where clause) means a temp table has to be built". Since the where clause is QuoteID (part of the index) and it builds a temp table and since a temp table produces a read only result set then the results in the PCC should be read only for the following SQL,

SELECT * FROM Details WHERE (QuoteID =" & pQuoteID & ") ORDER BY QuoteID, Line

But it is not. I can edit those results.

Am I misunderstanding you? If so can you please clarify?

I have not found this problem with other single tables, that have only one field as the primary key, as long as I add the correct index (field in the order by).






zemp

 

[Linda84]

In this example, the engine is choosing to optimize on the QuoteID+Line index, which can be utilized for the Order By AND for part of the restriction because you have the QuoteID column specified in both places. So, it won't need to create a temp table.

In your read-only example: SELECT * FROM Details WHERE (QuoteID =" & pQuoteID & ") ORDER BY Line
the engine was using the index on QuoteID+Line and using it to optimize the restriction. Then it was building a temp table on the resulting rows to get the proper ordering by the Line column.

The fact that the index also has Line as the second index segment doesn't come into play because the ordering is specified to be on Line, not QuoteID+Line. When you changed the query to order by QuoteID+Line, then it could use the index for optimizing both the ordering and restriction.

Linda
Pervasive Software

 

[zemp]

That make sense now. I think I got it.

Thanks for your time and explanation.


zemp