PPWIZARD is a free preprocessor for HTML, REXX, Visual Basic or any text files.
\ -> Commands -> #import -> #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:
- IBM's DB2
- EasySoft ODBC-ODBC Bridge
- Generic ODBC (probably covers most!)
- Microsoft Access
- Microsoft Excel spreadsheets
- Microsoft SQL Server
- CSV (and other text formats)
- FoxPro
- Paradox
- DBase
- Mini SQL (1.0.16, 2.0+)
- My SQL
- Openlink UDBC
- Oracle (7.x, 8.x)
- PostgreSQL
- Raima Velocis
- Solid
- Sybase System 10/11
- Sybase SQL Anywhere
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.
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.
- DefineName_#DATA
Normally PPWIZARD generates the information as per your
specification
(you preconfigure this or allow it to default).
You can also set this macro to have the data read into
memory as per the #data command, it is then
up to you to generate each database row (possibly after field
manipulation).
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.
- DefineName_SQL_DEBUG
This value is used to set the RexxSQL "DEBUG" variable.
It only takes effect when debug mode is on and by default
all available information is output (use "0" for none).
Note that RexxSQL debug output is not captured by
/ConsoleFile but must be redirected.
- DefineName_SQL_USERID
Your database may require a user ID for access.
- DefineName_SQL_USERPW
Your database may require a password for access.
- DefineName_SQL_DATABASE
This specfies a database.
It probably does not specify the database file name but
the value has probably been defined somewhere, for example
under Windows 2000, to access a database via ODBC the
name used here must first be defined in
"Start->Control Panel->Administrative Tools->Data Sources (ODBC)".
- DefineName_SQL_SERVER
Another field which might be required to establish a database
connection.
- DefineName_SQL_COMMANDS
Use this option to execute SQL commands after a connection has been
established but before your query has been run.
You specify a list of macros (which contain the actual commands)
separated by spaces.
A command should begin with "-" if errors from it
should be ignored.
- DefineName_SQL_QUERY
This is the actual query itself.
- DefineName_BLANK_FIELD
Normally a blank field will be displayed as blank.
You may wish to display '-' instead.
Another possiblity is to display a 1 by 1 transparent gif so as
to have table borders around blank fields look better.
For even more control (probably rare requirement):
- DefineName_BLANK_COLUMN_?
This allows you to specify different blank replacement
values for each column.
- DefineName_STRIP_SPACES
How do you want leading and trailing spaces on column data handled?
Supply "L" to strip leading, "T" to drop trailing, "B" for both
or nothing to not touch the value.
The default for fixed field imports is "T", all others are to do nothing.
You can set this on a field by field level if required:
- DefineName_STRIP_SPACES_?
Set the required value for a specific column.
- DefineName_DROP_BLANK_LINES
You can specify whether or not blank lines (or lines where all fields are blank) are significant. The default is 'Y' to drop blank lines, specify 'N' to prevent this.
- DefineName_TAB_CHAR
Normally tabs are ignored by the import process. This variable allows you to replace them with something else.
- DefineName_ASIS_TAGGING
The default value for this option is
"IMPORT_HTML_BASIC"
which takes care of converting the
"&", "<" and
">" characters into their html codes so they
they will display correctly in a browser.
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.
- DefineName_DROP_LINE_COUNT
If we are dropping lines (TAB- command etc), then how many should be
dropped. The default is one.
- DefineName_COLUMN_HEADINGS_ON_LINE_NUMBER
If we need to read the file to obtain column information then on what
line number contains this data?
- DefineName_BEFORE
You would probably only use this define if you didn't want to
generate a table at all. You may specify the string
"{$Columns}" which will get replaced with the number of
fields to be displayed.
If this define is not used then you can use the following:
- DefineName_TABLE_ATTRIBS
This value allows you to specify all HTML attributes of the
table apart from the number of columns.
- DefineName_TABLE_CAPTION_TEXT
The Text of a table caption.
- DefineName_TABLE_CAPTION_ATTRIBS
Any attributes for the HTML "caption" tag.
This is ignored unless text is also supplied.
- DefineName_HEADER
This is used to control the "code" which handles the "heading" record,
this does not have to be a html table.
You can specify the string {$Column?} to
represent a fields value (? = number of field where 1 is the first).
If this define is not used then you can use the following:
- DefineName_HEADING_COLUMNS
This value allows you to specify the column (HTML "<TH>" tags)
information to change alignment or colours of columns.
This value becomes the default for all columns.
- DefineName_HEADING_COLUMN_?
This value allows you to specify the column (HTML "<TH>" tags)
information to change alignment or colours of specific columns.
Another use for this would be to specify the width of a column.
This value is only used for column number '?'.
- DefineName_HEADING_BEFORE_DATA
This value allows you to specify some text to be placed in front
of the fields data, for example if you wish to change the font size
for each column you might used the value
"<FONT SIZE=-1>".
This value becomes the default for all columns.
- DefineName_HEADING_BEFORE_DATA_?
This value allows you to specify leading data on a column by
column basis.
- DefineName_HEADING_AFTER_DATA
This value allows you to specify some text to be placed after
the fields data, for example if you wish to change the font size
for each column you might used the value
"</FONT>" to close the previous font tag.
This value becomes the default for all columns.
- DefineName_HEADING_AFTER_DATA_?
This value allows you to specify trailing data on a column by
column basis.
- DefineName_RECORD
This is used to control the "code" for each record,
this does not have to be a html table.
You would definately define this option if you didn't want a html
table, you might wish to create a series of #defines (default configuration won't allow this)
or maybe you are not generating html at all and need to generate an IPF table.
You can specify the string {$Column?} to
represent a fields value (? = number of field where 1 is the first).
If this define is not used then you can use the following:
- DefineName_RECORD_COLUMNS
This value allows you to specify the column (HTML "<TD>" tags)
information to change alignment or colours of columns.
This value becomes the default for all columns.
- DefineName_RECORD_COLUMN_?
This value allows you to specify the column (HTML "<TD>" tags)
information to change alignment or colours of specific columns.
Another use for this would be to specify the width of a column.
This value is only used for column number '?'.
- DefineName_RECORD_BEFORE_DATA
This value allows you to specify some text to be placed in front
of the fields data, for example if you wish to change the font size
for each column you might used the value
"<FONT SIZE=-1>".
This value becomes the default for all columns.
- DefineName_RECORD_BEFORE_DATA_?
This value allows you to specify leading data on a column by
column basis.
- DefineName_RECORD_AFTER_DATA
This value allows you to specify some text to be placed after
the fields data, for example if you wish to change the font size
for each column you might used the value
"</FONT>" to close the previous font tag.
This value becomes the default for all columns.
- DefineName_RECORD_AFTER_DATA_?
This value allows you to specify trailing data on a column by
column basis.
- DefineName_AFTER
Unless you are not creating a table you are unlikely to want to
change the codes that end the table.
- DefineName_RECORD_FILTER
The contents of this variable should be one or more
rexx expressions.
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:
- Remove
If this variable is set to any non blank value then
the record will be dropped, the variables value is
shown when debugging so it is recommended that the
value be the reason for dropping the record.
If the contents starts with 'EOF:' then the current
record and ALL following are dropped.
- Column.?
The "Column" array holds the data for each field (that you
are interested in) of the current record in the order you
provided. For example "Column.2" holds the 2nd column's
data.
Note that "Column.0" holds the number of fields in the array.
- Dropped.?
The "Dropped" array holds the data for each field (that you
dropped) of the current record in the order that they were
dropped. For example "Dropped.1" holds the first dropped
field.
Note that "Dropped.0" holds the number of fields in
the array.
- ThisRecordsCodes
This variable gets initialized for each record with the
value that you defined (or allowed to default) for the
"DefineName_RECORD"
option. You can add to or modify this record in any way.
The value of "{$Column1}" gets replaced with the contents
of the rexx variable "Column.1" etc.
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.
- WriteLineToTmpImportFile()
The passed data is written to the output file, any line
feed characters will indicate the end of a line.
- RecordFilter
If you don't need to do any more filtering then you can
clear this variable. This will improve performance.
Note that any macros or other symbols are replaced once at the start of the import and not for each field.
- DefineName_KEEP_TMP_FILE
Normally PPWIZARD keeps the temporary file it creates while
importing when debug is on.
This option allows you to specify whether you do or don't
want the file kept (whether debug is on or off).
- DefineName_DO_PASS_2
You would rarely wish to modify this value.
It controls whether or not the generated file is #included
(pass 2), its value defaults to 'Y'.
You might wish to disable the processing if all your processing can
be done in pass one (for example you have imported a database into
memory.
- DefineName_PROTECT_START
By default will be set to the value
<?ProtectFromPpwStart>.
If you have filter code that wants to generate PPWIZARD commands (or macro references) or your data contains these then you will need to override (clear) this value.
Have a look at the
multiple HTML pages
example.
- DefineName_PROTECT_END
By default will be set to the value
<?ProtectFromPpwEnd>.
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):
- As for all ODBC imports you need to define the database as a
"ODBC Data Source".
- The first row of the spread sheet needs to contain the
SQL field names.
- You need to define the sql "table", you do this by defining
a range in Excel (Insert->Name).
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):
- As for all ODBC imports you need to define the database as a
"ODBC Data Source".
- The first line of the text file must contain the SQL field
names.
- The table name in your SQL query is simply the name of the
file you are importing.
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
The following files from the ODBC version of the Windows binaries
were used by me (this might change from version to version):
- REXXSQL.DLL
- REXXODBC.DLL
- REXXTRANS.DLL
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.
PPWIZARD Manual
Saturday May 28 2022 at 2:55pm