ScriptWiz

ScriptWiz is a utility provided to help you produce a basic script file without having to learn the GDIdb script language. Six different script types are supported.

After running ScriptWiz, you will have a complete and working script file, although (if you are generating a web site script) you will probably want to add some further HTML to the script to enhance the appearance of the web pages generated when the script is run. (to do this, open the script file with your text editor after ScriptWiz has finished.)


Script type

ScriptWiz can produce six different types of script. The script produced by the first four options on the dialog will create a full web site from your database data. The fifth option creates a script that will generate a text export file from your data- use this option (in combination with a server CGI script) to create a database on your web server synchronized with the database on your desktop P.C. The sixth option creates the server CGI and GDIdb script files required to get data submitted on a web page form back into a database on your desktop PC.

Options 1-4 (web publishing scripts)

If you are not certain exactly what script type you require, the best thing to do is to experiment, starting with the first option (which is the easiest to understand) and working down to the fourth option on the list (which is the most complex)

The script file produced by script Wizard may be edited later to fine-tune the output, either by just adding HTML tags to control the visual design or by adding further GDIdb script functions to control the way the HTML is produced. See the GDIdb on-line help for further info on script functions.

Single page web site

Multiple page web site

Basic relational database web site

Advanced relational database web site

CSV file database table export

Web form data retrieval


Script File Name

ScriptWiz uses the information that it will gather from you to generate a GDIdb script file- enter the file name you would like to give this file, which will be placed in the GDIdb program directory. The default name (which you can leave or change) is “script”

After running ScriptWiz you can open the script file from your text editor by selecting Edit/Edit script file from the GDIdb menu.

Note: If a file of the same name already exists, it will be overwritten without warning!


Top-Level HTML document Name

When you run your script, there may be many HTML documents produced by GDIdb whose names will be generated automatically. The top-level page however, (containing either the datasource data or links to further pages) needs to be given a name. The default name (which you can leave or change) is “index.html”


 ODBC DSN or Database file?

GDIdb can work with either an ODBC Data Source Name (DSN) or directly with a database/spreadsheet file. Select the option you require.

If you would like more information on ODBC, see the section on ODBC in GDIdb on-line help under “Getting started”.

Note:

If you are creating a database publishing script, this is the database where your web data will come from. If you are creating a data retrieval script, this is the database where your web form data will be stored.


Database Filename/Login Information

Enter (or browse for) the pathname to your database or spreadsheet file. If access to the database is restricted, you will need to enter a login name and a password- these will be included in the script file created by ScriptWiz.

If a password and/or login name are required but not supplied at this point, a dialog requesting the missing information will open when you attempt to move to the next page of the script wizard. Details entered into this dialog will not be included in the script file- so this dialog will open each time the script itself is run by GDIdb. (which is probably not what you want)

ScriptWiz will attempt to select the correct ODBC driver for your datasource automatically, if this operation is unsuccessful a dialog will open when you attempt to move to the next page of the script wizard requesting that you choose an appropriate driver from the list of drivers installed on your machine. It should be apparent (from the name of the drivers) which one you require. Check the "Select ODBC driver manually" box if you do not want ScriptWiz to automatically select an ODBC driver.

Notes:

1. If you are creating a database publishing script, this is the database where your web data will come from. If you are creating a data retrieval script, this is the database where your web form data will be stored.

2. Some database systems (such as .db, .dbf & .txt) require that path to the directory containing the file, rather than the complete file path be entered. If Script Wizard recognizes the database as one of the above, it will automatically strip the filename off the path that you enter. (The following dialog will allow you to select the file itself)

If you experience difficulties when moving on to the next ScriptWiz dialog, you may need to remove the filename from the File Path edit box, e.g.

selected file:

C:\Program Files\DevStudio\VFP\SAMPLES\TASTRADE\DATA\Behindsc.dbf

remove the filename, leaving the following path in the edit box:

C:\Program Files\DevStudio\VFP\SAMPLES\TASTRADE\DATA

The following Script Wizard page will allow you to select the file itself.

3. Excel Spreadsheet files- Before you can extract data from an Excel spreadsheet, you need to have a 'named' area of the spreadsheet defined. This named area will be used as your data table by the ODBC driver. Create a named area as follows:

•	Select the area on your spreadsheet that contains the data you wish to publish.

•	Select Name/Define under the Excel Insert menu.

•	Think of a name to give your table and enter it in the dialog which opens up.

When you move onto the next Script Wizard page, you should see this name in the 'Tables' list box.


ODBC Data Source Name

GDIdb uses Open Data Base Connectivity (ODBC) to work with your datasource, either using an ODBC Data Source Name (DSN) or directly with your datasource file.

ScriptWiz will display all of the ODBC DSN’s that have been defined on your system in a list box- select the one that is connected to the datasource whose contents you wish to work with. If you have not created a DSN for your spreadsheet/database, you will need to quit ScriptWiz and run the ODBC administrator,(located under My Computer/Control Panel) or extract the data directly from your datasource file. (Click on the dialog Back button and choose Database/Spreadsheet file)

If you are accessing a secure database, enter your database user name and password.

See the GDIdb help for further information on setting up ODBC.

Note:

If you are creating a database publishing script, this is the database where your web data will come from. If you are creating a data retrieval script, this is the database where your web form data will be stored.


Datasource Table

The list box contains all of the tables in your datasource that ScriptWiz could find. Select the one that contains the data that you wish to extract into your web site. If you entered a file of type .txt .csv .db or .dbf in the previous dialog, this listbox will contain all of the files of that type in the same directory as the file you selected. Select the file you wish to extract data from. (This will be the same file as you selected in the previous dialog.)

Relational database structures only:

If you selected a basic or advanced relational database structure (Radio buttons 3 & 4 on page 4 of the ScriptWiz dialog) , this will be the data table and will hold the data which will be grouped into categories stored in the category table that you were asked to select previously.

Note: If you received an error when you moved on to this dialog, it is possible that the ODBC DSN or datasource file that you selected in the previous dialog is not valid. Check Database Filename/Login Information help topics for more information.


Table Fields

The list box contains all of the fields in the chosen datasource table. Select the fields that contain the data that you wish to work with in your script and move them into the listbox on the right hand side by clicking the [>>] button.

Note:

If you are creating a database web publishing script, these are the fields that will appear on your web page. If you are creating a data retrieval script, these are the fields that will hold the data retrieved from your web form.

Relational database web site scripts only:

If you selected a basic or advanced relational database web site (Radio buttons 3 & 4 on page 4 of the ScriptWiz dialog), the fields that you select here are taken from the data table.

The data records will be grouped into categories stored in the category table that you were asked to select previously.

CSV file database table export script only:

The database fields that you select will appear as columns in your CSV files in the order that they appear in the dialog.


HTML Formatting

ScriptWiz can include:

HTML table formatting tags placed around the data that GDIdb extracts from your datasource. Leave this checkbox blank if you'd rather format the data yourself.

Forward/Backward links on the data pages. These links will provide the hyperlink equivalent of Database record Forward/Backward buttons at the bottom of each page.

A link back to the category or top-level page.

(The second two checkboxes will be disabled if you selected single HTML page on the web site structure dialog.)


Finished!

Click finish and ScriptWiz will write your script. After running ScriptWiz, you will have a complete and working script file, although you will probably want to add some further HTML to enhance the appearance of the web pages generated by running the script (script types 1-4 only). Select Edit/Edit Script file from the GDIdb menu (or leave the checkbox at the bottom of the final ScriptWiz dialog ticked) to open your newly-created script file for editing, the places in the script file where you are most likely to want to add HTML are marked with TODO comments.

If you wish to test-run your script file immediately, select Actions/Open Run Script from the GDIdb menu and double-click on the script name.

Script types 1-4 only

To preview the HTML generated by your script, select Edit/Preview web site from the GDIdb menu or open the top-level HTML document in the html directory. (which is a sub-directory of the GDIdb program directory)

Script type 5 (CSV export) only

View the CSV files generated by GDIdb by selecting Edit output files from the GDIdb Edit menu.

See the GDIdb on-line help for information on the script language- with an understanding of how GDIdb script language works there are many features that you can add to your web site that are not included in a standard ScriptWiz script.


Link Field Selection

The type of script that you are constructing will build a web site structure where one field from your datasource table is used as a hyperlink to a separate page that includes further fields from the same datasource table.

Select the field in the datasource table whose contents you wish to use as a HTML hypertext link to the second-level HTML pages. (The second-level HTML pages will probably contain the primary data that you are publishing on your web site)


Category page formatting

ScriptWiz can include Forward/Backward links on the category pages. These links will provide the hyperlink equivalent of Database record Forward/Backward buttons at the bottom of each category page. In addition, ScriptWiz can add a link back from the category pages to the top-level document. Check the box if you would like these links included.


Top-Level Category Table

The type of script that you are constructing (basic or advanced relational database structure) requires two datasource tables. The first table (the category table) is assumed to contain a list of categories, under which entries in the second table (the data table) are grouped. At this point you need to select the category table.

If you entered a file of type .txt .csv .db or .dbf in the previous dialog, this listbox will contain all of the files of that type in the same directory as the file you selected.

Note: If you received an error when you moved on to this dialog, it is possible that the ODBC DSN or datasource file you selected in the previous dialog is not working correctly.


Contents Hyperlink Field

The type of script that you are constructing (basic or advanced relational database structure) requires two datasource tables. The first table (the category table) is assumed to contain a list of categories, under which entries in the second table (the data table) are grouped.

At this point you need to select the field in the category table whose contents you would like to use as a HTML hyperlink to the pages generated from the data table. (These pages will have records grouped under the link category.)


Relational Key Field

The type of script that you are constructing (basic or advanced relational database structure) requires two datasource tables. The first table (the category table) is assumed to contain a list of categories, under which entries in the second table (the data table) are grouped.

The list box shows all of the fields in the category table, at this point you need to select the field that forms the relational key with the data table.

Note: ScriptWiz will attempt to construct an appropriate SQL query based on the data type of the field you select. Numeric and text data types are supported.


Relational Key Field

The type of script that you are constructing (basic or advanced relational database structure) requires two datasource tables. The first table (the category table) is assumed to contain a list of categories, under which entries in the second table (the data table) are grouped.

The list box shows all of the fields in the data table, at this point you need to select the field that forms the relational key with the category table.


CSV output file details

Enter the file name you'd like to give the CSV output file. Do not add a filename extension, GDIdb will automatically add the extension ".txt".

Multi-file output

Select this option to produce a separate file for each row in your database table. The following dialog will ask you to select the database key field- the contents of this field will be combined with the file name to produce a unique name for each file produced.

Name files sequentially

If your database table does not contain a unique key field, select this option. GDIdb will create each filename by combining the current table row number with the CSV file name.

Create index file

If you have selected "multi-file output" you will almost certainly want GDIdb to create an index file. This file will contain the names of all CSV files generated and will be uploaded to the web server along with the CSV files on a publish operation. The file will be named using the CSV file name, but with the extension ".idx".


Select key field

Select the database table key field (ideally, this should be a numeric field). GDIdb will create a file from each row in your database,  the filename for each file created  will be formed by combining the CSV file name with the contents of this field.


Data retrieval project name

Enter the name for your data retrieval project. ScriptWiz will create 4 project files from this name, these will be written to the current web project directory.


Select datasource table

Select the datasource table in which you wish to store your form data.


Web server details

CGI directory

Enter the path to your CGI directory. This path will be appended to the CGI script file name in the HTML form ACTION value.

E-Mail program

Enter the path to the program used to send e-mail from your web server. If you are not sure what this should be, try leaving it as the default which will work in most instances. If it does not work, you'll need to contact your ISP to find out the correct path.

"Data Accepted" page URL

Enter the URL for the page that is to be loaded after the form data has been submitted by the web surfer. Typically this page is used to thank the web surfer for his/her submission. You need to create this page yourself.


E-Mail account details

E-Mail address

The web server will e-mail data submitted on your web site to an e-mail address for later collection by GDIdb. Enter the e-mail address to which the web server should mail the data.

POP3 Server/logon/password

GDIdb will collect the e-mails sent above from the mail server using the POP3 mail protocol. You need to enter the POP3 mail server domain name or IP address, together with the mailbox logon name and password.


Finished!

Click Finish to generate the project files. A text file will also be generated (called 'projectname'.txt) which contains further information on setting the project up. Check the checkbox if you would like to read this file now.


Single page web site

Select this option to produce the most basic type of script file. Data will be extracted from a single datasource table and inserted into a single HTML file.

If you just want to include your datasource table as a simple html table in your web site, this is the best structure to choose- the script “foodstore.scp” provided with GDIdb is an example of this type of script.

Example:

The following database table contains a list of food items. A Single page web site script can include this information directly into the web page as a HTML table.

Database Table

ITEM	PRICE	STOCK
Coffee	£2-50	12
Oranges	£0-35	75
Bread	£1-20	56


Multiple page web site

If you select this option, the HTML structure produced will be a top-level HTML document containing a list of links which are created from one of the columns in the table. These links point to further pages which may contain data from any field in the same row as the link field. Script Wizard will prompt you for the column in the datasource table to be used as an HTML hyperlink to the second-level HTML pages.

If you have a datasource table that contains large text fields, this may be preferable to a single page web site script, as it allows the contents of the large text fields to be put on separate pages.

Example:

The following database table contains a list of food items. A Multiple page web site script can produce a top-level links page, using the text from column 1 as the link text. Each of these links will point to a further HTML page that can contain more detailed information about the item (e.g. the data from columns 1,2,3 and 4)

Database Table

ITEM	STOCK	PRICE	DELIVERY
Coffee	12	£2-50	£1.23
Oranges	65	£0-35	£1-23
Bread	675	£1-20	£1-23
Jam	32	£0-55	£1-23


Basic relational database web site

You need to understand a bit about how relational databases work to use this option, which assumes that you have two database tables, the category (or "one" table) table and the data (or "many") table. The category table contains a list of category names and the data table contains a list of data items, each one of which belongs to one of the categories in the category table. The two tables each have a field (called the key field) that is used to form the relationship between the two. (This relationship is called a "one to many" relationship.)

The web site created by the script will consist of a top-level links page, with one link for each of the entries in the category table. These links will point towards further pages that will contain data from the data table, grouped under that link’s category. (Only data table records where the data table key and the category table key match will be included on each of these pages.)

Example:

The following two database tables can be used to generate a top-level links page with 3 links. (The actual link text will be: Vegetables, Poultry and Fish) The page linked to the Vegetables link will contain information on the items Potatoes, Peas and Carrots, the page linked to the Poultry link will contain info on Chicken, Pheasant and Duck and the page linked to the Fish link will contain info on Salmon. Notice how the key field relates the two tables. (All vegetables have a key value of 1, etc..)

Category Table:

KEY	FOOD TYPE
1	Vegetables
2	Poultry
3	Fish

Data Table:

KEY	FOOD	PRICE
1	Potatoes	£0-45
2	Chicken	£2-34
1	Peas	£0-33
3	Salmon	£8-78
2	Duck	£5-91
2	Pheasant	£7-42
1	Carrots	£0-46


Advanced relational database web site

You need to understand a bit about how relational databases work to use this option, which assumes that you have two database tables, the category (or "one" table) table and the data (or "many") table. The category table contains a list of category names and the data table contains a list of data items, each one of which belongs to one of the categories in the category table. The two tables each have a field (called the key field) that is used to form the relationship between the two. (This relationship is called a "one to many" relationship.)

The web site created by the script will consist of:

1. A top-level links page, with one link for each of the entries in the category table.

2. Category links pages, (linked to the top-level page) one for each of the entries in the category table. The category pages will themselves consist of links, the link text is taken from one of the fields in the data table. Only data table records where the data table key and the category table key match will be included on each of these pages as links.

3. Data pages, one for each of the entries in the data table. These pages will be linked from the appropriate category links page and can contain any or all of the fields from the data table.

Example:

Using the database tables below, the following HTML structure can be produced:

A top-level links page with 3 links. (The actual link text will be: Vegetables, Poultry and Fish) The page linked from the Vegetables link will itself contain more links. (The actual link text will be Potatoes, Peas and Carrots) These links will point to further pages that contain detailed information on the items Potatoes, Peas and Carrots. (e.g. both the FOOD and PRICE fields)

(The same structure will be repeated for Poultry and Fish)

Category Table:

KEY	FOOD TYPE
1	Vegetables
2	Poultry
3	Fish

Data Table:

KEY	FOOD	PRICE
1	Potatoes	£0-45
2	Chicken	£2-34
1	Peas	£0-33
3	Salmon	£8-78
2	Duck	£5-91
2	Pheasant	£7-42
1	Carrots	£0-46

The example script “workweb.scp” provided with GDIdb is an example of this type of script.


CSV file database table export

If you want to create a flat-file database on your web server whose contents are synchronized with a database on your local P.C, this type of script provides a good way to do it. Two basic file structures are supported- the first is a simple CSV export of the database table. The disadvantage of this method is that any changes to the data in the table will result in the whole table being uploaded on a GDIdb publish operation. For this reason we recommend that you use a "multi-file output" file structure. When GDIdb runs a script of this type, a new file will be created for each row of data in the table. The filename of each file will be generated from the database key field. Using the GDIdb changed-only upload feature, you can then add/delete records from the database table- on a GDIdb publish operation, only those records in the table which have changed will be uploaded to the web server.

To help your CGI script process the files, the script can also produce an index file. This text file will contain a list of all the file names created.


Web form data retrieval

GDIdb can be used to automatically get data submitted on a form on your web site back into your local database- ScriptWiz will generate all of the files you need to do this.

What you'll need

A web form data retrieval project will require the following:

1. A spare POP3 e-mail address. Data from the web form is delivered to GDIdb by e-mail, so you will need a spare POP3 mail box which GDIdb can use for the project.

2. Web space with a cgi-bin directory. One of the files created by ScriptWiz is a Perl CGI script which must be copied to your web server CGI directory. You need to be able to run Perl scripts on your server for this to work.

Description

ScriptWiz will generate 4 files. These are:

1. The HTML form page. You will probably want to add some html to this file to enhance the appearance of the submission form, even though the form page is fully functional and may be uploaded directly to your web server HTML directory.

2. A web server Perl script. This script accepts the data from the above form, and e-mails it to a POP3 mailbox.

3. A GDIdb script file. When run, this script will collect all e-mails that are waiting in the POP3 mailbox, process the data they contain, and append the data in each e-mail onto your database table as a new record.

4. A help text file giving instructions on installing the script files.


GDIdb turns the data in your database/spreadsheet into HTML documents by executing a script file. This file is effectively a template describing the web site that will be constructed from your data and consists of HTML and functions telling GDIdb what database data you wish to include. Run Script Wizard (under Utilities) to create a basic script file.