Robelle | Products | Library | Support | Partners | Contact Us |
|
September 2001
To: Users of Robelle Software Re: News of the HP e3000 and Robelle, 2001 #5 If you haven't been visiting the Robelle web site, www.robelle.com, at least once a week, here is what you have been missing. Included are many of the stories posted to the web since the last What's Up DOC newsletter in July 2001.If you are reading a PDF or text version of this newsletter, remember that the headline of every story is actually a web link to the full story or more information. If you want to follow a link to get more information, go to the web home of the current newsletter: http://www.robelle.com/library/newsletter/latest.html Table of Contents: |
Robelle had a good week at HPWorld and we snapped many pictures. Click the link above to find out about the "Most Fun Booth" and Bob Green's Top Ten Software Tips from the Dark Side
Recently a friend needed to work with some data in mySQL to test a Web Application. I figured that I could provide some test data from one of our IMAGE databases and use our STExport program (part of the Suprtool product) to move the data over.
mySQL is an Open Source database that is commonly used as the backend database server for many Web applications for a huge number of Linux and Unix platforms as well as Windows machines.
The source and or binaries can be obtained from www.mysql.com or www.sourceforge.net and many other download mirrors around the globe.
In order to get started we decided to build a mySQL database that looked like an Image database, building a simple Master dataset and a single detail dataset. The byte type fields in Image were created as char fields. The I2 or J2 fields were created as int fields. The database resided on a small Windows 98 laptop system.
Having done this, we extracted data from the 3000 database and used default STExport settings to output a file that was comma delimited, with each field enclosed in quotes.
In investigating how to import data into mySQL, we first tried the mySQLImport program, but it didn't seem as robust and we could not figure out how to tell it what delimiters to use.
In looking at the documentation, we thought that the LOAD_FILE command might work, but further investigation showed that this command opens the file and returns the contents as a string. This feature is only used by Text and Blob columns of mySQL.
We finally had success with the LOAD_DATA statement after reading the documentation on the mySQL Website at:
http://www.mysql.com/doc/L/O/LOAD_DATA.htmlWe quickly tried importing the data with the default STExport settings. However, when we looked at the data, some records were not imported correctly. The records seemed to have the data offset by one field. We found the problem to be records with either quotes or commas in the data. Since these were also our delimiters and seperators we changed the Suprtool and STExport commands to the following:
get m-customer out mcust,link xeq export in mcust export quote none export delim "?" export out mcustexp export exitWe got rid of the quotes surrounding each field by using the Quote None statement and changed the delimiter to a character that we knew did not exist in our data. In our case we chose the Question Mark.
Importing the data into our table then became a single simple command entered from within Mysql Monitor:
load data infile 'm_customer_file.txt' into table m_customer fields terminated by '?';Once we had the basics down for importing the datasets into the mySQL tables, we could then import the entire database into mySQL.
We were able to check the data using the mySQL admin tools, but for more flexible access we installed some PHP scripts for working with mySQL databases.
Overall, we found it relatively easy with STExport to duplicate our production IMAGE database structure and contents in mySQL. Of course, we did do a little studying in two books:
To help both the customer and myself write the report, I pasted in the current version of the report and edited the report using Qedit for Windows.
What I found really helpful was that by just turning on the View "Ruler Bar" I could easily see the layout and number of spaces for the Header lines.
Once this portion was designed, I could then use the View "Record Numbers" feature to count the number of lines in the report.
Once I had the design done, I could also test the line spacing and layout by printing the report to the screen and cutting and pasting into Qedit for Windows to insure that everything lined up and the Line counts were correct.
I realize that this is very "old school" but there are still some of us who do high volume, good old data processing and printing reports and using this newfangled Qedit for Windows provided a nice trick for this old programmer.
neil@robelle.com
Suprtool added the Update-From-Table feature in version 4.4.10. You can read about this flexible new option in this NewsWire article.
Many applications keep a running total in the master record of all the detail transactions. You can use the latest feature in Suprtool to easily do this.
The following example reads the D-SALES detail set and summarizes the total sales for each customer.
Update Your Account
>base store,1,writer
>get d-sales
>sort cust-account
>duplicate none keys total sales-total
>extract cust-account
>output summary,link
>xeq
>input summary;list standard;x
Aug 23, 2001 11:56
CUST-ACCO ST-TOTAL-1
123 136193+
4566 723309+
IN=2, OUT=2. CPU-Sec=1. Wall-Sec=1.
Now let's update the master dataset records. Notice the new syntax on the TABLE and the EXTRACT commands.
>table t-sales,cust-account,file,summary,data(st-total-1) >get m-customer >if $lookup(t-sales,cust-account) >update >extract sales-history = & $lookup(t-sales,cust-account,st-total-1) >xeqFinally, here is what the Master set records look like:
>g m-customer;ext cust-account,sales-history;l;x >GET M-CUSTOMER (134) >OUT $NULL (0) CUST-ACCOUNT = 4566 SALES-HISTORY = 723309 >GET M-CUSTOMER (187) >OUT $NULL (1) CUST-ACCOUNT = 123 SALES-HISTORY = 136193
paul.gobes@robelle.com
You can do it in Suprtool, but there is a trick to it. Click the link above to find out how.
Robelle Solutions Technology Inc. provides the What's Up, DOCumentation? newsletter as a service to our customers. The newsletter is available on the web: in HTML format for browsing and PDF format for printing. Visit www.robelle.com/library/newsletter/
Comments about this issue, as well as ideas for future issues, including news, tips, and suggestions for articles, can be sent to bgreen@robelle.com.