×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PSQL 9.1 Problem

PSQL 9.1 Problem

PSQL 9.1 Problem

(OP)
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

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.

CODE

   rsDetail.Fields("Line") = 1



Any information would be great.

zemp

RE: PSQL 9.1 Problem

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

RE: PSQL 9.1 Problem

(OP)
I am using ODBC. I will try the trace.

zemp

RE: PSQL 9.1 Problem

(OP)
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

RE: PSQL 9.1 Problem

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:

CODE

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

RE: PSQL 9.1 Problem

(OP)
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.

CODE

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..

CODE

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,

CODE

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

RE: PSQL 9.1 Problem

(OP)
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

RE: PSQL 9.1 Problem

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

RE: PSQL 9.1 Problem

(OP)
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

RE: PSQL 9.1 Problem

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

RE: PSQL 9.1 Problem

(OP)
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,

CODE

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

RE: PSQL 9.1 Problem

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

RE: PSQL 9.1 Problem

(OP)
That make sense now. I think I got it.

Thanks for your time and explanation.

zemp

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close