Home

News

Download

Documentation

Links

Books

Support

Company

Frequently Asked Questions

This list of questions and answers should help you get started using ThinkSQL.

Why can't I see my tables?

When connecting to ThinkSQL you are given a default schema. You can access tables owned by this schema by just specifying their name: the schema prefix is optional. If the table you want to access is owned by another schema, however, you must prefix the table name with its schema name, e.g.:

SELECT * FROM restaurant.menu_item_group

Alternatively, you could SET SCHEMA 'restaurant' to change the current schema - this does not change the current user so you must be privileged to access the schema.

Why are my changes not there after I re-connect?

Statements issued to ThinkSQL are always within a transaction. To save any changes made during the current transaction you must commit them by issuing the SQL COMMIT command (or SQLEndTran from an ODBC client). This applies to all statements, including data definition commands (e.g. CREATE, DROP and GRANT).

If you disconnect or re-connect without committing, the default behaviour is for the server to rollback the transaction.

How do I create a new user?

To create a new user, test, and a corresponding default schema you must be connected as the ADMIN user and then use:

CREATE USER test;
CREATE SCHEMA test AUTHORIZATION test;   --sets the default schema
COMMIT;

and then connect to the new user/schema using:

CONNECT TO '' USER 'test'

Notice that the COMMIT was needed before the CONNECT because re-connecting would otherwise rollback any uncommitted changes.

Why do I get a syntax error when I try to drop a table or view?

The DROP TABLE/VIEW must be followed by CASCADE or RESTRICT. At the moment RESTRICT is supported, e.g.:

DROP TABLE test1 RESTRICT

Also, remember that in ThinkSQL such commands are within the current transaction and need to be committed or rolled back to have a permanent effect.

How do I view a list of existing schemas owned by the current user?

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

How do I view a list of existing users?

SELECT * FROM INFORMATION_SCHEMA.USERS

Where is the C client library?

The ODBC driver provides a native API for ThinkSQL. This can be called from most programming environments. In Windows, link to ThinkSQLodbc.dll. The Linux build of the library has not been released yet, but will be if there is a demand for it. www.microsoft.com/data/odbc for the documentation of this interface.

How do I return a result-set from a stored procedure?

Use the WITH RETURN option in the cursor declaration and open the cursor before the end of the routine, e.g.:

CREATE PROCEDURE test()
BEGIN
        DECLARE high_priced CURSOR WITH RETURN FOR
                SELECT menu_item_name, price FROM restaurant.menu_item
                WHERE price > 5.00
                ORDER BY menu_item_name
        FOR READ ONLY;

        OPEN high_priced;
END;

and then call the new routine e.g.:

CALL test()

to retrieve the results.

How do I save binary data?

For Delphi/Kylix examples, see the Borland Developer Support article. Here's some of the example code to save an image.:

...
Bitmap:=TBitmap.Create;
Bitmap.LoadFromFile('mybitmap.bmp');
procedure SavePictureToDatabase;
        var BlobField:TField;
        BS:TStream;
        begin
                with Query1 do
                begin
                        Insert;
                        BlobField:=FieldByName('blob_column');
                        BS:=CreateBlobStream(BlobField,bmWrite);
                        Bitmap.SaveToStream(BS);
                        Post;
                end
        end

If you experience truncation when reading BLOBs via the BDE, check the BLOB SIZE alias setting is large enough. This issue does not arise when using dbExpress.

How do I improve multi-statement performance?

When accessing the server via ODBC, JDBC or dbExpress, the default connection setting is to automatically commit (auto-commit) after every statement. This is to comply with the ODBC specification, but has a number of drawbacks:

  • Changes can't be undone which defeats the whole point of transactions.
  • A new transaction has to be started for every command, costing time and resources.
  • Issuing a new statement (+ auto-committing) before fetching all the rows from a previous SELECT introduces cursor isolation issues, because the auto-commit applies for the whole connection.

So, to increase the performance and functionality of your client, it's recommended that you turn off auto-commit and manually control the commit and rollback. To turn off auto-commit:

ODBC

Issue a:

SQLSetConnectAttr(ConnectionHandle, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER)

command once after the connection is open and then use SQLEndTran(..., SQL_COMMIT) or SQLEndTran(..., SQL_ROLLBACK) to manually end transactions.

JDBC

Call the Connection's:

setAutoCommit(false)

method once after the connection is open and then use the commit() or rollback() methods to manually end transactions.

dbExpress

Call the TSQLConnection's StartTransaction() method before each batch of statements and end each transaction with a call to its commit() or rollback() method.

Alternatively, call the TSQLConnection's:

SQLConnection.SetOption(eConnAutoCommit, 0)

method once after the connection is open and then use the SQLConnection.commit(0) or SQLConnection.rollback(0) methods to manually end transactions.

BDE (uses ODBC)

Call the TDatabase's:

StartTransaction

method before each batch of statements and then use the commit or rollback methods to manually end transactions.

How do I improve equality matching performance?

Indexes are automatically added by ThinkSQL to enhance the performance of primary, unique and foreign key constraints. Unlike some other servers, we don't confuse the index (physical access aid) with the (logical) constraint, so we try to make indexes as transparent as possible. Future versions of the server may auto-create additional indexes as they are needed. For now, you can add additional indexes using:

CREATE INDEX index_name ON table_name ( column_list... )

This will need to be committed to be permanent and visible to other users.

Why can't the 'admin' user see or create tables?

The 'admin' user, unlike the superuser in other systems, has no 'special' privileges to see or modify other users' data unless this is explicitly granted.

The 'admin' user initially doesn't own a schema, although by default it connects to the DEFAULT_SCHEMA schema (owned by the DEFAULT user). This means it is not privileged to create, drop or alter objects within that schema.

The purpose of the 'admin' user is to restrict the ability to create, modify and drop users. The 'admin' user of the primary catalog also has special privileges to create, open, close and backup catalogs, and to shutdown the server etc. So connecting as 'admin' should be for administration tasks only.

How do I start the server with a primary catalog other than the sample db1?

Add the catalog name to the server command line, e.g.:

ThinkSQL live_catalog

This will open live_catalog and make it the primary catalog rather than the default db1 catalog. If the catalog doesn't exist or cannot be opened for some reason, there will be no primary catalog and no users will be able to connect. The only commands that can be handled by the server in this case are shutdown and create catalog, which will then open the new catalog as the primary one.

© Copyright 2012, ThinkSQL Ltd. All rights reserved.