SQLIO Example Configurations

9.8

The following step by step instructions explain how to get SQLIO operational. Four configurations are explained based on the type of database in use.

Note:

1. For performance reasons, it is recommended that the schema tables be stored in SQLite database local to the runtime.

2. The illustations below are done in the simplest manner possible with the fewest changes required to programs.

3. The PL/B language has an extensive set of options available to alter SQLIO operations during program execution. These options may be specified in the PLBWIN.INI file, by using SETMODE, or in the actual OPEN/PREP statements.

4. You should allow the PREP instruction to create the tables for any files defined. This will ensure that they are created with the necessary attributes as required by SQLIO.

5. The SchemaEditor must be restarted after any modifications are made to the PLBWIN.INI file.

6. Once configured, the syntax for preparing or opening a file is:

   PREPARE File,"myfile <sql>"

   OPEN File,"myfile <sql>"

Example Configuration #1

The schema and data will be in one SQLite database (\sunbelt\plbwin.??\code\sunschema.db)

Note:

1. There should be no PLB_SQLIO keywords in the plbwin.ini configuration file

2. It is assumed that the schema database does not currently exist.

3. No modifications to the PLBWIN.INI file are required.

Procedure:

1. Start the Schema Editor.

2. Select "File/New" and create "sunschema.db" in the "\sunbelt\plbwin.??\code" directory.

3. Select the "SQLIO" tab.

4. Select "SQL Files/Add" from the menu.

5. Create a new file definition specifying a file name (i.e, address.txt) and a table name (i.e., address) with at least one column (i.e., "name" with a length of 20).

6. Edit the "address.txt" file definition once again.

7. Click the "Test Prep" button to verify the configuation.

8. You can use a utility such as DBExplorer to verify the creation of the table.

Example Configuration #2

The schema and user data will be in seperate SQLite databases

Note:

1. The SQLite schema will be in "\sunbelt\plbwin.??\code\sunschema.db". No sunschema database currently exists.

2. The user database will be "\temp\test.db".

3. One modification to the PLBWIN.INI file is required.

Procedure:

1. Add the following line to the "Environment" section of the PLBWIN.INI file in the \sunbelt\plbwin.??\code directory:

 

   PLB_SQLTABLEDB=testdb

 

2. Start the Schema Editor.

3. Select "File/New" and create "sunschema.db" in the "\sunbelt\plbwin.??\code" directory.

4. Select the "SQLIO" tab.

5. Select "Databases/Add" from the menu.

6. Enter "testdb" (the same name as used in step 1) for the name and "\temp\test.db" for the database file.

7. Respond "Yes" to the prompt regarding the creation of the database.

8. Close the database editor window.

9. Select "SQL Files/Add" from the menu.

10. Create a new file definition specifying a file name (i.e, address.txt) and and a table name (i.e., address) with at least one column (i.e., "name" with a length of 20).

11. Edit the "address.txt" file definition once again.

12. Click the "Test Prep" button to verify the configuation.

13. You can use a utility such as DBExplorer to verify the creation of the table.

Example Configuration #3

An SQLite schema with user tables stored in a SQL Server database

Note:

1. The schema will be in "\sunbelt\plbwin.??\code\sunschema.db". No sunschema database currently exists.

2. The user database has been created using the Microsoft SQL Server Management Studio and is empty.

3. Three modifications to the PLBWIN.INI file are required.

4. The Sql Server ODBC driver must be installed.

Procedure:

Begin by configuring the ODBC connection:

1. From the Windows Control Panel/Administrative Tools/Data Sources (ODBC), select the "System DSN" tab.

2. Click "Add" and select "SQL Server" driver.

3. Enter a name (i.e., SQLIO) and select the server from the drop-down list.

4. Click "Next" and configure the login as required by the database server.

5. Select the "Change the default database" checkbox and select the user database name from the drop-down list.

6. Click "Next", "Finish" and finally "Test Data Source".

7. Do not proceed until the test is successful.

8. Finally click "OK" and exit the Control Panel.

Next, set up the runtime and schema:

1. Add the following lines to the "Environment" section of the PLBWIN.INI file in the \sunbelt\plbwin.??\code directory:

 

DBADO=sunwado.dll

PLB_SQLIO_DRIVER=2

PLB_SQLIO_SQLTYPE=2

PLB_SQLTABLEDB=testdb

 

2. Start the Schema Editor.

3. Select "File/New" and create "sunschema.db" in the "\sunbelt\plbwin.??\code" directory.

4. Select the "SQLIO" tab.

5. Select "Data Sources/Add" from the menu.

6. Enter "testdb" (the same name as used in step 1 for the PLB_SQLTABLEDB keyword) for the name and "\temp\test.db" for the database file.

7. Select "DBFILE" for the driver and "MS-SQL" for the SQL Language.

8. Enter "DBADO;;DSN=SQLIO;UID={user id};PWD={user password};DATABASE={user database}" for the "Host" parameter.

9. Replace the "{user id}", "{user password}", and "{user database} entries in the parameter with values used during the ODBC configuration.

10. Click "Test Connection" to ensure the configuration is correct. Do not continue until the test is successful.

11. Close the database editor window by clicking "Update".

12. Select "SQL Files/Add" from the menu.

13. Create a new file definition specifying a file name (i.e, address.txt) and and a table name (i.e., address) with at least one column (i.e., "name" with a length of 20).

14. Edit the "address.txt" file definition once again.

15. Click the "Test Prep" button to verify the configuation.

16. You can use a database administration utility to verify the creation of the table.

Example Configuration #4

An SQLite schema with user tables stored in a MySQL database

Note:

1. The schema will be in "\sunbelt\plbwin.??\code\sunschema.db". No sunschema database currently exists.

2. The user database has been created using phpMyAdmin or other database administrative utility and is empty.

3. Four modifications to the PLBWIN.INI file are required.

4. The MySql ODBC driver must be installed.

Procedure:

Set up the runtime and schema:

1. Add the following lines to the "Environment" section of the PLBWIN.INI file in the \sunbelt\plbwin.??\code directory:

 

MYSQL=sunwmsql.dll

PLB_SQLIO_DRIVER=2

PLB_SQLIO_SQLTYPE=3

PLB_SQLTABLEDB=testdb

 

2. Start the Schema Editor.

3. Select "File/New" and create "sunschema.db" in the "\sunbelt\plbwin.??\code" directory.

4. Select the "SQLIO" tab.

5. Select "Data Sources/Add" from the menu.

6. Enter "testdb" (the same name as used in step 1 for the PLB_SQLTABLEDB keyword) for the name and "\temp\test.db" for the database file.

7. Select "DBFILE" for the driver and "MYSQL" for the SQL Language.

8. Enter "MYSQL;;{host}" for the Host parameter. "{host}" may be an IP address or a host name. Enter the user name and password for the MYSQL database. In the "Connect" field, enter the database name.

9. Click "Test Connection" to ensure the configuration is correct. Do not continue until the test is successful.

10. Close the database editor window by clicking "Update".

11. Select "SQL Files/Add" from the menu.

12. Create a new file definition specifying a file name (i.e, address.txt) and and a table name (i.e., address) with at least one column (i.e., "name" with a length of 20).

13. Edit the "address.txt" file definition once again.

14. Click the "Test Prep" button to verify the configuation.

15. You can use a database administration utility to verify the creation of the table.

See Also: SQLIO Overview, SQLIO Runtime Keywords