can this be done in vb? at all? 2

[PammyBoy]

Hello All,

Can the following be done?:
At work, we have database software written in basic. It runs on an IBM AIX machine and our windows 2000 server network connects to it via a terminal emulator. Is it possible to write a program in vb that'll go through the AIX database app and copy data via the terminal emulator and save the data into an Access database? Is this possible at all? My company uses custom database software but wants to switch to a different application. However, the only conceivable way to get at the old data would be to manually tranfer it to our new application. This would take months and numerous human errors would occur. Is there another way I can get at this "data"? This post may not be approriate for this forum - any help would be appreciated.

 

[John Lewis]

Some of your details are a bit foggy, but in general what you intend to do is very possible. In fact, you might not need vb at all.

First, I don't really understand how your W2K machines are connected to the IBM. You mention terminal emulation and a network. Is the IBM connected directly to the network, or do each of your machines have a serial connection to the IBM machine? If the IBM is connected to the network, have you considered the possiblity of a ODBC driver for the database on the IBM machine? Again, the details are missing, but there are several ODBC drivers available for different databases running on AIX.

If you have already exhausted that route, the next best option (in my opinion) is to capture the data via reports sent to a serial printer. This requires that a serial printer is configured on the system, or you have the ability to configure one, and that there are reports that you can print that have all of the data you want to recover. The data for each record does not have to be on the same report, you just have to be able to print out all of the data. The data from the different reports can be pulled into different tables in Access, then linked and exported to suit your new application.

The basic procedure would be take a serial cable and remove the end that does not connect to the computer to expose the wires. Expose the connections on the cable connecting the printer to the IBM at the printer side, and connect the recieve data line and the data ground from the pc cable to the same lines for the printer. This has to be done in a way that the connection to the printer can be maintained, as the printer will provide flow control. Set the terminal emulation to receive an ascii file to file, and start the report. When the report is done, you should have a copy on your pc that you can parse to extract your data into an Access database, or import directly into your new app.

Another option is a special terminal emulator called a screen scraper. The screen scraper will act like your present terminal emulation as far as the IBM is concerned, but it will allow you to save the data from specific portions of the screen to a data file. There are several applications commercially available. I have never used any of the commercial programs, so I can't offer a specific suggestion there, but search on 'screen scrape IBM' at your favorite engine and you should get several options.

Sorry for the lengthy post. Sometimes I get going and just can't stop. Hope this was of some help. I'll be watching, so if you need some clarification post again.

John

 

[PammyBoy]

Wow! Thanks John, your post was extremely helpful. I'll keep you updated.

Tom

 

[MichaelRed]

Depending on your access to the "custom" db on the AIX system, you should be able to create a routine to export the entire db to a simple text file and simply copy that file from the AIX system to any Win machine. Following this, there are ever so many ways to "read" the trext file and create records in Ms. A. (or other db engines).

In the general sense, the text file could be generated by the report function in the original (AIX) db (if you don't have the capability of writting custom routines in the application), simply listing each record and each field in the record to a report sent to a text-only printer re-directed to a file. I'm not at all familiar with AIX, but most operating systems support the re-direction of peripherial output, and the reporting system doesn't (usually) have any influence on the selection of the output device.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PammyBoy]

So if there isn't a print to file option in the database app, how would I redirect the data to a file instead of the printer?

-Tom

 

[PammyBoy]

Okay, let's say I wanted to take a crack at writing a screen scraper... anyone have any advice on going about this?

 

[strongm]

Yep. Just do a key word search in this forum. I've provided at least one full work through with code.

 

[John Lewis]

MichaelRed had a couple of really good points in his post, so let's start there. I made some assumptions in my first post that may or may not have been correct. First, I assumed that you did not have access to the basic code or the database on your system. If you do indeed have that access, that is probably a better place to start.

Second, I assumed that you did not have root access to your system. If you do have root access, the idea of setting up a printer to dump your reports directly to a file is certainly worth investigation. I haven't the first clue as to how to set you up to print to file in AIX, but if you ask in forum52 (AIX), I'm sure someone over there can tell you how to set up the printer and how to get the files from the AIX box to a machine that you can work with.

Now to the screen scraper . . . First, strongm . . . are you referring to your post in thread222-302107 ? If so, that is a fine example of how to scrape the screen to a bitmap, but a bitmap is of little value in this case. What is needed is the actual text that is on the screen, not an image of the screen. If I hit the wrong thread, please post a link, as I have not been able to find anything else related. I have found several other posts in this and other forums related to screen scraping, but all of them focus on capturing text or images from an existing application screen. In my opinion, that is not the best way to proceed with this project.

Most commercial terminal emulator/screen scrapers work by capturing the data from within the terminal emulation program itself. It seems to me that this is more efficient and should provide for easier adaptation to the different screens you will have to capture data from.

If you elect to try that option, the first step is to write a basic terminal emulator, keeping in mind that you need to store the data in such a way that you can easily retrieve the text that is displayed on a specific portion of the screen. You need to establish a connection to the AIX machine, either using the mscomm control for a serial port connection or the winsock control for a network connection. The serial port option is much easier to implement because you can set up a serial terminal to run parallel to the machine your program is running on and watch what is supposed to be happening in real time.

Once the connection is established, I would set up an array of fixed length strings, with the length of the strings equal to the width of your terminal (probably 80) and the number of elements equal to the number of lines (probably 25). Each element of the array will need to be initialized by copying a string of spaces to it. If you simply empty the string array in the initialization phase, you will have problems with the data extraction.

The data that comes your connection will have control codes embedded that indicate where the text that follows is to be placed, how it is to be formatted, and sometimes commands to beep or pass data to an attached printer (or other device for that matter). The formatting codes can be ignored, as it doesn't really matter if text is underlined, bold, reverse, etc. The port pass through can cause problems if it is used, so if your application does pass through printing or device control, you will have to trap and discard it. The placement codes will have to be held, and you will need to place the text that follows in the appropriate position in the correct element of the array. The control codes that are used vary from terminal to terminal, so you will need to know what model terminal you are emulating. From there, the termcap file included with Linux will provide a list of the control codes used for the terminal so you know what you are trapping.

When the screen is filled, the data you need can be extracted from the array and stored in a database. As you will be gathering different types of data from several different screens (again, I am assuming), I would create a procedure to open a text file with a definition of what data you wish to extract, and use that information to do the actual extraction instead of hard coding the extraction parameters. This will allow for you to write a different configuration file for each screen instead of changing the code for each screen.

After the data is extracted, repeat the initialization routine and send the appropriate command to the server to proceed to the next record. This project will take some coding, but it really is not as bad as it sounds. Just some serious time.

Another thought that comes to mind. . . you mentioned that you are preparing to switch software. Have you selected a new software vendor? If so, have you asked them about the data conversion? Often times in a situation like that the new software vendor will handle the data conversion. There is generally a fee involved, but it is usually reasonable considering the effort involved.

Wow! Another one of those lengthy posts. Hope it is of some help, and of course I'll be watching.

Happy New Year
John

 

[vb5prgrmr]

for screen capture or part thereof check out the code i found and posted in thread708-428674


Good Luck

 

[strongm]

No, I was referring to thread222-255803

 

[strongm]

(and the technique will only work here if the terminal emulator in use is a proper console application, and uses STDIN and STDOUT)

 

[PammyBoy]

Wow! Thanks to both you for all your advice. You were right in assuming that I do not have access to the AIX machine; screen scraping seems to be the only way to go. And because of your detailed advice, I'm going to take a try at writing my own screen scraper for this project. As always, I'll keep ya updated. Thanks again.

 

[strongm]

So, did you make any progress?

 

[Merlijn]

I don't know if this exists on IBM AIX machines, but I remember from the days I used to work with IBM S/3x-AS/400 and PC's that they had a util called PC-support. With it one could transfer data in both directions between an IBM and a PC. From IBM to PC one would get an ASCII file with the same layout as the original file on the IBM.

Maybe some of the terminalemulators of today have the same possibility. I can't tell because I don't work on IBM midranges no more.

Hope my two cents may be of any help.

Merlijn is the name, and logic is my game.

 

[PammyBoy]

I've been swamped at work and my attention has been drawn away to other more pressing projects. However, I did find an ocr sdk called smartlink that might make writing my screen scraper a little easier.

 

[sdraper]

In our shop we use PCS3270 by IBM for our terminal emu. It has a well documented VB API. Have you checked to see if yours does too?
sdraper