SqlPerlPlus is a Perl/Tk-based GUI tool for performing basic queries and SQL database table manipulation similar to "sa" or "Sql Plus", et. al. The biggest differences between this tool and others is: 1) It's graphical. 2) It can work with any database that Perl/DBI can talk to. 3) It is user-configurable via an ascii text file (sqlplcfg.txt) 4) It's free, opensource, and written in Perl/Tk. 5) Easy auto and manual formatting for report generation. 6) It can load table data into tables and write out table data in several formats, including delimited flatfiles (you choose field and record delimiters), columised (padded with spaces to desired widths), XML, and even MS-Excel (.xls). 7) When used to load data into a table in the formats mentioned in #5 above, it can generate a Perl script to automate this process. This is useful when needing to create a program to periodically load a table from a regularly-updated file on an ongoing basis. For additional information, visit the homepage at: http://home.mesh.net/turnerjw/jim/sqlperl.html 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. SqlPerl 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. SqlPerl also allows one to both load data from and write data out to M$-Excel spreadsheets! When one loads formatted or delimited data into a table using SqlPerl, a Perl script is automatically generated which can then be used later to load or reload data in the same format into the same table. This allows one to test load data initially into a table, then have a ready-made cron-job to do all subsequent loads / reloads! This script is saved to the file "sqltemp.pl", which can easily be renamed. To install (Windows): Stop - download the self-extracting "sqlplsetup.exe" from my homepage (previously mentioned above) and run it. To install (Linux/Unix/MacOS): 1. Make sure Perl and Perl/Tk are installed on your system. 2. Make sure to obtain the following Perl modules are installed on your system: They are all available at my homepage and CPAN and are all single-file pure-Perl modules. Tk::JDialog Tk::JFileDialog Tk::JBrowseEntry Tk::JOptionmenu JCutCopyPaste 3. Copy "sql.pl", "sqlmake.pl", and "sqlplcfg.txt", "sqlpl.bin", and "sqlpl.dat" to the same directory somewhere in your path. 4. Edit the file "sqlpl.bin" with your favorate text editor, to add your user-name to give yourself access to tables. 5. Run "sqlmake.pl" to "compile" (encrypt) "sqlpl.bin" to "sqlpl.dat". 6. With a web-browser, view the file "sqlperl.html" for docs. 7. sql.pl & ========================================================================== Here is what to do to get the SqlPerlPlus database GUI tool to work with Sybase: I) For installing & using the binary: 1) Install the Sybase Openclient (v. 12.5+) 2) Open Administrative Tools.Data Sources(ODBC) and add a System DSN with the following options: Driver should be "Sybase System 11", then the Data Source Name should be "mbxdev"; Server Name: 10.4.1.100:4100; and the Database Name: "mbxdev_propval". 3) Place the binary (sqlx.exe) into your path and touch a file called .sqlrw (unless you only want "readonly" database access). NOTE: If you have ActivePerl already installed on your system, but wish to install the binary, place it in a directory OTHER than c:\perl\bin. 4) Create/Edit a text file in the same directory called "sqlplcfg.txt" that has the following lines in it: dbname= (You can specify this multiple times, once for each dsn you want in the dropdown list) dbuser= (You can specify this multiple times, once for each user you want in the dropdown list) dbtype=Sybase (Make Sybase the default database selected) use=propval (You can specify this multiple times, once for each database you want in the USE dropdown list) browser=start The Full form of the dbname, dbuser, and dbtype lines are: dbcmd=[:{}[;...][:] [] indicate optional values; Attributes take the form: attr1 => value1[,attbn => valuen...] s are SQL commands to be run when starting up. is any theme specified in the file .myethemes. Attributes, initial commands, and theme are taken first from the selected dbuser, then dbname, then dbtype. EXAMPLE: dbname=propval_core:{AutoCommit => 1, noplaceholders => 0}:set TEXTSIZE 65535:Blue II) (Only if building from source - not needed for binary): 1) Install ActivePerl (latest 5.6.8 build 817 from www.activestate.com). 2) Install the following CPAN modules via PPM: Spreadsheet-WriteExcel, Spreadsheet-ParseExcel-Simple, and DBI. 3) Install the following modules from my website: JBrowseEntry, JCutCopyPaste, JDialog, JFileDialog, JOptionmenu, and setPalette. 4) Obtain sql.pl from Jim and place it in c:\perl\bin. 5) Download DBD-Sybase, v. 1.04.6 or better from http://www.peppler.org/downloads/ActiveState/. 6) Open a DOS shell and switch to where you downloaded this zip file to and unzip it. 7) Switch into the directory it creates (DBD-Sybase) 8) Run the command: "ppm install DBD-Sybase.ppd" 9) Switch to the c:\perl\bin\ directory and run the command: "pp -o sql.exe sql.pl”. ========================================================================== Documentation for setting up security using "sqlpl.bin" and "sqlmake.pl": SqlPerl prevents unauthorized access to database tables via an encrypted security file called "sqlpl.dat". It is built from a text-based configuration file called "sqlpl.bin" by "sqlmake.pl". Each line represents a list of user-ids and what they are allowed to access. Note: "user" refers to a user's system login (account) name. "dbuser" refers to a database's login usernames. The general syntax is: --,user1[,user2,user3...] Users in this list can access any table in any database. dbtype,user1[,user2,user3...] Users in this list can access any table in any database of this type, ie.: Oracle,oracleuser1,oracleuser2 dbname,user1[,user2,user3...] Users in this list can access any table in any database of this name, ie.: employee_database,payroll_user,accounting_user dbname:dbuser,user1[,user2,user3...] Users in this list can access any table in any database of this name under the database login name "dbuser". dbname:dbuser:table1,user1[,user2,user3...] Users in this list can access only table "table1" in any database of this name under the database login name "dbuser". dbtype:dbname:dbuser,user1[,user2,user3...] Users in this list can access any table the database of this name and type under. NOTE: As of v. 3.97, you must create a file in your home directory called ".sqlrw" (it can be empty). Otherwise, sql.pl operates in READONLY mode!