RCVF equivalent in VB script using cwbx

Hi…
Does anyone know how to read DBF directly from VB script, similar to RCVF in CL.
The idea is, I want to read file from PC, without have to create any program in the as400.
condition is, i am only allowed to use cwbx class, so i'm scratching my head to find suitable API in the as400 to read a file, but i can only find QHFRDSF to read only stream file from HFS.
again, please note, i am not allowed to use ADODB, ODBC connection... only cwbx class
also don't want to put ANY program in the AS400
LVL 6
dedy_djajapermanaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dedy_djajapermanaAuthor Commented:
i looked at QQQQRY, but was intimidated by the complexity of it. is it the only suitable API i can use?
daveslaterCommented:
Hi
how big is the file?
Do you need to query it or receive all records.

dave
dedy_djajapermanaAuthor Commented:
less than 100 records, so i was thinking to do the "query" part on the pc.
a RCVF equivalent will do, i.e., record by record sequentially, but i couldn't find a way....
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

daveslaterCommented:
Hi
try this - it is written in Excl past into the Thisworkbook area
Dim systemNames As New cwbx.systemNames
Dim as400 As New cwbx.AS400System
Dim dlr As cwbx.DatabaseDownloadRequest
Dim tr As cwbx.DatabaseTransferResults
Const as400filename = "slaterd/eedownload"
Const PcFilename = "c:\temp\ee.tab"


Sub xx()
' Declare variables
' Retrieve the default system and use it to initialize the AS400System
' object
as400.Define systemNames.Item(2)
' Set the System property of the download request object
Set dlr = New cwbx.DatabaseDownloadRequest
Set dlr.System = as400

' Set the AS/400 file name for the transfer
dlr.AS400File = as400filename

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''  Set PC options for the data being downloaded''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Set the PC file type to tab delimited text
dlr.pcFile.FileType = cwbdtTabDelimitedText
'dlr.pcFile.FileType = cwbdtDosRandom
' Set the name of the PC file
dlr.pcFile.Name = PcFilename
On Error Resume Next
Kill PcFilename
On Error GoTo 0
'dlr.pcFile.Options.ExistingFileOption = cwbdtOverwriteOutFile
On Error Resume Next
' Attempt the transfer twice, to ensure we get an error
dlr.Download

Close #1
Open PcFilename For Input As #1


Do While Not EOF(1)
DoEvents
Line Input #1, txt
Debug.Print txt
DoEvents
Loop

'' display results
Set tr = dlr.TransferResults

' Display the completion status of the request
If (status = cwbdtStatusCompletedOK) Then
Debug.Print "Transfer completed successfully " & tr.RowsTransferred
ElseIf (status = cwbdtStatusCompletedWithWarning) Then
Debug.Print "Transfer completed with a warning"
ElseIf (status = cwbdtStatusCompletedWithError) Then
Debug.Print "Transfer completed with an error"
End If

For Each dataerr In tr.DataErrors
Debug.Print "Error Type = " & dataerr.ErrorType & vbCr & _
"Error Row = " & dataerr.Row & vbCr & _
"Error Column = " & dataerr.Column
Next


End Sub


dave
daveslaterCommented:
Hi
There is a better way if your file does not contain any packed fields - I can not get them to convert!

Dave
dedy_djajapermanaAuthor Commented:
hi dave,

thanks. not too sure whether it's working. in this company, almost everything is not working due to extra tight restriction (they're using 3rd party security package). RTOPC.exe and CWBTF.EXE are rejected.

my mistake, should have stated "cwbx.program class to call as/400 API" in my requirement. meaning, only 1 option, calling as/400API. i have created some functions, and they work. now i'm stuck in retrieving file records....
Member_2_276102Commented:
Dedy:

Are you permitted to submit commands? What version of OS/400? Do you have QShell installed?

Weird restrictions -- no access via normal network exit points but required to read files from a remote PC. That's an oxymoron.

Tom
daveslaterCommented:
hi
Still having fun with this - tom's Qshell may be the only solution.
I have tried a l,ot of things but no luck.

need a beer and some sleep.

Dave
dedy_djajapermanaAuthor Commented:
Tom,
Yes, weird restriction, i just joined this company, and don't want to raise too many request at this moment. i'm trying to work with the current environment setting as is.
i can submit job using cwbx.program calling qcmdexc, but qshell is restricted too, OS ranging from V4R4 to V5R2...
if i have to submit a job, how can i get the return value?

Dave,
it looks very close, isn't it? :-)

now, the workaround that i'm taking at this moment, is to cpytoimpf, and read it with QHFRDSF... yes, it's dumb thing and very slow, but that's the best i can do for now, i think i really need to find out how QQQQRY can do the trick
daveslaterCommented:
Hi
reading this as part of another question.

Java Tool Box
The "Toolbox" JDBC driver. This is shipped as part of the IBM Toolbox for Java (57xxJC1). It is implemented by making direct socket connections to the database host server. This happens to be the same route that the Client Access/400 ODBC driver takes. However, Client Access/400 is NOT required. The Toolbox runs on any JVM. The class name to register is com.ibm.as400.access.AS400JDBCDriver . The URL subprotocol is as400 .

Is this a possibility?

Dave
dedy_djajapermanaAuthor Commented:
hmm... the toolbox feature is interesting, but not applicable to my requirement. first, i know nothing much about java... second, i actually do my program for an excel spreadsheet.
daveslaterCommented:
Hi
just trying options. I'll keep looking :-)

How about FTP?

Dave
daveslaterCommented:
Is there a shared folder the PC has access to?
dedy_djajapermanaAuthor Commented:
FTP, no.
shared folder, that's how i do it now, CPYTOIMPF to put into shared folder, then read with QHFRDSF... (\\system\QDLS is not shared)
dedy_djajapermanaAuthor Commented:
didn't realize you're referring to "shared folder", i thought it's /QDLS
Member_2_276102Commented:
Dedy:

I'm not sure why I asked about submitted jobs. It's just that the more is known, the more the scope of a solution gets defined. Unfortunately, I wasn't remembering that you couldn't "create any program in the as400". Ouch. That pretty much cuts the possibility of any server function that you might create. And having to go back to V4R4 effectively eliminates the really cool QShell possibilities.

Can you define "create any program in the as400" better? For example, would a REXX script qualify as a "program"? A PC could load a REXX script and execute it without compilation. There would be little practical difference between that and executing commands one at a time via QCMDEXC or QCAPCMD.

Also, you name a restriction of "only cwbx class". Is that a policy restriction or an implied restriction? That is, does your policy specifically state "only cwbx class" or does it exclude so many other things that that's the only thing left over? One potential that seems not covered is VA-RPG. No program on the AS/400, no ODBC, no ADODB, just normal OS/400 interfaces. But it makes it pretty easy to read records from AS/400 files.

Hmmm... Excel? Can you elaborate on that? Do you want the records to appear as rows in Excel or do you want a PC program that alters rows based on calculations on different records from your file? E.g., a tab-delimited CPYTOIMPF is pretty easy for Excel and you're already testing CPYTOIMPF possibilities. Excel VB could massage that. That seems a good direction to investigate.

As for Windows shares, if NetServer is running, you can create shares dynamically via the Server Support APIs. You could even start/stop NetServer via QCMDEXC. But without QShell and without writing programs on the AS/400, it looks like /QDLS plus the HFS APIs is your only choice.

You got yourself into a deep one. Sheesh... a company uses a server that provides as much as OS/400 does and then says "Don't use it!"

Tom
daveslaterCommented:
hi
i say sack the security guy - what is the point have having a great db if you can not access it!
dedy_djajapermanaAuthor Commented:
haha... i understand your points... questioning the company's "policy" on the restrictions that's so much that left nothing much.
ok, let me explain the background...
First, i'm a system guy, not a developer. I just joined the company last month.
actually, not all the systems are restricted in the same manner, some are older system, that doesn't have sqlpackage that i can't access through ADODB, some are restricted for download, etc etc.
I was creating a system monitoring tool, and wanted to try something new... 100% PC based, same package that works in all boxes, history can be kept "forever" as PC disk is so cheap. but i knocked onto those restriction, it works in one machine, but doesn't work in another...
Member_2_276102Commented:
Dedy:

Then I gotta say "Go with a VA-RPG routine." If you've never written VisualAge RPG for a PC function before, this is the time for it. I assume all you'd really want it to do would be read records from whichever AS/400 you point it at and deposit them on your PC. Most of the actual "coding" will be pretty much the same as if it were an RPG IV program on the AS/400 -- it's just developed, compiled and run on your PC.

There'd only be one significant 'trick' that you'd need to work around and that'd be the control over which AS/400 it would connect to for any given run. I haven't done that for a while, but I don't recall it being that difficult.

You wouldn't even need to develop any significant GUI interface if you didn't want one. The F-spec becomes a DDM connection between your PC and the AS/400. Reads are almost exactly the same as you'd do them native on the 400.

Technically, you could almost develop a simple DSPF with an F-key to say "Go!" and an RPG program to do the work. Then import them both into the VA-RPG IDE.

Hmmm... you probably wouldn't want to do the stuff that updates an Excel spreadsheet directly, but you could certainly format lines to output to a PC streamfile. Does "no ODBC" mean "no ODBC to the AS/400" or does it mean "no ODBC at all". With VA-RPG you could read from the AS/400 and then use ODBC to update an Access database or similar but only on your PC. That'd perhaps be easier than coding to write a PC .CSV or tab-delimited streamfile.

Tom
dedy_djajapermanaAuthor Commented:
tom: unfortunately it's not right timing for me to explore VA-RPG

tom&dave: any of you used QQQQRY before?
dedy_djajapermanaAuthor Commented:
i think it's out too...

i'll just keep it open until monday, to see if anybody got anything, otherwise i'm gonna close it
daveslaterCommented:
Hi
don't close it yet.

All avenues have not been explored.

can you map a share on the IFS? If so then you can FTP it to there then read it.

It may be faster than the CPYtoIMPF route.

Dave
daveslaterCommented:
Hi
QQQQRY - never needed to its built into sql opnqery etc..

Dave
Member_2_276102Commented:
Dedy:

It'll be faster to learn VA-RPG than QQQQRY -- unless you don't know RPG IV. VA-RPG uses the RPG IV compiler.

However, perhaps there is even another alternative -- ever use QLGSORT and QLGSORTIO APIs? These are essentially what the FMTDTA command does but with an added bonus. The APIs can use input and output record buffers. I.e., you're not required to sort input files into output files -- you can pass a record buffer in and sort to output files, or sort input buffers to output buffers, or sort input files to output buffers. You can control how many records are in the buffers.

What that means is that you can call the APIs naming an input file and supplying an output record buffer variable and specifying single record per buffer. Each subsequent call puts another record in the output buffer field. I use this as a way for CL programs to read and write files without using DCLF or SNDF or RCVF.

It means I have to specify a 'sort sequence' but I generally just make one up since I don't care.

As callable APIs, maybe these could work?

Tom

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dedy_djajapermanaAuthor Commented:
Dave: i've thought of ftp too, but ftp is not a preferable option, because:
I have to either save the password, or prompt for the password everytime..., or "capture" the password to feed into both as400connection and ftp (i don't want it to be a security issue in the future).

Tom, the QLGSORT and QLGSORTIO look interesting, i'll explore them monday.

Discussing with both of you is really enjoyable...
daveslaterCommented:
Hi - I am about out of ideas;

Can't you even write one program on the 400 and get it compiled - that way we could do what we want.

I spent 1/2 of today looking at QQQQ - nervous twitch – I would have spent longer but got to the stage where I needed several beers and we have a no alcohol policy.

Dave
ps
I have a good cricket bat (like a base ball bat but bigger)  that could be used to persuade you security guy/girl to change their polices :-)
daveslaterCommented:
ps the program I was thinking of was to dump the records to a user space and read them from there!

daveslaterCommented:
Got It  - well may be

OK - you have a net work of 400's - some you can access because of the OS level and some you can not.

How about using the less secure ones to craete a DDM link to the secure ones, copy the records to a temp file and read it from a non-secure one!

Dave

daveslaterCommented:
Ok don't tell ne DDM has been locked down
Member_2_276102Commented:
Dave:

Yeah, that was one I asked myself when VA-RPG came to mind. Personally, I suspect it isn't even password-protected, but ya' never know.

Tom
dedy_djajapermanaAuthor Commented:
LOL...

not all servers' DDM are started by default... :-)
QLGSORT and QLGSORTIO look like the ones... i need time to read before i can write the code. kinda busy weekend..

of course i know cricket bat, but i prefer to persuade people with beer, but they don't deserve that. :-)

but for both of you, if any of you come down to singapore, do give me a call, i'll buy you beer... :-) who knows that from a casual conversation we can come up to a business idea...
dedy_djajapermanaAuthor Commented:
Tom:
QLGSORT and QLGSORTIO REALLY WORKS, exactly like RCVF. performance-wise, it's very good too... I'd been going through the API list (for this purpose alone; more than once), but didn't bump onto those ones.
Thanks!

Dave:
Thank you too.

BOTH:
The offer to buy you beer is still valid for any of you. :-)

regards,
dd
Member_2_276102Commented:
Dedy:

They take a little practice, but these can be extremely handy APIs in surprising places. Hmmm... I probably should post my examples on my web site. Well, glad you see how they work for you. I'd never considered that they could be used for _remote_ access to files before.

Tom
Member_2_276102Commented:
...and yeah, next time I take some time to visit Singapore, I'd gladly share a beer with. But I'd guess that you'll probably be in Seattle before I make it to Singapore, so I'll say that the same will be true for you here.

Tom
dedy_djajapermanaAuthor Commented:
just in case anyone wants to see the result, i posted it in my page
http://e.1asphost.com/dd400/index.htm
Member_2_276102Commented:
Glad to see another web page with another example out for the AS/400 people. The more that show up, the less I have to feel badly about how my pages have aged!

Tom
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.