This page outlines the protocol for communication between the server and it's clients.

If anybody creates nice client classes for a particular programming language and is happy to share them please submit them and I will make them available for downloading.

Database

You can add, edit or delete any databases you wish the server to take care of on the administration tab. SQL4Sockets works with SQLite databases and it will also negotiate the sharing of an ODBC databases. Configuration settings are encrypted and saved in configuration.asc.  The default password is 'password'. The text encoding of both the communication and the database character set should be UTF8.

Launching

Launching the server with the switches –h -l tells it to hide and listen. Alternatively the server can be set up to run as a service.

Delimiters

Record Delimiter = Chr(20)
Field Delimiter = Chr(31)
Null Field Flag = Chr(24)
End Of Transmission = Chr(23)


Instructions Sent To The Server
With all instructions if an error occurs the server will respond with:
"ERROR" + Chr(20) + "Error ID" + Chr(20) + "Description of error" + Chr(23).

Successful instructions that do not require a detailed response are acknowledged with:
"OK" + Chr(23).

If a more detailed response is appropriate it will be described in the example of the relevant instruction below.

BLOB_READ
One parameter - the ID of the BLOB you want returned.
The server responds with the raw data of your blob base 64 encoded and terminated with Chr(23)
Example:
"BLOB_READ" + Chr(20) + "BLOB_ID" + Chr(23)
   
BLOB_WRITE
One parameter - the base 64 encoded BLOB.
The server responds with an ID which you will need to use to retrieve the BLOB
   
DATABASE_CONNECT
Three parameters - database alias, user name and password.
The server responds with "OK" + Chr(23) if you're credentials are correct or otherwise "ERROR" + Chr(20) + "Description of error" + Chr(23)
Example:
"DATABASE_CONNECT" + Chr(20) + "myDB" + Chr(20) + "SYSDBA" + Chr(20) + "masterkey" + Chr(23)
   
DATABASE_DISCONNECT
No parameters.
Example:
"DATABASE_DISCONNECT" + Chr(23)
   
FIELD_TYPES
No parameters. Should only be called immediately after a successful SQL_SELECT instruction.
The server responds with "OK" followed by a list of field types delimited with Chr(20) and terminated with Chr(23)
Example:
"FIELD_TYPES" + Chr(23)
   
GET_RECORDS
No parameters. Should only be called after a successful SQL_SELECT instruction.
The server responds with "OK" followed by a delimited list of fields and records terminated with Chr(23).
Fields are delimited by Chr(31), Records are delimited by Chr(20), fields that are null are represented by Chr(24)
Example:
"GET_RECORDS" + Chr(23)
  
LAST_INSERT_ID
No parameters.
The server responds with "OK" + Chr(20) + "n" + Chr(23) where n is a string representation of the ID you requested (e.g. "1000000")
Example:
"LAST_INSERT_ID" + Chr(23)
   
SCHEMA_FIELD
Details to follow...
   
SCHEMA_INDEX
Details to follow...
   
SCHEMA_TABLE
Details to follow...
   
SQL_SELECT
One parameter - the SQL SELECT statement you want the server to run.
The server responds with "OK" + Chr(20) + FieldCount + Chr(20) + RecordCount + Chr(20) + a list of field names delimited by Chr(20) + Chr(23).
You can the ask for the records to be transmitted using the "GET_RECORDS" instruction.
Example:
"SQL_SELECT" + Chr(20) + "SELECT * FROM COUNTRIES ORDER BY COUNTRY;" + Chr(23)
   
SQL_EXECUTE
One parameter - the SQL statement you want the server to execute.
The server responds with "OK" + Chr(23)
Example:
"SQL_EXECUTE" + Chr(20) + "UPDATE COUNTRIES SET COUNTRY = 'Just Ordinary Britain' WHERE COUNTRY = 'Great Britain';" + Chr(23)

SQL_EXECUTE_BATCH
A delimited list of the SQL statements you want the server to execute.
If you do not need to retrieve an ID with your updates send them all as a batch, it is thousands of times faster than running them individually with SQL_EXECUTE.
You can mix and match SQL_EXECUTE and SQL_EXECUTE_BATCH within a transaction to maximize efficiency.
Example:
"SQL_EXECUTE_BATCH" + Chr(20) + SQL1 + Chr(20) + SQL2 + Chr(20) + SQL3 + Chr(23)
   
TRANSACTION_BEGIN
No parameters.
Example:
"TRANSACTION_BEGIN" + Chr(23)
   
TRANSACTION_COMMIT
No parameters.
Example:
"TRANSACTION_COMMIT" + Chr(23)
   
TRANSACTION_ROLLBACK
No parameters.
Example:
"TRANSACTION_ROLLBACK" + Chr(23)

If you are connecting to an ODBC database that does not support transactions leave the BEGIN, COMMIT and ROLLBACK fields empty when you set up the database on the server, but still call TRANSACTION_BEGIN, TRANSACTION_COMMIT and TRANSACTION_ROLLBACK in your code as they are used to control sharing.