asked on

Use VBScript to read (SQL?) a record from an iSeries (AS400) database?

Using VBScript, how can I read a record from iSeries database file and gain access to the fields in the record?

As you can see in the code example, I'm using the trailer number off the 5250 display to 'select' using SQL, retrieve that trailer's record from the 'TRLRPMT' file on the iSeries, then using the 'TPCAPC' field, use the system 'shell' to call acrobat and display the pdf file containing the trailer schematics.

Please look at what I've come up with in the code example a critique the methology as well. I'm not sure the 'conn.open' statement is even working.

This is 500 points worth and I need this asap. All help is appreciated.

Thanks,

Phil

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=GET AND DISPLAY TRAILER SCHEMATICS
[PCOMM SCRIPT SOURCE]
Dim RetVal
Dim Conn
Dim Cmd
dim sqlstmt

' Initialize the session
autECLSession.SetConnectionByName(ThisSessionName)

' Get 6 characters starting at row 4 col 22 of screen 
RetVal = autECLSession.autECLPS.GetText(4, 22, 6) ' Get the trailer number

Set conn = CreateObject("ADODB.Connection")
Set Cmd = CreateObject("ADODB.command")

conn.open = Driver={Client Access ODBC Driver (32-bit)};System=MY400;
Uid=KNOP;Pwd=XXXXXXXXX;

sql = "SELECT * FROM ITSR6FILE.TRLRPMT" 

Set rs = CreateObject("ADODB.Recordset")

Set rs.ActiveConnection = conn

rs.Open Sql

do while not rs.eof

rs.movenext

msgbox"" & tpcapc & " is Invalid data, try again."


If tptrlr = retval Then

ProcessID = autSystem.shell("acrobat.exe","P:\Trailers\" & Trim(TCPAPC) & ".pdf")

End If

If tptrlr = retval Then

exit do

End If

Loop

rs.Close

Open in new window

ASKER CERTIFIED SOLUTION

Gary Patterson, CISSP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phillip Knox

ASKER

Gary,

I'm receiving this error:

PC5250 Error in the script on line 26. Expected ')'.

Any thoughts?

Thanks,

Phil

Phillip Knox

ASKER

Murph, can you tell me how to check and see if the SQL statements are working? Like a debug?

Phillip Knox

ASKER

Gary,

I'm sorry, I put the questions prior to a different expert I was working with previously on a different question. Really apologize for the blunder.

I've got this narrowed down to getting the statement on the 'ProcessID' line working.

I can't seem to find the correct way to insert the Trim(rs!TCPAPC) into the statement to display the pdf.

As you can see, I've got several example lines I've tried for getting the display PDF to work.

I can substitute a real file name on the line like "HETRAILKING.PDF" and the shell statement works fantastic.

Is there a way to 'see' if the SQL is actually pulling a record off the iSeries?

Please respond with your ideas. You've been a real help so far.

Thanks, Phil

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=GET AND DISPLAY TRAILER SCHEMATICS
[PCOMM SCRIPT SOURCE]
Dim RetVal
Dim Conn
Dim Cmd
Dim ProcessID
Dim sqlstmt

' Initialize the session
autECLSession.SetConnectionByName(ThisSessionName)

' Get 6 characters starting at row 4 col 22 of screen 
RetVal = autECLSession.autECLPS.GetText(4, 22, 6) ' Get the trailer number

Set Conn = CreateObject("ADODB.Connection")
Set Cmd = CreateObject("ADODB.command")

conn.open "DRIVER={iSeries Access ODBC Driver};SYSTEM=S1058EB2;USERID=KNOP;PWD=PIPSTER11"

sql = "Select TPCAPC FROM ITSR6FILE.TRLRPMT WHERE TPTRLR = '"& RTrim(RetVal) & "'"

Set rs = CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = conn

rs.Open sql

rs.movefirst

Msgbox "PDF file = '"& Trim(RetVal) & "'"
Msgbox "PDF file = '"& Trim(TPCAPC) & "'"

'ProcessID = autSystem.shell("acrobat.exe","P:\Trailers\'" & Trim(rs!TCPAPC) & "'".pdf")
'ProcessID = autSystem.shell("acrobat.exe","P:\Trailers\'" & Trim(rs!TCPAPC) & "'.pdf")
'ProcessID = autSystem.shell("acrobat.exe","P:\Trailers\"' & Trim(rs!TCPAPC) & '".pdf")
rs.Close

Open in new window

SOLUTION

Theo Kouwenhoven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phillip Knox

ASKER

I've tried to use STRPCCMD and it seems to work ok but, if the file's not found or doesn't exist, it just sits there, There's no error message returned from STRPCCMD.

Murph, can you show me how to test in the iSeries SQL I can't seem to get it to use the lines i'm putting in.

thanks

Theo Kouwenhoven

just login in the Iseries,
Type STRSQL (enter)
and past your command

Gary Patterson, CISSP

You can test your SQL statement by checking the "rs" resultset and seeing if it contains the expected value. What does this return if you insert it in place of one of your other Msgbox statements?:

Msgbox rs!TCPAPC
or
Msgbox "acrobat.exe","P:\Trailers\'" & Trim(rs!TCPAPC) & "'".pdf"

In VB6 (and VBA, I think), you can access a column in the current row of a recordset several different ways:

Column number in Fields collection (counting left to right in your SELECT clause):
rs.Fields(1).Value

Column name in Fields collection (quotes required):
rs.Fields("TPCAPC").Value

Column name using default collection (which is Fields - quotes required):
rs("TPCAPC").Value

"Bang" syntax (no quotes around column name):
rs.TPCAPC

If you have trouble with one of these, try the others. Seems like VBA is different from VB rules sometimes.

- Gary Patterson

Phillip Knox

ASKER

Not at work right now, but will test these out tomorrow guys. Thank you!