Getting Started with Sculptor 6 SQL¶
The Sculptor 6 SQL client sends all queries to a Sculptor server. To run queries on local files, the Sculptor server must be running on the local computer. One or more catalogs and schemas need to be defined on each Sculptor server. The program \Sculptor\sbin\sqlmanager.q is used to create and maintain schemas. It can be run on any Windows client to maintain catalogs on a Windows, Unix or Linux server.
Ensure that the Sculptor server is running on the server and then start sqlmanager.q on any Windows client.
On the Servers tab, click Add Server. Enter a Server Name and the full path to the Sculptor Directory on that server and save. Accept the option to get catalog information from the server.
If a server has already been defined on this client, click the server name and accept the option to get catalog information from the server. To edit or delete an existing server, select the server name and click the right mouse button to open a context menu.
The DEFAULT catalog should exist on all servers. It is used by sql when no catalog name is specified. Other catalogs can be added if required. To edit or delete an existing catalog, select the catalog name and click the right mouse button to open a context menu.
For each catalog, including the default, at least one schema must be defined. The schema name should reflect the name of the application or a main feature of the application. Set the Schema Directory to a full or partial path name to a directory that contains Sculptor files on this server. If the path is partial or blank, the table name in the sql statement must specify the rest of the path. The User Name is for future use and may be left blank.
When a catalog is selected on the Catalogs tab, new schemas can be added to that catalog. To edit or delete an existing schema, select the schema name and click the right mouse button to open a context menu. Only schemas belonging to the selected catalog are available for editing.
For each schema, at least one set of tables must be specified. Filenames and wildcards can be used. A single * means all Sculptor files in the schema directory.
When a schema is selected on the Schemas tab, new tables can be added to that schema. To edit or delete an existing table, select the table name and click the right mouse button to open a context menu. Only tables belonging to the selected schema are available for editing.
SQL queries can now be run from any client. The sql Command Line Program The program sql provides a command line interface for SQL queries. For a list of command line options, type sql –h. For more information, start the sql program and type help when the “* >” prompt appears. Statements can be split over several lines and must end with a semicolon. To execute a statement, type RETURN after the semicolon.
Example using files on the local host with the default catalog and default schema
* > connect to default
Connecting to “localhost” … Connected (Kfserver version 1.10)
* > select * from myfile;
...results
Example using files on the local host with the default catalog and a named schema
* > connect to default
Connecting to “localhost” … Connected (Kfserver version 1.10)
* > select * from myschema.myfile;
...results
Example using files on a named server with a named catalog and schema
* > connect to myserver
Connecting to “myserver” … Connected (Kfserver version 1.10)
* > select “Date”, “Mileage”, “RegNo” from mycat.myschema.mileage order by “Mileage”;
...results
Notes In Sculptor SQL, column names are case sensitive because Sculptor field names are always case sensitive, so it is not necessary to use quotes as shown in the last example above. However, case sensitive names are non-standard in SQL and you may prefer to use quotes so that your queries use the standard syntax.
Sculptor ODBC Driver¶
Since Sculptor version 6.0.3 to install or update the Sculptor ODBC driver (scodbc6.dll), run the program regsvr_scodbc in the “sculptor\bin” directory (In previous versions use the batch file \Sculptor\bin\install_scodbc.bat to register the Sculptor ODBC driver on a client)
The Sculptor ODBC driver enables Sculptor programs to use the embedded sql command in the Sculptor language with Sculptor files. It also enables programs written in other languages, such as C#, to use embedded sql statements with a Sculptor database (through ODBC).
Sculptor SQL is SQL-92 compliant. Some features have not yet been implemented and these will be added in future releases. See Sculptor SQL Engine Features for full details.