The above advertising does not reflect the actual opinions of the owner(s) of this website nor do we endorse any products or services mentioned, in that the content of the adds is beyond our control, but are required by the company which hosts this site. Direct all comments or complaints to them (http://www.virtualave.com)

Updated: 10/21/06


The SqlPerl Home Page


SqlPerl - GUI interface to databases, by Jim Turner.

SqlPerl is a Sequel Graphical User Interface program for performing SQL commands and queries to databases. SqlPerl provides the following primary features:

SqlPerl is written completely in Perl, a modern, high-performance scripting language and runs under X or Windows using the Perl/Tk X-development library and should be usable on any Unix or Windows platform which supports Perl, Perl/Tk, and a DBI or ODBC-supported database package.

Click for System Requirements.

I) Graphical user-interface

The GUI provides easy user-interaction with the database with push- button SQL commands/queries and a text-box for typing in more complex commands. SqlPerl is great for database administrators who occassionally need to examine data, change specific fields, rows, or columns, load flat-files, generated quick formatted printable listings, etc. One can quickly look up data without having to remember table and field names and cryptic SQL commands.

Logging in

The user can invoke SqlPerl from the command line by simply typing:

sql.pl

at the Unix command-prompt. A small window will appear requesting the user to enter the desired database, user-name, and password. Clicking [Ok] or pressing [Enter] after typing in a password causes the user to be logged in. If the user is successfully logged in, the main screen will then be displayed. The main screen consists of the following elements from top to bottom and left to right:

1) File specification:

The user can type in a path and file name for uploading and downloading or click the [File:] button to browse and select an existing file. The radio-button just left of the [File:] button if checked, causes query results to be written (appended) to the specified file. This is how to generate delimited flat-files from data queries.

2) Delimiter:

Specify the delimiter string for use with flat-files and for displaying data. If doing a query and formatting is requested, the delimiter will be repeated to form a separator between the header and the data. Fields will be formatted into columns. If formatting is not requested, the delimiter will be used to separate the fields on the display. If File output is requested, the flat-file will be created using the delimiter string to separate fields. Records are always separated by a newline (\n). to specify a special character, ie. tab or dollar-sign, preceed it with a backslash, ie. \t or \x27. If data is being loaded from a flat-file, the delimiter used in the file should be specified as the delimiter here.

3) Header:

Click the button just left of "Header" to cause the first line displayed for queries to contain the field names. If creating a flat-file, the 1st record will contain the field-names. If uploading data from a flat- file whose first record is a header-record, click the Header button.

4) Prompt:

Check the radio-button here unless creating or uploading a flat-file or typing in sql-commands. When doing a query, the user may specify constraints (the arguments for an SQL "WHERE" clause) in the "SQL:" box below. The constraints should be separated by the delimiter character, ie. if "\t", then type in "field1='value1'\tfield2='value2'...". NOTE: Another way, which is normally easier when doing updates, is to use the "Order By" menu to specify the fields for the Where-clause, then you will be prompted to enter values for each field specified.

5) Values:

When doing a SELECT and formatted output, users can specify the number of lines (records) to be printed per page here. The default for n is 56. A form-feed followed by any header-information will follow each n-th line. If doing an INSERT, the user can specify a list of values to be inserted into the table separated by the delimiter character. NOTE: This line is also used to specify the number of lines to be displayed on a page when doing "SELECT" (see "Formatting").

You can also specify alternate column headers here (the default is to use the field names) by specifying: "\h=col-header1,col-header2,,col-header4...". In this case, the header for the third column will be the field name.

NOTE: non-numeric values must be surrounded with quotes. Single or double-quotes are ok, but if a value is to contain quotes, then use the other type of quotes, ie. "John M'cBride". You can also leave this field blank and you will be prompted for the values.

For inserts from a file, you can specify how many records in the file to skip before loading records into the file by specifying "\s=#". The default is 1 if the Header box is checked, zero otherwise.

6) SQL:

You can type in SQL commands directly here in this box. To execute the command you typed in, click the radio-button here and then click [SELECT]. If doing a SELECT, UPDATE, or DELETE, you can enter an entire WHERE-clause (minus the word "WHERE") here and select the "PROMPT" button and this will be used as the WHERE clause, IF no fields are specified in the WHERE list. Otherwise, this field is ignored.

It is also used when INSERTing data from a flat-file and using an Oracle SEQUENCE to spcify one or more sequence names separated by commas.

7) Table:

This listbox displays the list of all tables in the database. Click on a table to display it's fields in the "Field" box and to do SQL queries/commands on that table.

8) Field:

This listbox displays all of the fields in the selected table (see "Table") box above. Click on a field to add it to the "Order" box (or "Order By" box), depending on which one has the [Select] button checked. Double-click on a field to place its name in the "SQL:" line above. You select fields for the "Order" box in the order you wish to manipulate them. You can remove a field from the "Order" box by clicking on it there.

For example, if you were working with a table called "employee" and it has four fields: id, name, extension, and salary and you wanted to do something like: select name, extension, id from employee order by id -or- update employee set (name="Joe", entension="12345", id=3) where (id=2) then you would click "name", then "extension", then "id" from the "Field" box, then click the [Select] button under the "Order By" box, then click "id" again from the "Field" box.

NOTE: You do not have to click any field names if all fields are going to be used and in the same order that they appear in the "Field" box. You can now click [SELECT] or [INSERT], depending on which SQL statement you wish to do. If doing INSERT, you will be prompted for the current "id", enter 2. You are then prompted for the "name", "extension", then the new "id".

8) Order:

This box specifies the order in which fields are to be used as arguments in SQL commands. To add field names, click the [Select] button below the "Order" box, then click field names from the "Field" box. To remove a field-name, click that name. If no field names are in the "Order" box, then all field names are used in the order they appear in the "Field" box. If uploading from a delimited flatfile, use the "Order" box to specify the order that the field values appear in each line of the flat-file. To add field names, click the [Select] button below the "Order" box, then click field names from the "Field" box. To remove a field-name, click that name.

9) Where:

This box which fields to prompt for constraint values when doing a SELECT, UPDATE, or DELETE. It is ignored when doing an INSERT. You will not be prompted if the values corresponding to each field in the Where list are specified in the VALUES field separated by the field delimiter. No quotes are needed around string values in either case. To add field names, click the [Select] button below the "Where" box, then click field names from the "Field" box. To remove a field-name, click that name.

10) Order By:

This box specifies the order in which data records are to be sorted when doing a SELECT. (It represents the "ORDER BY" clause of the SELECT statement). When doing an update from a FILE, it represents which fields in the file contain constraint values rather than new data. tTo add field names, click the [Select] button below the "Order By" box, then click field names from the "Field" box. To remove a field-name, click that name.

10) Descend

Check this box, if records are to be sorted in descending order (when doing a SELECT.

11) SELECT

Click this button to execute a query or do an SQL command typed into the "SQL:" line. NOTE: Click the corresponding radio button ("File:", "Where:", or "SQL:". If "File:" or "Where:" is selected, a query will be done on the current table, using the fields specified in the "Order" and "Order By" boxes and results will be displayed in a window on the screen. If "File:" is checked, the results are appended to the specified file". If "SQL:" is checked, whatever sql command is typed into the text box will be performed without regard to any other boxes, fields, or selections.

12) Distinct

Check this checkbox to cause the [SELECT] button to do a SELECT DISTINCT query.

13) INSERT

Click this button to insert data into the current table.

If the "File:" radio-button is selected, the corresponding file is read in as a delimited flat-file and the data inserted into the current table. The records in the file must be delimited by the specified delimiter character(s) or in columns equal to those specified in the FORMAT box; and contain the correct number, datatype, and order of columns corresponding to what is specified in the "Order" box. If using Oracle or Sprite sequences for one or more fields, then headers and values for those fields should NOT be included in the flat-file or specified in the "Order" box, but the fields should be specified in the "Order By" box.

For example, if you were inserting a flat-file into a table called "employee" and it has four fields: id, name, extension, and salary and you wanted to do something like: insert name, extension, and salary, and use a system-generated sequence number for the "id" field; then you would click "name", then "extension", then "salary" from the "Field" box into the "Order" box, then click "id" into the "Order By" box, type in the Oracle sequence name into the "SQL" box - unless the sequence name is the same as the key field - i.e. "id", the click the [Insert] button.

If the "Where:" radio-button is checked, any values entered on the "Values:" line (separated with the user's chosen "Delimiter" character and without quotes) will be inserted into the current table ordered by the field names (if any) specified in the "Order" box. Otherwise, the user is prompted to enter a value for each field name appearing in the "Order" box.

If the "SQL:" radio-button is checked, whatever SQL command typed into the text box will be executed without regard to any other boxes, fields, or selections.

14) UPDATE

Click this button to update data in the current table.

If the "File:" radio-button is selected, the corresponding file is read in as a delimited flat-file and the data updated into the current table. The records in the file must be delimited by the specified delimiter character(s) and contain the correct number, datatype, and order of columns corresponding to what is specified in the "Order" box. Columns whose corresponding fields specified in the Order box, but not in the "Order By" box will have their values overridden by the corresponding values in the file. The values corresponding to the fields in the "Order By" box are used as constraints in a WHERE clause. For example, assuming a flat file "f" contained the following line: "a,b\n" and "f" was specified in the "File:" box, the "Order" box contained "F1,F2", and the "Order By" box contained "F2". Pressing "UPDATE" would execute the following sql:

If the "Where:" radio-button is checked, any values entered on the "Values:" line will be inserted into the current table ordered by the field names (if any) specified in the "Order" box. Otherwise, the user is prompted to enter a value for each field name appearing in the "Order" box. The user will first be prompted to specify the values for any fields specified in the "Where" box for use in generating a "Where" clause, if no values are specified on the "SQL:" box. The "SQL" box is taken as a WHERE clause minus the "where" keyword if no fields are specified in the "Where" box, otherwise, anything in the "SQL" box is taken as a list of constraint values (separated by the field delimiter).

If the "SQL:" radio-button is checked, whatever SQL command typed into the text box will be executed without regard to any other boxes, fields, or selections.

15) DELETE

Click this button to delete data in the current table. To specify a WHERE clause, either enter it on the "SQL:" box or select fields into the "WHERE" box. If field names are in the "WHERE" box, a list of values will be looked for in the "SQL" box, if none found, you will be prompted for values. If no where-clause is specified, a dialog box will appear asking the user if he wishes to delete the entire table. A "YES" answer deletes the whole table.

16) DESCRIBE

Click this button and a window pops up displaying all field names in the current table along with their Oracle datatypes and maximum lengths.

NOTE: The Precision values for numeric types are not shown.

17) Format:

Click this button to create formatted output for the current table or to insert records into the current table from a column-spaced input file. Click it again to clear any format information appearing on the "Format:" line. A series of format specifiers will appear in the box at right, one for each field name in the "Fields" box (or each field, if none selected). NOTE: The "Delimiter" character is changed to the default of "-" if formatting is toggled on and "," if toggled off. The format specifiers are Perl format specifiers in the general format:

@nj -OR- @n#.##

where @ is the "at-sign",

n represents a number of characters, and

j represents justification and is either "<" "#", "|", or ">".

n will be the maximum width of the field minus one (the @ sign represents the 1st character). "<" means left-justify the field, "|"=center, and ">"= right justify. "#.##" represents a right-justified decimal field (used only for "Packed Decimal" fields.

The user can then modify the format string to change column sizes, justification, as well as add other characters to print out amoung the data on each line.

NOTES:

A) Formatting only applies to output (either to the screen or to both the screen and a file, if the "File:" radio-button is checked.) OR to INSERTing records from a column-spaced input flat-file.

B) The "Delimiter" character is used to separate the header line from data lines, instead of separating fields, so the user should usually change the delimiter character from a field delimiter, such as a comma, or a tab, to either a dash or an "=" sign. The delimiter character will be repeated for each character of data line output, for example (Assume the delimiter character is the "=" sign:

        ID   EMPLOYEE           EXTENSION      SALARY
        ===============================================
         1   Doe, John          11111          12235.00
         2   Smith, Jack        11211          14228.00 ... 

C) By default, a form-feed character will be inserted after each 56th line of output (54 records) and the headers reprinted, if the "Headers" box is checked. To change this, enter a numeric value on the "Values:" line. If zero, headers will print once and no formfeeds will be inserted.

D) If inputting records from a column-spaced flat-file, ie. a file produced from formatted output, if the "Header" button is checked, the 1st record in the input file is skipped (assumed to be a header) as are any lines starting with a form-feed (\f). If a field-delimiter is specified, any line containing only spaces and the delimiter character are also skipped. This permits "formatted" files to be fed back in.

E) If inputting records from a column-spaced flat-file, fields specified with the ">" (right-justify) format character will be first stripped of leading spaces before being added to the table. This allows for data written to a "formatted" flat-file via ">" (right-justified) to be read back in properly.

18) Status box:

The actual SQL commands along with any status or error messages are displayed in this box. The user can scroll the box backwards to see a history for this session of the SQL commands/queries he has issued.

NOTE: Each command is committed as it is done! There is no procedure for doing rollbacks.

II) Interactive Record Manipulation, ie. inserts, updates, and deletes.

It is easy to manipulate records within tables interactively. SqlPerl can prompt users for necessary field data via popup windows and then generate the nessessary SQL.

A) Inserting records interactively: To insert a record into a table, do the following:

B) Updating records interactively: To update one or more records in a table, do the following:

C. Deleting records interactively: To delete records from tables interactive do the following:

III) Easy uploading from and downloading to delimited flat-files.

A) Downloading.

SqlPerl permits users to upload data from flat-files into tables and download data from tables into flat-files. All the user needs to do to download data is to click the radio button left of the [File:] button and either enter a file name or browse for an existing one (to be appended to), then do a query (via the [SELECT] button). The user should follow the steps below:

B) Uploading.

To upload data from a delimited flat-file into a table, do the following:

IV) Quick printable report generation with user-selectable formatting.

With SqlPerl, it is easy to produce simple, printable reports of your data using Perl's formatting capabilities. It also makes it easy to display data in nice, smooth columns with headers, etc. To format query output into columnar format, do the following:

V) Quick lookup of tables, fields, and data.

SqlPerl provides quick lookup of data. The names of all tables in the database appear in the "Tables" box when the user logs in". Click on a table name to see all field-names in the table. Click DESCRIBE] to see all field names along with their Oracle datatypes and maximum lengths.

To look up data, simply click fields, select your constraint fields into the "Where" box, use the "Order By" box to specify sort order, and click the [SELECT] button to do a query. You will be prompted for the constraints, which will be "ANDED" together.

Data matching the criteria and selected field-names is displayed in a pop-up window on the screen.

Press the [Format:] button to arrange the data into smooth columns, the "Header:" button to print the field names as headers at the top of the columns, and click the radio-button just left of the [File:] button to save the displayed query results to a text-file for printing (use "Format:") or saving (unformatted, delimited).

V). Minimum System Requirements: