Client Access ODBC To AS400 4

[Skittle]

This isn't really an RPG question but its the best place I can think of to get help on this one. I didn't get any help from the ODBC forum.

I have a VB program that reads a file on the AS400 via ODBC
using a connection string:-

objConn02.Open "Provider=IBMDA400Data
Source=99.99.99.99;", "myuser", "mypassword"

The trouble is that if I have not started Client Access and
signed on to a normal emulation screen, I get an error on the attempt to connect:-

Run Time Error '-2147217843' (80040e4d)
[IBM][Client Access Express ODBC Driver (32-bit)]
Communication link failure .comm rc=8270 - CWBSY0270 - User profile will be disabled with next invalid password, password length = 0, prompt mode = never, system ip address = 99.99.99.99".

If I start Client Access it works fine.
Is there somthing I need to do with Client Access in addition to creating the ODBC link?

Dazed and confused

 

[Mercury2]

Hi,
I think you came across this problem because you have to signon twice to be connected to the AS/400-iSeries.
Look up the cwblogon command. You can use this command before your VB program starts :
cwblogon AS400_IP /u userid /p password
You can put a shortcut with this as the target in your startup folder. It does mean the user name and password will be visible to someone who goes digging, and you will need to change the shortcut if you change your password on the AS/400.

But for more rigorous requirements, review these iSeries Access APIs:

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzaik/rzaikcommapilist.htm

especially those related to signon and userid/password. Visual Basic can use these APIs on the PC. It doesn't take a huge effort to create a decently secure and reliable infrastructure for userid/password management.
hth

 

[Skittle]

Thanks for your help.

Much appreciated.

Dazed and confused

 

[Skittle]

...as an additional thought. Does anybody know a book or a webite that gives examples on how to use these API's?

I have managed to get a few running to execute a command from VB but its taken me a while to figure out. The IBM Iseries information centre does give the API names and parameters but is not clear how they should be run together.

I am particularly interested in the sequence of Client Access API's I need to call to run a program on the AS400 and return parameter values ( i.e. 'PARAM' values defined in an RPG program ).


Dazed and confused

 

[as400pro]

IBM used to have examples at this page:

http://www.redbooks.ibm.com/tstudio/workshop/snippets/snippets.htm

, but it appears they have discontinued them.
You would think they could have left the old ones and just not added new ones to the page.



T. Bishop

http://www.as400pro.com

 

[MdnghtPgmr]

Skittle,

The API's are here. I don't know if it has what you need.

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm

HTH,
MdnghtPgmr

 

[MdnghtPgmr]

Skittle,

That didn't take me where I thought it would. Once there, look on the left and click "Programming". Then the second entry is the API "tab".

HTH,
MdnghtPgmr

 

[Mercury2]

I think the following page can be of some interest for you

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzaik/rzaikapprc.htm

particularly the chapter about "iSeries Access for Windows Distributed Program Call API" and the Programmer's Toolkit will be appreciated. The toolkit will lead you to VB examples that'll show you how to call a remote program accepting input parameters and returning output parameters.

 

[Skittle]

Thank for your help fellas.
I'm almost there but not quite...

I now understand how the API's work together and how I should use them. For example to run a program I need to:-

cwbo_CreateSystem ( create a system link )
cwbRC_StartSysEx ( create a dialogue with the system )
cwbRC_CreatePgm ( define a program )

cwcRC_CallPgm ( call the program )

cwbo_DeletePgm ( tidy up and delete program )
cwnCO_StopSys ( tidy up and end dialogue )
cwbo_DeleteSystem ( tidy up and delete system link )


However....my declaration for my system is not working.
The system handle parameter remains empty. Any ideas why?
Am I missing something silly?

In a module I have:-
'
' Create AS400 System Object
Declare Function cwbCO_CreateSystem Lib "cwbCO.dll" _
(ByVal strSystemName As String, _
ByVal lngSystemHandle As Long) As Integer

Executed from a Form button I have the following code where '99.99.99.99' is of course my AS400 IP address:-



Dim lngCO As Integer
lngCO = cwbCO_CreateSystem("99.99.99.99", lngSystemHandle)


My declaration looks like this:-


'
' Create AS400 System Object
Declare Function cwbCO_CreateSystem Lib "cwbCO.dll" _
(ByVal strSystemName As String, _
ByVal lngSystemHandle As Long) As Integer



In debug the lngSystemHandle retains a value of '0' although the lngCO becomes '4014'if that means anything.




Dazed and confused

 

[Mercury2]

4014 CWB_INVALID_POINTER A pointer is NULL
You must declare SystemHandle parameter as a pointer NOT as a value.
Read on and look out

http://publib.boulder.ibm.com/html/as400/v5r1/ic2978/info/rzaik/rzaik.pdf

from page 58 (pdf page 64)

[u]Wrong declaration[/u]
Declare Function cwbCO_CreateSystem Lib "cwbCO.dll" _
                 (ByVal strSystemName As String, _
                  [COLOR=red][b][s]ByVal[/s][/b][/color] lngSystemHandle As Long) As Integer

[u]Right declaration[/u]
Declare Function cwbCO_CreateSystem Lib "cwbCO.dll" _
                 (ByVal strSystemName As String, _
                  lngSystemHandle As Long) As Integer

 

[Skittle]

Thanks Talkturkey. It allocates a handle correctly now.
Really appreciate your help.

I still have one obstacle. (
The cwbRC_AddParm API is passing a parameter to my program on the AS400 as a wierd symbol instead of the actual character value I feed it. I'm assuming this is because I need to add one of the following values to the API so that ASCI is converted to EBCDIC and visa versa.

CWBRC_TEXT_CONVERT
CWBRC_TEXT_CONVERT_INPUT
CWBRC_TEXT_CONVERT_OUTPUT


The definition on page 682 of the pdf file you pointed me too indicates I should use the values like this:-

cwbRC_AddParm( hPgm,
CWBRC_INOUT | CWBRC_TEXT_CONVERT_OUTPUT,
bufferSize,
buffer );


I have been unable to find a definition to say what these constants are set to. I found all of the others in a text include file in my Client Access directory but these three are missing. Do you know what they are or where I can find out the value of these constants? I have searchedhe web and IBM's site but nobody states what they are set to. It may of course not be the cause of my problem but to eliminate it from enquiries it would be handy to know.

Example:-

I am calling a program on the AS400 from VB with a single character parameter. The CL program then sends a message to my user profile including the single character parameter. Thats all it is.
If I pass 'X' I get a message on the AS400 of 'ã'.


Declare Function cwbRC_AddParm Lib "cwbRC.dll" _
(ByVal lngProgramHandle As Long, _
ByVal lngType As Long, _
ByVal lngLength As Long, _
ByVal strParameter As String) As Long

Declare Function cwbRC_CallPgm Lib "cwbRC.dll" _
(ByVal lngConversationHandle As Long, _
ByVal lngProgramHandle As Long, _
lngMessageHandle As Long) As Long
.
.
.
Dim lngLength As Long
Dim strParameter As String
Const cwbrcInOut = 3
.
.
.
lngLength = 1
strParameter = "X"
lngRC = cwbRC_AddParm(lngProgramHandle, cwbrcInput, _
lngLength, strParameter)
lngRC = cwbRC_CallPgm (lngConversationHandle, _
lngProgramHandle, _
lngMessageHandle)
.
.
.


Dazed and confused

 

[Zaphod4242]

I would suggest using the iSeries Access OleDB provider instead of ODBC, and certainly instead of those cumbersome APIs. It is extremely easy to call a program and pass and return parameters. I have used this approach in lots of ASP web pages accessing the AS/400 (iSeries) database and calling programs directly. It works very well.

 

[Mercury2]

Skittle,
I tried out the cwbRC_AddParm API's and met also the same problem.The only workaround I finally found was to use cwbRC_RunCmd and cwbSV_GetErrText API's instead but I don't believe this is what you aim.
I think that Zaphod4242 is right and I would avoid to use those APIs and use iSeries Access OleDB instead.
Regards
--Philippe --

 

[Skittle]

Hmmm hate giving up after putting so much effort in.
If any of you guys know 'C' programming, I found this reference in the Client Express Toolkit as a text file:-


#define CWBRC_TEXT_CONVERT 0x0030
#define CWBRC_TEXT_CONVERT_INPUT 0x0010
#define CWBRC_TEXT_CONVERT_OUTPUT 0x0020

I'm going to post into the C forum to see if anybody can make sense of the definitions for me. If not, I'll have to try the way you guys have suggested.


Dazed and confused

 

[Skittle]

SORTED IT!!!!

The C definitions looked like Hexadecimal do I plugged in a value of 48 ( 0x0030 ) as CWBRC_TEXT_CONVERT and the correct parameter value was passed to the AS400!!

Yipeeee!
)

It may not be the best way to do it but I am almost out of time for my little project so it will do.
Many thanks all.



Dazed and confused

 

[Mercury2]

The definitions you're speaking of are on your HD in folder :
\Program Files\IBM\Client Access\Toolkit\Include\cwbrc.h
I knew that and did not reference it in my previous post coz I also used a value of 48 with no luck; hence this is why I thought you had taken the wrong route and recommended to use iSeries Access OleDB instead. What the heck did you do to pass the correct value ? )

 

[Skittle]

LOL! The plot thickens.....

My entire code is as follows. Its a single form
with a button on it to run the program. Another button exits the form. However....! I cannot get a return parameter value back. I pass the parameter to my TESTC1 program which isues a CHGVAR to change the parameter to something else. I can't figure out a mechanisum for getting the value back into VB. I tried the GetParm
API but it crashes the program!! ( This is which part of my code is commented out. Begining to lose hope....gone for a quick cry in the WC.

'
'
' Constant Values For Client Access Definitions
' ---------------------------------------------

Const cwbrcInput = 1
Const cwbrcOutput = 2
Const cwbrcInout = 3

Const CWBRC_TEXT_CONVERT_INPUT = 16
Const CWBRC_TEXT_CONVERT_OUTPUT = 32
Const CWBRC_TEXT_CONVERT = 48

Private Sub cmdExit_Click()
Unload Form1
End Sub

Private Sub Command1_Click()
Dim lngSystemHandle As Long
Dim lngConversationHandle As Long
Dim lngProgramHandle As Long
Dim lngRC As Integer


'
' Create System
lngRC = cwbCO_CreateSystem("99.99.99.99.99", _
lngSystemHandle)


'
' Start Conversation
lngRC = cwbRC_StartSysEx(lngSystemHandle, _
lngConversationHandle)

'
' Create Program
lngRC = cwbRC_CreatePgm("TESTC1", "QGPL", _
lngProgramHandle)
'
' Add Parameter
strParameter = "ABCDEFGHIJ"
lngLength = 10

lngRC = cwbRC_AddParm _
(lngProgramHandle, _
cwbrcInout Or CWBRC_TEXT_CONVERT, _
lngLength, _
strParameter)
'
' Call Program
lngRC = cwbRC_CallPgm
(lngConversationHandle, _
lngProgramHandle, _
lngMessageHandle)

' THIS BIT DOES NOT WORK AND CRASHES PROGRAM - START
'lngIndex = 0
'lngRC = cwbRC_GetParm
' (lngProgramHandle, _
' lngIndex, _
' cwbrcInout Or CWBRC_TEXT_CONVERT, _
' lngLength, _
' strParameter2)
'MsgBox strParameter2, vbOKOnly
' THIS BIT DOES NOT WORK AND CRASHES PROGRAM - END


'
' Delete Program
lngRC = cwbRC_DeletePgm(lngProgramHandle)

'
' End Conversation
lngRC = cwbRC_StopSys(lngConversationHandle)



'
' Delete System
lngRC = cwbCO_DeleteSystem(lngSystemHandle)


End Sub




I will include only the GetParm eclare as its the only one not working, if you want to see the rest I can post them

'
' Get AS400 Parameter Value
Declare Function cwbRC_GetParm Lib "cwbRC.dll" _
(ByVal lngProgramHandle As Long, _
ByVal lngIndex As Long, _
ByVal lngType As Long, _
ByVal lngLength As Long, _
strParameter2 As String) As Long



Dazed and confused

 

[Skittle]

Hmm found another oblique reference. For GetParam, the index must >= 1. I tried setting the inde to 1 and the program runs without crashing but gives me a return variable value in strParameter2 of blanks.

Anybody any ideas where I'm going wrong....???



Dazed and confused

 

[Mercury2]

I tried out your example above and as a result ... no luck: it does NOT work, same symptoms and I'm fed up with it.
FWIW, I'd like however to publish my "working-but-not-accurate-solution" w/ "cwbRC_RunCmd and cwbSV_GetErrText API's" instead, if this so-called method can be of any help for you.

AS/400 side
------------
(CLLE program "FROMVB")

pgm ( &parm1 &parm2 )             
dcl &parm1 *char 4                
dcl &parm2 *char 4                
chgvar &parm2 '2222'              
sndpgmmsg ( &parm1 *bcat &parm2 ) 
dmpclpgm                          
endpgm

Client side:
-----------

Option Compare Database
Option Explicit

'------Declarations------
Declare Function W32_cwbCO_CreateSystem Lib "cwbCO.dll" Alias "cwbCO_CreateSystem" (ByVal SystemName As String, hSystem As Long) As Integer
Declare Function W32_cwbRC_StartSysEx Lib "cwbRC.dll" Alias "cwbRC_StartSysEx" (ByVal hSystem As Long, hRequest As Long) As Integer
Declare Function W32_cwbRC_RunCmd Lib "cwbRC.dll" Alias "cwbRC_RunCmd" (ByVal hRequest As Long, ByVal cmd As String, ByVal hErrHandle As Long) As Integer
Declare Function W32_cwbSV_CreateErrHandle Lib "cwbsv.dll" Alias "cwbSV_CreateErrHandle" (hErrHandle As Long) As Integer
Declare Function W32_cwbSV_GetErrText Lib "cwbsv.dll" Alias "cwbSV_GetErrText" (ByVal hErrHandle As Long, ByVal strText As String, ByVal strLength As Long, ByVal alwaysNull As Long) As Integer

Function AS400_Connect()

'-----Code-----
Dim hSystem As Long
Dim hRequest As Long
Dim resCode As Integer
Dim cmd$
Dim sysname As String
Dim hError As Long
Dim errMsg As String * 255

cmd$ = "CALL PGM(MYLIB/FROMVB) PARM('XXXX' ' ')"
sysname = "Ip_Addr"


resCode = W32_cwbCO_CreateSystem(sysname, hSystem)
If resCode = 0 Then
Debug.Print "AS/400 system environment successfully initialized."
Else
Debug.Print "cwbCO_CreateSystem failed with code " & resCode
Exit Function
End If

Debug.Print "Initializing AS/400 command request.."
resCode = W32_cwbRC_StartSysEx(hSystem, hRequest)
If resCode = 0 Then
Debug.Print "AS/400 command request successfully initialized."
Else
Debug.Print "cwbRC_StartSysEx failed with code " & resCode
Exit Function
End If
Debug.Print "Initializing AS/400 error interpreter.."
resCode = W32_cwbSV_CreateErrHandle(hError)
If resCode = 0 Then
Debug.Print "AS/400 error interpreter successfully initialized."
Else
Debug.Print "cwbSV_CreateErrHandle failed with code " & resCode
Exit Function
End If
Debug.Print "Issuing command on AS/400.."
resCode = W32_cwbRC_RunCmd(hRequest, cmd$, hError)
If resCode = 0 Then
Debug.Print "Command issued"
Else
Debug.Print "cwbRC_RunCmd failed with code " & resCode
Exit Function
End If
' This is where the method to retrieve the parameters is not really accurate since
' I'm getting results thanks to the SNDPGMMSG command in the called program
' and not from the " chgvar &parm2 '2222' " command. 
' cwbSV_GetErrText API is normally used to trap back AS/400 error messages   :o(
Debug.Print "Getting results..."
resCode = W32_cwbSV_GetErrText(hError, errMsg, 255, 0)
Debug.Print Trim(errMsg)
Debug.Print "End of module"
End Function

What you exactly get in errMsg is "XXXX 2222".

HTH (a little)

 

[Skittle]

Hhhhmmm.

Time to start looking at the Access OLE DB providor recommended by Zaphod42 I think. Your command method is still handy though.

Many, many thanks for your help.



Dazed and confused