Two useful utility scripts
by Roger Harris, October 31, 2004 02:41
Below is a link to a zip file containing two sets of scripts that I will be including in the next release of the registered version. If you would like to test them out and let me know if you find any problems or have any feedback, I'd appreciate it. (If you have any problem with them, it MAY be because you don't have the latest version of odbic.exe, so let me know and I'll send it to you.) There aren't yet any "help" files included in the zip, but they are both fairly straightforward.
The first one is ODBgen.odb (plus all the scripts that start with ODBgen). The ODBgen.odb script will first ask you to enter a DSN, user and password for a database on your system (or you can enter any connection string, if you are using DSN-less connections). The script will connect to that database and execute a "SQL TABLES" command. (This is one possible cause of a problem; you may not have the version that has that special command.) You should see a list of all the tables and views in that database. Each one will be have a link to another script to execute a "SQL COLUMNS" command, which should show a list of all the columns in that table. You can select any of them (or click "Select all"), and you should also click the boxes on the right-hand side for any of them that are to be considered as part of the unique key for the table. When you submit that form, you will get back a page that has several "textarea" boxes with generated code that you can copy and paste (and possibly modify if necessary) into an ODBiC script:
- A SQL SELECT statement that selects all non-key columns with a WHERE clause based on your key columns (e.g. WHERE keycolumn='$keycolumn$', using ODBiC variables having the same name as your key columns).
- A SQL INSERT statement for all of the checked columns (including any keys).
- A SQL UPDATE statement that SETs all non-key columns with a WHERE clause again based on your key columns.
- A <FORM> definition that might be used for inserting data (e.g. with the generated INSERT statement in the target script) or with a QBE command in the target script. The form will have a <TABLE> definition with all the checked columns as <INPUT> fields, where the prompts and the input names will be the same as the column names.
- An ODBiC QBE ("query by example") statement for all of the checked columns, which might be used with the above input form (i.e. used in the target script for the form).
- A <FORM> definition that can be used for updating data, which is similar to the "INSERT or QBE Form" above except that: 1) each <INPUT> will also have a VALUE="$columnname$" attribute to fill in the current values for each of the input variables (i.e you would use a SELECT in front of this form to get the current values for all the columns); and 2) the "key" columns are not updatable fields -- they're just shown in the table as text (so the user can see them), but the current values are passed as "hidden" variables so they can be used in the WHERE clause for an UPDATE statement. (So, the generated UPDATE statement above can be used in the target script to update all the non-key columns, for the distinct row identified by the key columns).
All of the SQL statements should properly enclose text and date field values in apostrophes, as SQL requires, while leaving any numeric field values unquoted. The two forms should have input sizes matching the sizes of the database columns (up to a maximum size of 60 characters for the input box, but all fields should have a MAXLENGTH attribute matching the database column definitions). Columns greater than 100 characters should be shown as "textarea" inputs. Note that the forms have a dummy ACTION="..." attribute, which you will need to change to specify the scripts that you want execute when the forms are submitted.
The second set of scripts are ODBfmgr.odb and all of the scripts beginning with ODBfmgr. (ODBfmgr.odb is the starting page.) This set of scripts implements a simple "file manager" function that you can use to upload, download, do directory listings, rename files, and delete files on your Website, using only ODBiC. (All of these functions, except the directory listing, will only work on directories that allow writing by "anonymous user" CGIs.) The upload function has an option to rename any existing file with the same name to have a ".bak" entension, and there is a function to restore the ".bak" (by deleting the current version and renaming the ".bak" version back to its original name).
The directory listing shows any subdirectories with a clickable link to list files in that subdirectory. Non-directory files are clickable download links. (Note that HTML and graphics files will actually be downloaded, rather than displayed in the browser.) This listing also has the rename and delete functions for each file.
The ODBfmgr.odb script has two places where <SELECT> drop-down menus are used to select a directory for uploading a file and for restoring .bak files. You should edit these lists to include all of the directories that you want to be able to upload to. (Note that these should be absolute file system paths to the directories.) There are default entries for "\temp" and "$path_translated_dir$" (which will be the directory where ODBfmgr.odb itself is executed), but you can change and add to that list. If you prefer, you might change these to be input boxes instead of <SELECT> lists, so any directory could be typed in, but the lists will be safer and easier to use.
Download ODBgen and ODBfmgr utilities
Name E-mail optional TopicMessage:
HTTP Link: Link text: