PPWIZARD is a free preprocessor for HTML, REXX, Visual Basic or any text files.
[Bottom][Contents][Search][Prev]: TEXTEDIT with PPWIZARD[Next]: Loading XML into a DOM (in windows)
\ -> Examples / Tips -> Accessing SQL DATABASES Directly

Accessing SQL DATABASES Directly

Note that PPWIZARD has an #import command that can probably handle most SQL requirements but the samples shown here are useful if you have more complex requirements or simply as samples for the facilities used.

These examples use Mark Hessing's RexxSQL library to access the database program. Its free and is available for multiple operating systems and can access many different database programs. its available from "http://rexxsql.sourceforge.net/".

Note that the routine ErrorSQL() has been provided specifically for your use when accessing SQL databases directly as shown here.

Example #1

This example was developed and tested under Windows 2000, accessing a Microsoft Access 2000 database. While it shows how to obtain the rows it is a bit simplistic in that all it does is display the description column of each row matching the query. In real life you would probably be creating a HTML table.

#NextId
#DefineRexx ''
   ;--- Load REXXSQL --------------------------------------------------------
   call LoadRexxSql

   /*--- Turn on debug (to maximum) --------------------------------------------*/
   @@VersionInfo = SqlVariable("VERSION")
   say @@VersionInfo;
   say copies('~', length(@@VersionInfo));
   call SqlVariable "DEBUG", 3

   /*--- Connect to ACCESS database -----------------------------------------*/
   @@Id           = "ConnectID";
   @@UserId       = "";
   @@Password     = "";
   @@DataSourceId = "PHASE2";           /* Defined in WIN32 "Data Sources ODBC" */
   @@Server       = '';
   if  SQLConnect(@@Id, @@UserId, @@Password, @@DataSourceId, @@Server) < 0 then
       ErrorSql('Connection failed to "' || @@DataSourceId || '", have you set up an ODBC datasource (control panel)?');

   /*--- Specify the query --------------------------------------------------*/
   @@Select = 'SELECT * FROM Department ORDER BY Department.DepartmentDescription';

   /*--- Prepare query ------------------------------------------------------*/
   if  SqlPrepare('SQLQUERY', @@Select) < 0 then
       ErrorSql();

   ;--- Fetch first record --------------------------------------------------
   if  SqlOpen('SQLQUERY') < 0 then
       ErrorSql();
   @@FetchRc = SqlFetch('SQLQUERY');

   ;--- Work through all rows -----------------------------------------------
   do  while @@FetchRc > 0
       call Say 'Description =' || SqlQuery.DepartmentDescription
       @@FetchRc = SqlFetch('SQLQUERY');
   end
   if  @@FetchRc < 0 then
       ErrorSql();

   ;--- Close the query -----------------------------------------------------
   if  SqlClose('SQLQUERY') < 0 then
       ErrorSql();
   if  SqlDispose('SQLQUERY') < 0 then
       ErrorSql();

   /*--- Disconnect from the database ------------------------------------------*/
   if  SQLDisconnect(@@Id) < 0 then
       ErrorSql('Disconnection from database failed!');
#DefineRexx

Example #2

This example shows you how to access data using the "mSQL" (free) database program under OS/2. The database we will process is called "supersite" and the table we will access is "link".

You may want to have a look at the #evaluate routines AsIs() and AutoTag().

mSQL EXAMPLE

;--- Very simplistic start of HTML ------------------------------------------
<HTML>
<BODY>
<H1>Example - Create Links List from SQL Query</H1>

;--- Initialization ---------------------------------------------------------
#define QueryCategory 3             ;;This query is for this category
#define LinkTemplate <P><A HREF="{$url}">{$title}</A>: {$description}  ;;HTML for each record (note that it would be more efficient (but less "clean") to just pick up rexx variable names here

;--- Enable PPWIZARD to make use of external SQL library --------------------
#evaluate  ''  "call LoadRexxSql"

;--- Connect to the data base ("supersite" on local machine) ----------------
#evaluate 'ConnectRc' "SQLConnect('PPW',,, 'supersite', 'localhost')"

;--- Perform a database query (on the table "link") -------------------------
#evaluate+ 'QueryRc' ^SQLCommand('RxLink', 'SELECT * FROM link WHERE category = <$QueryCategory>')^
#info      'QueryRc = <$QueryRc>'
#info      'Number of records is <??RxLink.url.0>'

;--- Generate the html (for simple list) ------------------------------------
<UL>                            ;;Start of list
#RexxVar RxCount = 1            ;;Initialize record counter (rexx variable)
#{                              ;;Start of loop
   #if ['<??RxCount>' <= '<??RxLink.url.0>']
       ;--- Use previously defined macro (template) to generate output ------
       <$LinkTemplate url="<??RxLink.url.RxCount>" title=^<??RxLink.title.RxCount>^ description=^<??RxLink.description.RxCount>^>
       #RexxVar RxCount + 1     ;;Update record counter
   #elseif
       #break
   #endif
#}                              ;;End of loop
</UL>                           ;;End of list

;--- Disconnect the data base -----------------------------------------------
#evaluate 'DisConnectRc' "SQLDisconnect('PPW')"

;--- Very simple end of HTML ------------------------------------------------
<P>
<HR>
<CENTER>End of simple SQL IMPORT Example</CENTER>
</BODY>
</HTML>


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

[Top][Contents][Search][Prev]: TEXTEDIT with PPWIZARD[Next]: Loading XML into a DOM (in windows)


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