Solved

How to return a non-read only recordset from AS400.

Posted on 2002-06-24
24
612 Views
Last Modified: 2012-08-13
I have the following versions:
AS400 – V5R1
VB – 6.0 SP 5
Client Access Express – Version 4 Release 4

Purpose: I am calling a stored procedures on the AS400 and sending two Parameters.  The AS400 returns a recordset correctly, except that it is “Read-Only”.  

My Problem: I cannot use the recorset.sort or edit any data in VB.

What I am Looking for:  I want the recordset to be released from the AS400 so I can change the data.  I do not want to change the data on the AS400.  I want the recordset to be “marshaled” (if that is the correct use of the term) so I can manipulate the data in VB.

I have the following Code:

Public Sub GetRecordSet as adodb.recordset

Dim ADOcn As ADODB.Connection
Dim ADOcmd As ADODB.Command
Dim p1 As ADODB.Parameter
Dim p2 As ADODB.Parameter

Set ADOcn = New ADODB.Connection
ADOcn.CursorLocation = adUseClient
ADOcn.Open "Provider=IBMDA400;Data source=OURSYS";" & GetUserName & ", & GetPassword

Set ADOcmd = New ADODB.Command
Set ADOcmd.ActiveConnection = ADOcn
ADOcmd.CommandText ="{Call QGPL.GPC7060SP(?,?)”
ADOcmd.CommandType = adCmdText
ADOcmd.Prepared = True

Set p1 = New ADODB.Parameter
p1.Type = adVariant
p1.Direction = adParamInputOutput
p1.Value = “DALLAS”
ADOcmd.Parameters.Append p1

Set p2 = New ADODB.Parameter
P2.Type = adVariant
P2.Direction = adParamInputOutput
P2.Value = “RED”
ADOcmd.Parameters.Append p2

Set GetRecordSet = New ADODB.Recordset
GetRecordSet.CursorLocation = adUseServer
GetRecordSet.CursorType = adOpenKeyset
GetRecordSet.LockType = adLockBatchOptimistic
Set GetRecordSet = ADOcmd.Execute

Set ADOcmd = nothing
Set ADOcn = nothing

Exit sub


0
Comment
Question by:darold_rudolph
  • 9
  • 7
  • 4
  • +4
24 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7104680
Creating a disconnected recordset?
0
 

Author Comment

by:darold_rudolph
ID: 7104697
Yes, ineffect create a disconnected recordset with the data from the as400.
0
 
LVL 5

Expert Comment

by:bob_online
ID: 7104722
after executing, try

Set GetRecordSet.ActiveConnection = Nothing

You probably need to switch the cursor to the client before opening the recordset.
0
 

Author Comment

by:darold_rudolph
ID: 7104864
Thanks, Bob_online but not yet.

I tried both:

GetRecordSet.CursorLocation = adUseClient
and
GetRecordset.curSorLocation = adUseServer

Along with:

GetRecordSet.LockType = adLockOptimistic
and
GetRecordSet.LockType = adLockBatchOptimistic


I do not get any error executing through the code.  But using all of the above examples I get the same error when I try to change any value in the recordset.

[
Runtime error "3251"

Current Recordset doe snot support updating.  This may be a limitiation of the Provider, or the selected locktype.
]

Note: V5R1 is suppose to now support the disconnected recordset.

0
 

Author Comment

by:darold_rudolph
ID: 7104899
Thanks, Bob_online but not yet.

I tried both:

GetRecordSet.CursorLocation = adUseClient
and
GetRecordset.curSorLocation = adUseServer

Along with:

GetRecordSet.LockType = adLockOptimistic
and
GetRecordSet.LockType = adLockBatchOptimistic


I do not get any error executing through the code.  But using all of the above examples I get the same error when I try to change any value in the recordset.

[
Runtime error "3251"

Current Recordset doe snot support updating.  This may be a limitiation of the Provider, or the selected locktype.
]

Note: V5R1 is suppose to now support the disconnected recordset.

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 7106114
In your original code change this:
Set GetRecordSet = ADOcmd.Execute

To:
GetRecordSet.Open ADOcmd, ADOcn

If Not GetRecordSet.Supports("adUpdate") Then
   Msgbox "Sorry you are out of luck!"
End if

If you need more details, let me know,

Anthony
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7106131
A recordset returned by a stored proc can only be read-only. The SP is not an updatable data source.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7106280
>>A recordset returned by a stored proc can only be read-only<<

That is true only if you use the Execute method.  If you use the Recordset's Open method then you can get an updateable recordset (providing the provider supports it)

Anthony
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7106289
but how can a SP know which table to update?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7106325
As an example, I will use the NorthWind database in SQL Server and the sample Stored Procedure CustOrdersOrders. In case you don;t have it installed this is what the sp looks like:

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
     OrderDate,
     RequiredDate,
     ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID

The following code, will give you an updateable query:

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Password="""";Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=HOME-SERVER"
cn.Open

Set cmd = New ADODB.Command
With cmd
   Set .ActiveConnection = cn
   .CommandText = "CustOrdersOrders"
   .CommandType = adCmdStoredProc
   .Parameters.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
   .Parameters.Append cmd.CreateParameter("@CustomerID", adVarWChar, adParamInput, 5, "FRANK")
End With

Set rs = New ADODB.Recordset
With rs
   Set .Source = cmd
   .CursorType = adOpenDynamic
   .LockType = adLockOptimistic
   .Open Options:=adCmdStoredProc
   Debug.Print rs.Supports(adUpdate)
   .Close
End With
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing

Again, the SQL Server provider supports this, quite frankly I do not know if Client Access Express provider does as well.

Anthony
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7106338
Your SP is simply a SELECT statement. What if the SP is a cursor and do some process in it?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7106513
It does not make any difference, it was simply an example.   Try it for yourself and you will see what I mean.

Anthony
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7106531
Eric,

Here is an article from MSDN that makes your point:
PRB: Multiple Statements in Stored Procedure Causes Forward-Only/Read-Only Recordsets (Q246636)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246636&SD=MSKB&

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7106547
So to sumarize, if your Stored Procedure consists of a "simple" Select statement and (IMHO this is the biggest hurdle you face) your provider supports it, you should be able to get an updateable query.

Posting your SP from the AS400 may help.

For those reading this and wondering why you would implement a Stored Procedure with a select statement instead of doing a simple select, the answer is simple and can be summarize in one word:  Permissions.  Most shops do not (and should not) give you direct Select permission against the table (in fact users should not have any permissions of any type directly on the tables), but rather all actions should be implemented through Stored Procedures and User Defined Functions.

Anthony
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7106930
From the link you provided:

--Start--
CAUSE
SQL Server cannot provide schema information necessary for updates if the stored procedure contains more than just a SELECT statement.

RESOLUTION
You can use client-side cursors in order to get a read-only Static cursor, which allows scrolling and bookmarks. You will have to manage updates yourself.
--End--

It is written that you will have to manage updates yourself if the SP contains more than a SELECT. I have oftem see RPG400 code called as SP (which are more than simple SELECT) from VB. This is why I told that it is not an updatable datasource.

darold_rudolph, what kind of SP do you have?
0
 

Author Comment

by:darold_rudolph
ID: 7107772
Thanks for the great discussion.

Here is the results

I replaced my:
Set GetRecordSet = ADOcmd.Execute
statement with:
GetRecordSet.Open ADOcmd, ADOcn
with no other changes.

I got an error message:
Runtime error '3707'
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.

The SP is much more complicated than a simple SELECT, it contains full RPG code with if/thens.

I am currently copying the GetRecordset, field by field, record by record to new blank recordset, which allows me to edit the data in the recordset.  It is a slow process.

Also, the rumors were that a new feature of V5R1 would allow disconnected recordsets.

Thanks,
Darold
0
 

Author Comment

by:darold_rudolph
ID: 7107901
Thanks for the great discussion.

Here is the results

I replaced my:
Set GetRecordSet = ADOcmd.Execute
statement with:
GetRecordSet.Open ADOcmd, ADOcn
with no other changes.

I got an error message:
Runtime error '3707'
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.

The SP is much more complicated than a simple SELECT, it contains full RPG code with if/thens.

I am currently copying the GetRecordset, field by field, record by record to new blank recordset, which allows me to edit the data in the recordset.  It is a slow process.

Also, the rumors were that a new feature of V5R1 would allow disconnected recordsets.

Thanks,
Darold
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7107922
You are absolutely right on both counts, this line:
GetRecordSet.Open ADOcmd, ADOcn

Should have been, just:
GetRecordSet.Open ADOcmd

But also as you have discovered, this still may not be feasable in your case.

Anthony
0
 

Author Comment

by:darold_rudolph
ID: 7108619
Getting close?

I replaced:
Set GetRecordSet = New ADODB.Recordset
GetRecordSet.CursorLocation = adUseServer
GetRecordSet.CursorType = adOpenKeyset
GetRecordSet.LockType = adLockBatchOptimistic
Set GetRecordSet = ADOcmd.Execute

with:

Set GetRecordSet = New ADODB.Recordset
GetRecordSet.CursorLocation = adUseClient
GetRecordSet.CursorType = adOpenStatic
GetRecordSet.LockType = adLockOptimistic
GetRecordset.Open ADOcmd

I get the recordset and when I use the command:
  Call GetRecordset.collect("FIELD1") = "TEST"
       "FIELD1" is the named field and is 4 characters
it passes but the next statement:
  GetRecordset.movenext
does not.  I get the error:
Runtime error '-2147467259 (80004005)'
Insufficient base table information for updating or refreshing.

The statement:
  Call GetRecordset.Supports(adUpdate)
returns a true

Therefore it seems to let me write to the buffer of the recordset, but will not let me actually update the recordset.


A clarification on the SP.  The RPG program does use if/thens but it also creates a temporary table and uses a SELECT to return the recordsets to VB.

Also, sorry about the double comments. I hit refresh on my screen.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7108731
Should this not be:
GetRecordset.("FIELD1").Value = "TEST"
GetRecordset.Update  'Although this is probably implicit in the MoveNext
GetRecordset.MoveNext

In any case, I believe the error "Insufficient base table information for updating or refreshing", is related to the CursorLocation = adUseClient

So I believe you may be in a catch-22 situation.

Anthony
0
 

Author Comment

by:darold_rudolph
ID: 7108899
Again, thanks for your reponse.

The two are the same since .value is the default
GetRecordset.("FIELD1") = "TEST"
GetRecordset.("FIELD1").value = "TEST"

also, the update is implicit in the .movenext and I do get the same error with the .update as I do with the .movenext.

Having the cursor as adUseServer, returns a blank recordset, but it does have the correct fieldnames.

I would think at V5R1 should have an easy way of creating a disconnected recordsets!

Useless information:
I you watched one of the Discovery channels you would know that it should be catch-18.  The publisher already had another book out that year with an "18", therefore the book catch-18 was renamed to catch-22.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7109090
I realize that Value is the default property for the Fields collection.  It is just that you had used:
Call GetRecordset.collect("FIELD1") = "TEST"

Also, you may want to get in the habit of explicitly naming your default properties, as there are no default properties in .NET.

In any case, this is totally irrelevant to the case in hand <g> and I am not sure what else you can do.

Anthony
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7962953
Hi darold_rudolph,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept acperkins@devx's comment(s) as an answer.

darold_rudolph, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 5

Expert Comment

by:Netminder
ID: 8038935
Per recommendation, force-accepted.

Netminder
EE Admin
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now