Updated: 10/21/06
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.
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. To output a "CSV" file, use "," (in quotes) for tye field delimiter. A "CSV" file will have double-quotes put around it if it contains the delimiter character itself (ie. ",").
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.
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 O'Tool". 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 bottom 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" (middle) radio-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 (top="File:", middle="Where:", or bottom="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.
"Where Files": If you have a text, csv, or xml file that contains rows of field vaules separated by the field delimiter / xml column (the simplist example would be a list of indices, one on a line/row) that you wish to use to fetch records in a table that match, you can specify the file name (full path) in the SQL box by itself. Select the field(s) whose values will be present in this file in order which they appear on a row into the "Where" column, check the middle radio button, then the [Select] button. You WILL be prompted to enter "Where values, just leave them blank and select comparisen operators (normally "=" will be proper). The program should return records that match the data in the rows of this file as if doing a loop using "where (indexfield = ?...) performing the quere for each row found in the file.
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:" (top) 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:" (middle) 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:" (bottom) 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:" (top) 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:
update table t set ( F1 = 'a') where (F2 = 'b')
If the "Where:" (middle) 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:" (bottom) 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.
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:" (top) 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.
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:
1) Click the name of the table to insert into from the "Table:" box.
2) Click the "Where" (middle) radio-button.
3) Select names of fields to enter data for (unless entering data for all fields) into the "Order" box. NOTE: You will be prompted to enter a value for each field. To avoid being prompted for values, you may enter values into the "Values:" line (one for each field selected in the "Order" box) separated by your chosen delimiter character (Do not use "=" or any character which appears in any of the values). It is NOT necessary to enclose character values in any quotes!
4) Click the [INSERT] button.
5) Enter a value for each field in the popup window and click "OK". NOTE: To avoid being prompted for values, you may enter values into the "Values:" line (one for each field selected in the "Order" box) separated by your chosen delimiter character (Do not use "=" or any character which appears in any of the values). It is NOT necessary to enclose character values in any quotes!
6) Verify that the data was correctly inserted by checking the status box at the bottom. It should display the actula SQL exeuted along with any database error-messages.
B) Updating records interactively: To update one or more records in a table, do the following:
1) Click the name of the table to update from the "Table:" box.
2) Click the "Where" (middle) radio-button .
3) Select names of fields to change (unless changing all fields) into the "Order" box. NOTE: You will be prompted to enter a value for each field. To avoid being prompted for values, you may enter values into the "Values:" line (one for each field selected in the "Order" box) separated by your chosen delimiter character (Do not use "=" or any character which appears in any of the values). It is NOT necessary to enclose character values in any quotes!
4) Select constraint fields (those to be used in a "Where" clause) into the "Order By" box (the field values entered will be AND-ed together). NOTE: To specify a real "WHERE" clause, enter the full clause (WITHOUT the leading word "WHERE") in the "SQL:" box and make sure nothing is in the "Order By" box. To avoid being prompted for constraints and having to specify a complete WHERE-clause, select fields into the "Order By" box and list the values (one for each field name in the "Order By" box) separated by your chosen delimiter character in the "SQL:" box. Do NOT enclose the values in any quotes.
5) Click the [UPDATE] button.
6) Enter a value for each field in the popup window and click "OK". NOTE: To avoid being prompted for values, you may enter values into the "Values:" line (one for each field selected in the "Order" box) separated by your chosen delimiter character (Do not use "=" or any character which appears in any of the values). It is NOT necessary to enclose character values in any quotes!
7) Verify that the data was correctly updated by checking the status box at the bottom. It should display the actula SQL exeuted along with any database error-messages.
C. Deleting records interactively: To delete records from tables interactive do the following:
1) Click the name of the table to delete records from the "Table:" box.
2) Click the "Where" (middle) radio-button .
3) (Skip this if deleting all records in the table) Select constraint fields (those to be used in a "Where" clause) into the "Order By" box (the field values entered will be AND-ed together). NOTE: To specify a real "WHERE" clause, enter the full clause (WITHOUT the leading word "WHERE") in the "SQL:" box and make sure nothing is in the "Order By" box. To avoid being prompted for constraints and having to specify a complete WHERE-clause, select fields into the "Order By" box and list the values (one for each field name in the "Order By" box) separated by your chosen delimiter character in the "SQL:" box. Do NOT enclose the values in any quotes.
4) Click the [DELETE] button.
5) Verify that the correct records were deleted by checking the status box at the bottom. It should display the actula SQL exeuted along with any database error-messages. NOTE: No error message is displayed if no records were found which matched the specified WHERE constraints -- just no records are deleted even though it sais that the delete was done.
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:
1) Click the name of the table to download from the "Table:" box.
2) Click the top radio-button (just left of the [File:] button).
3) Type in a file-name on the line just right of the [File:] button OR click the [File:] button to browse the directories and select an existing file for appending data to.
4) Set the "Delimiter" character to the desired delimiter and click the "Header" button if a header record containing the column headers is to be created (it is still possible to re-upload the created flat-file later with a header record)!
5) Click the field-names from the "Field" box in the order they are to appear in the records, unless all fields are desired and in the same order they appear in the "Field" box.
6) Click the [SELECT] button just below the "Order By" box and then click field-names from the "Field" box which should be used to control the sorting order (the "ORDER BY" clause in an SQL "SELECT" statement).
7) Optionally click the "Descend" and or the "Distinct" buttons.
8) For each field to be used in a "WHERE" clause, double-click the field-name from the "Field" box, then enter the proper Oracle-expression, ie. "like", "<", "=", etc. followed by a value (surround non-numeric values with quotes).
9) Click the [SELECT] button. You will be prompted for a value for each field in the "Order By" box.
10) Check the "Status" box at the botton of the screen for any errors. The output will appear on your screen and be written to the specified file. Each record will appear on a separate line (records are always separated by a newline ("\n") and each field will be separated by the specified delimiter character(s).
B) Uploading.
To upload data from a delimited flat-file into a table, do the following:
1) Click the name of the table to upload data into from the "Table:" box.
2) Click the top radio-button (just left of the [File:] button).
3) Type in a file-name on the line just right of the [File:] button OR click the [File:] button to browse the directories and select an existing file for input. NOTE: The file-name, if typed in, must already exist!
4) Click the button just under the "Order" list and click the field-names from the "Field" box in the order they appear in the records in the input file, unless ALL fields are desired and in the SAME ORDER as they appear in the "Field" box.
5) (If doing UPDATE multiple records from a file) Click the button just under the "Order By" list, then select the field(s) from the "Field" list which are to be used as keys (not changed). NOTE: A field being used as a key can not be changed. The other fields will be changed using their respective values. For example: If a table contained fields: ID, LN, FN, MI; an update file contained columns in order of: FN,ID,MI; and you wanted to change the FN, and MI fields based on ID as your key; you would set the "Order" list to contain "FN,ID,MI", and the "Order By" list to "ID". This would generate the following sql: update yourtable set FN = :1, MI = :3 where ID = :2
6) Set the delimiter character(s) to that used in the file (\t=tab), and check the "Header:" box if the first line of the file is a header (to be skipped).
7) Click [INSERT] or [UPDATE] as desired.
8) Check the "Status" box at the botton of the screen for any errors. It will also show a history of each record successfully uploaded.
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:
1) Click the name of the table to query from the "Table:" box.
2) Click the top radio-button (just left of the [File:] button) if sending formatted data to a file.
3) Type in a file-name on the line just right of the [File:] button OR click the [File:] button to browse the directories and select an existing file for appending data to. You can also enter a pipe symbol ("|") followed by a printer name to print directly to a printer.
4) Click the field-names from the "Field" box in the order they are to be printed, unless all fields are desired and in the same order they appear in the "Field" box.
5) Click the "Header" button if headers are desired and, if headers are desired and a page-length other than 56 lines is desired, enter the number of lines per page on the "Values: line. If headers should only print once at the top of the file and no pagination is to be done, enter 0 (zero). A pagebreak can be inserted between each page by placing a "\f" (formfeed character) before the page-length in the "Values: line (Any non-numeric value may be placed here and will print before the headers).
6) Change the "Delimiter" to either "-" or "=" or "*" or whatever you desire ("-" and "=" are usually recommended). This character will be repeated and will fill in a line between the header line and the following record line.
7) Click the [Format:] button. A default format string of format specifiers, one per field selected will appear on the line just right of the [Format:] button. You can then change the format specifiers as desired, (see section I, "Format:" for more details). The default usually provides a pretty good appearance.
8) Click the [SELECT] button. The formatted output will display in a pop-up window on the screen and be written to the select file (if the radio- button just left of the [File:] button is checked.
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 top 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).
1) Unix or M$-Windows.
2) DBI-supported database package
3) Perl, v. 5.003 or better.
4) Tk400.200 (Perl/Tk Library) or better.
5) Either Perl:DBI and DBD:your-database OR DBD:ODBC interface (Windows).