AS400 Stored Procedures and CR 8.5

[GrayGhost]

I'm attempting to set up some side-by-side database performance tests on our AS400. We have Crystal Reports 8.5 and the 4.5.0 version of OS400.

I'm trying to get some base line timings for a report using PF's, LF's, a VIEW, and a STORED PROCEDURE.

It's the latter I'm having trouble with. I can create a stored procedure on the AS400, and can access it using the SQLCON32 utility. But if I try to build a report over it, I get an ODBC error telling me I need read/write access. Since I use the same DSN name (and user-id and password) for both, this puzzles me.

Has anyone out there used AS400 stored procedures with Crystal, and if so could you pass along your experiences?

Thanks!

 

[synapsevampire]

Perhaps the ODBC driver has a setting which allows for read only?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[GrayGhost]

Yes, I changed any/all ODBC connection settings that might have an effect. The error message I get is documented in the Crystal KB so I changed those settings, but still no joy. The wierd thing is, I can open the connection with the SQLCON32 utility that Crystal provides. Perhaps I'm using the wrong driver? I'm using the Client Access Express 4.3 32-bit driver. Maybe my AS400 has some settings that need tweaking - any ideas out there?

Thanks.

 

[mmills7228]

You might want to try Client Access Express v4 r5 with the latest service fix. I use this version to connect to data from an asp application. You might want to use the oledb provider. I am not an expert in the area by any means but i know that the oledb driver exposes more functionality like command line commands etc via ado.

 

[4eveRed]

Hi GrayGhost, did you ever resolve this problem? I'm getting the same error and any feedback would be appreciated.

Thanks

 

[GrayGhost]

Nope, I never resolved the issue. I'm taking mmils7228 advice and will see if I can get upgraded to Client Access Express 4.5. Hopefully that will help. I will pass along any results....

Thanks one and all.

 

[GrayGhost]

Update:

I think I may have another avenue to explore....
See this thread: thread149-100220.

Does anybody have experience with DDCS (Connect 5) on an AS400?

Thanks in advance.
GrayGhost

 

[4eveRed]

I'm running Client Access Express v4.5.

I understand v5 is now available. I will give it a try.

 

[GrayGhost]

Here's an update: once I upgraded to V5xx, things started working the way I expected them to. I'm not a stored procedure developer by trade, so I had to invest a little play time, but I did eventually get stored procedures to work. Moral of the story is get as current as you can....Thanks for all the feedback!!!

 

[krist]

Hi GrayGhost,
What a greattt newsss !! I have been strugling with this, many many times with no success.

May I know, do you finally make it into production stage ? Crystal Report on AS400 Stored Procedure ?

The V5xx thing, is it the Client Access version or OS400 version ?
Cam Client Access V5x work with OS400 v4xx ?

Thanks for the info,
Krist

 

[GrayGhost]

We have over 50 AS400's in our network. Most of them have OS's at V5R1, while our workstations have varying versions of Client Access (now called iSeries Access). I had immediate success with stored procedures and Crystal when I upgraded my PC's Client Access to V5R1. I upgraded iSeries Access to V5R2 recently and was able to work with V5R1 and V5R2 OS's with no problems in Crystal. By the way, I use Crystal Reports 8.5.

As I mentioned earlier in this thread, I hadn't developed stored procedures before but learned pretty quickly how to do it. Here are some hints:
a) Passing complex parameters to stored procedures from Crystal is a tricky proposition. Stored procedures don't accept parameter lists, for example. Keep your parameters simple if you can.
b) Error handling can be a challenge - be sure to research how it works in stored procedures.
c) Keep a copy of Graeme Birchall's "DB2 UDB V8.1 SQL Cookbook" handy (Graeme you are a wonder!!!) - look here to download a copy:

http://ourworld.compuserve.com/homepages/

graeme_birchall/HTM_COOK.HTM . This download has an unbelievable amount of real-world help for SQL users.
d) Verify that your ODBC connection has read/write or read/call turned on.
e) If you are getting the "connection is set to read-only" error when you try to preview your report, make sure you read the Crystal Knowledge Base article c2008274: there's an ODBC-related Windows registry setting that must be added in order for stored procedures to be called from a Crystal Report. The iSeries Access ODBC administration utility is supposed to maintain this setting but doesn't - IBM chose to implement it that way, ostensibly because it's a security exposure.
f) When testing stored procedures from the STRSQL green screen, remember to enter a COMMIT (or Rollback?) statement before you exit or you will leave your tables in a locked state.

Good luck!