This list of questions and answers should help you get started using ThinkSQL.
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.
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.
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.
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.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SELECT * FROM INFORMATION_SCHEMA.USERS
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.
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.
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.
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:
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:
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.
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.
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.
Call the TDatabase's:
StartTransaction
method before each batch of statements and then use the commit or rollback methods to manually end transactions.
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.
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.
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.