PPWIZARD is a free preprocessor for HTML, REXX, Visual Basic or any text files.
[Bottom][Contents][Search][Prev]: #import - Multi Line Records[Next]: #import - T2H
\ -> Commands -> #import -> #import - SQL

#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.

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):

  1. As for all ODBC imports you need to define the database as a "ODBC Data Source".

  2. The first row of the spread sheet needs to contain the SQL field names.

  3. 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:

Setting up an Excel table

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):

  1. As for all ODBC imports you need to define the database as a "ODBC Data Source".

  2. The first line of the text file must contain the SQL field names.

  3. 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:

Setting up an ODBC Datasource

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):

  1. REXXSQL.DLL
  2. REXXODBC.DLL
  3. 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.


email me  any feedback, additional information or corrections.
See this page online (look for updates)

[Top][Contents][Search][Prev]: #import - Multi Line Records[Next]: #import - T2H


PPWIZARD Manual
My whole website and this manual itself was developed using PPWIZARD (free preprocessor written by Dennis Bareis)
Sunday November 05 2017 at 10:00am