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

Phillip KnoxSenior Systems AnalystAsked:
Who is Participating?
 
Gary PattersonVP Technology / Senior Consultant Commented:
Well, the "best" way to do this is to drop the Vba approach altogether and modify the host program to use the STRPPCMD command to open Acrobat on the user's machine.

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fcl%2Fstrpccmd.htm

Sometimes, though, that just isn't an option, so:

Is is safe to say that you don't do much VBscript/VBA programming and you also don't know SQL?  Don't mean to offend!  It just looks like you are struggling with some basic concepts here, and those are two basic prerequisites to doing what you want to do.  

Anyway, here's the goal:

1) Scrape some parameters off the screen,
2) Establish a connection to the AS/400 database (in this case using ado, which is fine).
3) Use the parameters that you scraped of the screen to form a query, and grab one column from one specific row of a database file.
4) Use the data in that column to form the pathname to a PDF and open it using Acrobat.

Assuming that is right:

1) Looks ok

2) Looks close, but you need to get your string syntax right.  The right side of the "=" needs to evaluate to a single string in double quotes:

cnnstr = "DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=MY400;USERID=KNOP;PWD=yourpasswd"

3) Your current query selects all columns "*" and all rows from table ITSR6FILE.TRLRPMT.  I think you probably want something more like this:

SELECT TPCAPC                                                         -- Select the columns you want
FROM  ITSR6FILE.TRLRPMT                                        -- From the table you want
WHERE TPTRLR = contents of RetVal variable           -- Select the row(s) you want.

sql = "Select TPCAPC FROM FROM  ITSR6FILE.TRLRPMT WHERE TPTRLR =" & RetVal

4) of the IF statement.  Our SQL WHERE clause handed that for you.  You'll need to code to handle the "no record found" condition.  What if you get multiple hits?  Anyway, assuming for a moment that we live in a perfect world and there is always one perfect match:

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

"resultSetVar!fieldname" is how you access a single field out of the current row in a recordset.  We designed our query to just return one column, but it could have had many, and this is how you reference just one of them.

Suggest you test your SQL using STRSQL or Navigator's Run SQL Statement and get the syntax right first, then plug it into the VBA program.

Again, this probably needs some work to handle "no record found" and "multiple records found" conditions, record locks, connection drops, etc, and I didn't test it, but it is pretty close.  Post back if you have trouble.

- Gary Patterson



 
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;USERID=KNOP;PWD=yourpasswd"
sql = "Select TPCAPC FROM FROM  ITSR6FILE.TRLRPMT WHERE TPTRLR =" & RetVal
Set rs = CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = conn

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

Open in new window

0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
Gary,

I'm receiving this error:

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

Any thoughts?

Thanks,

Phil
0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
Murph, can you tell me how to check and see if the SQL statements are working? Like a debug?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Phillip KnoxSenior Systems AnalystAuthor Commented:
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

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

Copy it to the AS/400 SQL to validate and check the result.

Does the Trim(rs!TCPAPC) give any result? I assume that the TCPACP should be a constant (like in VB) so more the idea like Trim(rs!"TCPAPC") or the filed number like Trim(rs!0) or Trim(rs!1)
0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
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
0
 
MurpheyApplication ConsultantCommented:
just login in the Iseries,
Type STRSQL (enter)
and past your command

0
 
Gary PattersonVP Technology / Senior Consultant Commented:
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



0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
Not at work right now, but will test these out tomorrow guys. Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.