SQL Relay Client API's
The SQL Relay client API's are ultimately based on the C++ API and the
Rudiments library. A more portable approach would be to implement the API
natively in a language like Java, Python or Perl. A more stripped down and
potentially higher performance version of the API could be written in any
language.
SQL Relay has always had an open-protocol by virtue of being
open-source, but reverse-engineering it from the API code can be a daunting
task. If it were documented in plainer terms, it would be "more open".
Born of these motivations, the following pseudocode demonstrates the
protocol and its capabilities. This code should provide enough detail about
the protocol to enable a developer to write his or her own API.
Note that this specification will likely change a bit as new features
are added to SQL Relay.
Legend
- Yellow: long - a 32 bit integer
- Red: short - a 16 bit integer
- Green: string - a series of 8 bit chars
- Blue: double - a 32 bit, double precision floating point number
Client/Listener Communication
- Connect to the sqlr-listener using an inet or unix datagram socket
- Send:
length of user name |
user name string |
length of password |
password string |
- Receive:
- If error status is 1
length of the error |
error string |
- If error status is 0
whether to reconnect to the connection daemon or not |
- If reconnect is 1
length of the inet port |
unix port string |
inet port |
- If reconnect is 0 skip down to Send below.
Client/Connection Communication
- Connect to the sqlr-connection-"dbase" using the inet or unix datagram
socket that you got from the listener
- Send:
11 (sending authentication) |
length of user name |
user name string |
length of password |
password string |
- Receive:
- Loop:
- If sending a new query or re-executing a previously prepared query:
- Send:
- If executing a new query:
0 (new query) |
length of the query |
the query string |
- If re-executing a previously prepared query:
13 (reexecute query) |
cursor id |
The number of input bind variables |
- For each input bind variable:
length of the bind variable name |
the bind variable name string |
- For a string variable:
1 (indicates that this is a string variable) |
the size of the bind variable value |
the bind variable value |
- For a long variable:
2 (indicates that this is a long variable) |
the bind variable value |
- For a double variable:
3 (indicates that this is a long variable) |
the bind variable value |
the bind variable precision |
the bind variable scale |
- For a NULL variable:
0 (indicates that this is a NULL variable) |
- For a BLOB variable:
4 (indicates that this is a blob variable) |
the size of the bind variable value |
the bind variable value |
- For a CLOB variable:
5 (indicates that this is a blob variable) |
the size of the bind variable value |
the bind variable value |
The number of output bind variables |
- For each output bind variable:
length of the bind variable name |
the bind variable name string |
- For string/BLOB/CLOB output bind variables:
1 (indicates that this is a string/BLOB/CLOB variable) |
the size of the bind variable value |
- For cursor output bind variables:
6 (indicates that this is a cursor variable) |
- If you want to get column info:
- If you don't want to get column info:
The number of rows to skip |
The number of rows to fetch |
Skip to "If fetching rows from a result set: Receive" below...
- If fetching from a bind cursor
14 (fetch from bind cursor) |
cursor id |
0 (number of input binds) |
0 (number of output binds) |
- If you want to get column info:
- If you don't want to get column info:
The number of rows to skip |
The number of rows to fetch |
Skip to "If fetching rows from a result set: Receive" below...
- If sending a commit:
- If sending a rollback:
- If toggling autocommit:
- Send:
11 (autocommit) |
0 to turn off autocommit and 1 to turn on autocommit |
- Receive:
1 if toggling autocommit succeeded and 0 if it failed |
- If sending a ping:
- If sending a identify:
- Send:
- Receive:
length of the id string |
id string |
- If fetching rows from a result set:
- Send:
1 (fetch rows from a result set) |
the id of the cursor to fetch from |
The number of rows to skip |
The number of rows to fetch |
- Receive:
- If error status is 1
length of the error |
error string |
- If error status is 0
cursor id |
whether result set was suspended or not: 1 or 0 |
whether the server knows the total number of rows or not: 1 or 0 |
- If server knows the total number of rows:
whether the server knows the number of affected rows or not: 1 or 0 |
- If server knows the number of affected rows:
whether the server is sending column info or not: 1 or 0 |
column count |
whether the server is sending column types as strings or predefined id's: 1 or 0 |
- If server is sending column info:
- For each column:
- Column Name:
- Column Type:
- If server is sending column type id's:
- If server is sending column types as strings:
type string length |
type string |
- Column Sizes:
- Column Flags:
is nullable (1 or 0) |
is primary key (1 or 0) |
is unique (1 or 0) |
- Loop:
output bind variable type: 0,1,2,4 or 5 |
- If type is 0 then the data is null
- If type is 1 then the data is a string
output bind value length |
output bind value |
- If type is 2 then the data is BLOB/CLOB data
- Loop:
- If type is 1 then chunk is string data
- If type is 3 then break out of the loop
- If type is 4 then the data is a cursor id
- If type is 5 then break out of loop
- Loop, Receiving:
- If field type is 0 then the data is null
- If field type is 1 then the data is normal data
length of the data |
the data |
- If field type is 2 then this is a chunk of long data
length of the chunk |
the chunk |
- If field type is 3 then this is the end of the long data
- If field type is 4 (end of result set) then break out of the loop
- If aborting a result set:
- Send:
2 (abort a result set) |
the id of the cursor to abort |
- If suspending a result set:
- Send:
3 (suspend a result set) |
the id of the cursor to suspend |
- If resuming a previously suspended result set: