SQL*Magic : Super*SQL
|SQL*MagicSuper*SQLCase StudiesResourcesTechnology ChoicesSQL StandardLinks

Super*SQL FAQ

Why was Super*SQL Developed?

At SQL*Magic we believe that users and developers should have the freedom to choose whatever database product they want to meet their business needs.  We also believe that these needs can change over time. For example, a developer working on a project might want to start  with a license-free database such as MySQL or Hypersonic SQL. However, if the product is successful then it will probably be desirable to support enterprise database systems such as Oracle or SQL Server.

There are numerous barriers which impede this freedom of choice. Even with JDBC, which is a great technology, there are slight differences in the registration of drivers that complicate porting. However, the major problem is with the administration tools and, in particular, the SQL syntax used by different systems.

For example, note the differences in the following SQL query:

Oracle: SELECT 'Events scheduled for '||SYSDATE||NVL(TODAYS_EVENTS,'No events planned') FROM CONVENTION_SCHEDULE;

MySQL: SELECT CONCAT('Events scheduled for ',SYSDATE(),NULLIF(TODAYS_EVENTS,'No events planned')) FROM CONVENTION_SCHEDULE;

Transfering data between systems is also a problem with each database vendor providing different bulk loading tools and scripts.

Finally, most database systems do not support all hardware platforms equally well. Even Oracle, which has made a commendable effort to support commonly used systems, does not provide any client tools for the Mac. Super*SQL will run on any platform that has a Java Runtime Environment.

Super*SQL...

With Super*SQL, you really can write one set of code and use a single database query and update tool for any database system that you want to work with.

top

Where can I find JDBC drivers and how much do they cost?

Most database vendors provide a JDBC driver free of charge to owners of the database product. There are also 3rd party companies that provide JDBC drivers for multiple databases, but these can be quite expensive. If you have problems locating a JDBC driver for the database you are using, contact us at [email protected] and we'll help you locate one.

top

Why Isn't Super*SQL an Open Source Project?

At SQL*Magic, we believe in the Open Source movement. We participate in numerous on-line forums and provide some of our software components as open source modules.

However, having been in the software development game for many years, we know that delivering software features and functions in a planned and predictable way requires dedicated resources. We have ambitious development plans for Super*SQL which include support for procedural constructs and joins between different database systems. The only way we know how to actually deliver the planned enhancements is to generate some revenue from the products that we build. We believe that the pricing structure for Super*SQL provides excellent value, both for the personal and corporate editions.

top

I can't load data into tinySQL using Super*SQL - what's wrong?

There are bugs in tinySQL that prevent the loading of any data types other than INT or CHAR. SQL*Magic will be correcting these bugs over time. However, for the time being the simple work around is to use a spreadsheet program such as MS Excel or Quattro Pro to load the data. Either of these programs will accept a standard Super*SQL unload file as text input, using the bar | as a field delimiter. Once loaded into the spreadsheet program, the data can be saved as a DBF file which will make it accessible from tinySQL.

top

How can I change the appearance of web reports produced by Super*SQL?

Super*SQL implements HTML formatting through support for CSS. Even if you are not familiar with CSS but have worked with HTML before you will find it easy to change the appearance of the output generated by Super*SQL.

When Super*SQL generates output with format HTML, a CSS tag will be included in the page header. This tag will define a number of CSS classes: 

body
table used for output tables.  Defaults to a thin, gray border with no caption.
tr.head // used for the 1st row of a table
tr.odd // used for odd rows of a table - defaults to lightCyan background color
tr.even // used for even rows of a table - defaults to lightYellow background color
td.text // used for text cells in a table
td.dec // used for decimal number cells in a table
td.int // used for integer cells in a table
td.date // used for date cells in a table
td.posneg // this is a psuedo class extension supported by Super*SQL.  Applying this 
class to a particular column (see below) will cause the class td.pos to be used for 
positive values and td.neg to be used for negative values.
td.pos // can be used for numeric data cells that have a positive value
not applied to any column by default
td.neg - can be used for numeric data cells that have a negative value
not applied to any column by default

As data is output to the HTML page, the appropriate class tags will be attached to each element.

Users can override the default class definitions by using the CSS command. For example, css tr.head {background-color: blue; color: white } would change the table header to have white text on a blue background.

Users can include a background image (for example, a company logo) as part of the output page by overriding the body class as follows; css body {background-image:url(myimage.gif)};. Users can also define new classes as shown here: css td.mine {color: green; font-family: arial};.

These user defined classes can be used with particular columns using the COLUMN command,as shown below; column AColumnName class mine; The column command can also be used to define a descriptive header. column AColumnName heading "This is not the default heading";

Finally, the user can assign the psuedo class posneg to a column in order to display positive and negative values differently (useful for financial reports); column AColumnName class posneg; In this case positive values will be displayed using the properties assigned to td.pos, negative values will be displayed using td.neg.

The files in the testpack will allow you to run the csstest.sql script to demonstrate the CSS support in Super*SQL. For more information on CSS, visit the W3C's website.

top

How can I move information between databases using Super*SQL?

There are two different ways to move information using Super*SQL.

  1. Unloading individual tables: The command "unload table myTable;" will generate a set of output commands and data strings that can be used to recreate a table and its contents in another database system. Column types will be translated as necessary when the new table is created. For example, a NUMBER column in Oracle will be created as a NUMERIC column in MySQL.

Super*SQL allows you to rename your table when you do the unload. By using the command "set unloadprefix test;" you will generate commands that will recreate table myTable as test_myTable at load time. This can be very useful for subsetting data for testing purposes without over-writing a real data table.

Super*SQL allows you to include a WHERE clause when unloading a table. For example, the command "unload table myTable where myColumn=1;" will generate the commands and data strings required to recreate the table with only the selected data being transferred.

Note: If you are working within one database system the following SQL command will achieve the same result much more easily.

CREATE TABLE TEST_MYTABLE AS SELECT * FROM MYTABLE WHERE MYCOLUMN=1;
  1. Unloading information selected from multiple tables. In this case the command "set format unload;" can be followed by any valid SQL SELECT statement. No table creation or loading commands will be generated by Super*SQL, and therefore a loading control file must be supplied. An example is shown below. 
set dialect ORACLE;
Load data 
infile C:\testdata\mydata.dat
into table myTable REPLACE
Fields terminated by '|'
trailing nullcols
(first_name,last_name,telephone_number,email_address)

The file mydata.dat should contain 4 columns of information separated by vertical bars.

When you want to do the actual unload, make sure that you have used the "Spool mydatafile" command to specify the output file. When the command has completed you MUST use the "spool off;" command in order to complete the I/O to the file and close it. If you do not use the "spool off" command (or exit from Super*SQL), then the file will not be completely written.

Loading Information

Once you have finished doing the unload, you can connect to a different database and do a load immediately using a command such as "load c:\testdata\mydata.dat".

If you are loading data which was unloaded from multiple tables (method 2) above), then you should provide a control file as shown above. In that case, you would load the control file which specifies the data file as follows; "load c:\testdata\mydata.ctl;"

The Super*SQL load files are exactly the same across all platforms and databases. Therefore you can unload data from a tinySQL database on a Macintosh computer, copy it to a floppy disk or CD, and then load it into MySQL or any other database on a Linux or Windows computer.

top

How can I work with MS Excel (or other spreadsheets) using Super*SQL?

You can perform SQL queries and updates on Excel spreadsheet data in two different ways.

  1. If you have an ODBC driver available for Excel (that probably came bundled with MS Access), you can set up an ODBC datasource for each spreadsheet and connect to the spreadsheet using the DSN (Data Source Name) that you specified in the ODBC connection.

Advantages: the data is read directly from the spreadsheet file and, therefore is always current.

Disadvantages: Super*SQL will treat each spreadsheet file as a different database connection. Therefore it will NOT be possible to do joins between tables (spreadsheets). The ODBC datasource will have to be configured on each client machine that you want to access the spreadsheet from.

  1. You can open up your spreadsheet in MS Excel, then save it as a dBase IV file (or more than one file if you have multiple sheets in the spreadsheet). Super*SQL can then immediately read the file and perform queries and/or updates using SQL by connecting to the directory that the spreadsheet file is in with a tinySQL connection. Using Excel in this way is one of the fastest and easiest ways to set up a database. Super*SQL stores all of its system tables in this way.

Advantages: Immediate access to multiple spreadsheets in the same directory through a single tinySQL connection. This allows joins between spreadsheets. Any program (not just MS Excel) that can read/write dBase files can be used to view or update the data. By putting your spreadsheets on a network drive any number of users can access them using Super*SQL with no client set-up. Super*SQL can access the dBase files on platforms that do not support MS Excel such as Linux, or on computers that do not have MS Excel installed.

Disadvantages: If the spreadsheet is changed, it will have to be saved to dBase IV format again. This approach will NOT preserve formulas. Calculated data will be saved as static numeric fields.

top

When I update Super*SQL tinySQL tables in Excel, why do the changes get lost?

You can edit tinySQL DBF table files with Excel, Quattro Pro, or another program that can read and write dBase files. There are a few things to watch out for when using Excel.

When you save the updated file from Excel, the default location will not be where the file came from. As a result, it is very easy to miss the fact that you have written a new DBF file rather than over-writing the existing one.

Even when you are very careful about over-writing the existing DBF file, changes can be lost, and there will be absolutely no indication that a problem has been encountered while writing the file. We have not been able to identify exactly why this happens, but the following procedure almost always works.

  1. Edit the tinySQL table and save the file in XLS format (not DBF).
  2. Delete the DBF file.
  3. Save As from Excel to write a new DBF file.

In rare cases, even this procedure will not work. In such cases there is something in the structure of the spreadsheet that will not allow Excel to convert it to DBF format. In this case use the following procedure.

  1. Edit the tinySQL table and save the file in CSV format (not DBF);

  2. Delete the DBF file and the XLS file (if one exists).

  3. Use Excel to open the CSV file, then Save As to create a new DBF file.

Limitations of tinySQL files:

top

When I try to load data from an unload file, why doesn't the table get created?

The most difficult task for Super*SQL during the load process is the creation of the table. This should work properly when you are unloading and loading into the same database type. However, when moving data between types there can be problems when trying to translate the column type. If your loads are failing, try the following.

top