| #import - SQL |
This type of #Import directly accesses SQL databases such as MS Access 2000, MS SQL Server, mSQL or DB/2.
It requires a free 3rd party product called REXXSQL which can be obtained from Mark Hessling's site at "http://rexxsql.sourceforge.net/". To install RexxSQL you will need to follow its instructions but under Windows I simply copied the 3 DLLs into the system directory. PPWIZARD will inform you if it has problems finding the DLL. I have added a small tips section below...
This command is of course only supported on those operating systems that "RexxSQL" runs on (currently Windows, OS/2 and Unix). Check out Mark's pages for more details on the databases supported however this is a list (may not be complete) of databases known to work with RexxSQL:
This form of SQL import should be able to handle all or nearly all of your needs but if you need something more complex then you should check out the accessing SQL in rexx page.
If debug mode is on a lot of SQL related information is generated including a dump of all column information available on the columns returned by your query. PPWIZARD's debug mode (by default) also turns on REXXSQL debug so it should be quite easy to diagnose any problems you might have.
| Field Information Parameters |
The SQL import requires "FieldInfo" following the "DefineName" parameter.
You must specify field information for each column you want to handle from the data returned by your SQL query.
The field information is of the format "{ColName}TitleText".
The optional "ColName" parameter specifies the column name returned by your query while "TitleText" specifies the value for the title in the header record.
If a "ColName" is not specified then the "TitleText" is used for the column name as well. This would probably only be done if you either did not care about the title text in the header or you did not have a header.
| DEFINITIONS/OPTIONS |
If you can't understand how these options work then I suggest you try using /debug or #debug to watch what variables the import uses etc.
To save the data to memory you need to supply the name of the #data structure, you refer to this when generating the output with the <?Data> symbol. If used a lot of the other definitions are not used, if unsure have a look at the debug output.
For even more control (probably rare requirement):
You can set this on a field by field level if required:
If your database contains preformatted HTML code (or references PPWIZARD macros) then this default handling would prevent this from working so you would probably want to turn this off, on the other hand you may wish to extend this to other characters (possibly international characters such as umlauts).
This definition lists zero, one or more names as used on previous "#AsIs SETUP" commands (separated by whitespace). Each is added in turn.
To prevent all character conversions you should assign an empty value to this parameter.
If this define is not used then you can use the following:
If this define is not used then you can use the following:
Normally all records are displayed. A filter can examine all column variables and modify them or tell PPWIZARD to ignore the record. The filter is not called for the heading record.
The following rexx variables and functions are relevant:
If the contents starts with 'EOF:' then the current record and ALL following are dropped.
If all your records are processed the same way then you should not need to modify this variable. It is useful where you might want the output (row of table) to look different depending on the records data. In some cases this can be better done by updating the rexx "Column.?" array.
If you need multiple lines you can of course use "<?NewLine>" where required.
Note that any macros or other symbols are replaced once at the start of the import and not for each field.
| Example - Import From MS Access 2000 (ODBC) |
Here is some sample code which accesses a access database via ODBC and creates a table of the contents (all default look and feel):
;--- Specify the query (this determines the rows and their columns) ---------
#define IMPORT_SQL_QUERY \
SELECT * FROM [FullDetails] \
WHERE DeptSeqNo > 4 and DeptSeqNo < 11 \
ORDER BY Department.DepartmentDescription
;--- Perform the SQL import -------------------------------------------------
#define IMPORT_SQL_DATABASE PHASE2
#import "" SQL "" \
"{DepartmentDescription}Department's<BR>Description" \
"{DeptSeqNo}Department<BR>Sequence<BR>Number"
The first parameter on the import command is normally a filename but for SQL this parameter should be "".
The following shows how you could have executed a couple of commands prior to the above query to generate the view that the above uses (not the best way, but good for an example):
;--- Define a query (probably better hardcoded in database but...) ----------
#define DELETE_VIEW \
-drop table FullDetails
#define CREATE_VIEW \
CREATE VIEW FullDetails AS \
SELECT * \
FROM Department
;--- Do the above 2 commands after connecting to the database ---------------
#define IMPORT_SQL_COMMANDS \
DELETE_VIEW \
CREATE_VIEW
| Example - Import From MS Excel Spread Sheet (ODBC) |
This was tested under Windows 2000 using Excel 2000. You need to do the following (otherwise it is like any other SQL import):
Not being an Excel expert in my testing I hard coded a range, this would be a problem if you added records which extended the number of rows past the end of the range. You could make it cover a really large number of rows and have the SQL query drop blank records or if you know how to define the range better then please let me know!
Your query might look like:
SELECT * FROM ODBC_TABLE
An example of how things get setup in Excel follows:
| Example - Import From Comma Seperated Value File (ODBC) |
This was tested under Windows 2000 and Windows 98. You need to do the following (otherwise it is like any other SQL import):
Your query might look like:
SELECT * FROM simple.csv WHERE AGE > 10 ORDER by AGE
An example of how to set up the ODBC datasource follows:
| Example - Create One Page Per Record From Template) |
In this case we wish to read an SQL database and generate a page for each record using a template file. The template file refers to SQL data via PPWIZARD macros which we will get the import to set up.
;--- Specify database (WIN32 ACCESS DATABASE defined in Control Panel -> ODBC Data Sources) ---
#define IMPORT_SQL_DATABASE DefinedInControlPanelOdbc
;--- Specify the query (this determines the rows and their columns) ---------
#define+ IMPORT_SQL_QUERY \
SELECT * FROM inventory_test \
ORDER BY short_description
;--- Set up the format of the data the import will generate -----------------
#define IMPORT_HEADER
#define IMPORT_BEFORE
#define IMPORT_AFTER
#define IMPORT_PROTECT_START
#define IMPORT_PROTECT_END
#define IMPORT_RECORD \
;--- Create PPWIZARD macros from SQL record's data --- -\
#define+ item {$Column1} -\
#define+ desc {$Column2} -\
#define+ price_r {$Column3} -\
#define+ price_o {$Column4} -\
#define+ desc_s {$Column5} -\
-\
;--- Create new file and include HTML template ------- -\
#output "<$item>.htm" ;;New HTML file -\
#include "master.htm" ;;Include template -\
#output ;;Close file
;--- Perform the SQL import -------------------------------------------------
#(
#import "" SQL ""
"{product_no}Product Number" ;;Column 1
"{short_description}Description (short)" ;;Column 2
"{long_description}Description (long)" ;;Column 3
"{price_4}Price 4" ;;Column 4
"{our_retail}Our Price" ;;Column 5
#)
| Example - Simplest CSV Import |
This is just about the simplest example you are likely to see just in case the above has confused you!
#define IMPORT_SQL_DATABASE C_TMP_CSV ;;Name of configured ODBC Data Source
#define IMPORT_SQL_QUERY SELECT * FROM TestSQL.csv ;;ODBC datasource specifies directory where CSV is...
#import "" SQL "" \
"{Field1}Html Field #1" ;;Name and description of field 1 \
"{Field2}Html Field #2" ;;Name and description of field 2
| TIPS |
The following files from the ODBC version of the Windows binaries were used by me (this might change from version to version):
I do not believe that these need to be in the system directories, certainly they appear to work fine in the current directory (I assume the PATH is searched otherwise).
You can run the REXXSQL utility test program "rexxsql.exe" (without parameters), it will tell you if some DLLS are missing (expect to see the correct syntax displayed).
If you have problems it might be worth while to run the test rexx programs supplied outside of PPWIZARD to try to isolate the problem.