We strive to ensure the PrimSQLData is as easy to use as possible. To communicate
with the database, you place the PrimSQLData_Interface script into the same prim
or prim link set as your calling script. Your script will communicate with
the Interface script, which will in turn handle the communication with the PrimSQLData
server. Here is how you do a query:
- Pass a query via llMessageLinked() to the Interface script.
- Wait for the results in your link_message{} event.
Each PrimSQLData has their own data table and access is restricted to this table.
You do not know the table name, but you use '_TAB_' as a placeholder for the tablename
in your queries. To access the table via query, you must pass:
- Your SL Avatar UUID - this is the UUID of the avatar that signed up for the PrimSQLData
account.
- Key 1 - this is a GUID given to you when you sign up for your account.
- Key 2 - this is another GUID given to you when you sign up for your account
These 3 unique identifiers are your username/passwords necessary for accessing your
data.
--- SCRIPT BEGIN ----------------------------------------------------------------------------------------
// ** PRIMSQLDATA SAMPLE SCRIPT BY RIEFA RENNEBOHM
// ** FOR MORE INFORMATION GO TO WWW.PRIMSQLDATA.COM
//WE ARE USING THE QUERYID TO PROVIDE A UNIQUEID THAT WILL BE RETURN WITH YOUR QUERY
RESULTS
//YOU CAN USE THIS TO KEEP TRACK OF WHICH RESULT BELONGED TO WHICH QUERY
//YOU CAN USE ANY DATA TYPE FOR QUERYID - INTEGER, KEY ETC BECAUSE IT WILL BE CAST
INTO A STRING
integer queryid = 0;
string BuildPrimSQLDataCommand(string queryid, string account_creator_sluuid, string
account_creator_key1, string account_creator_key2, string sql)
{
//DO NOT TOUCH THIS - IT MUST BE FORMATTED THIS WAY
//THIS FORMATTING CODE DOES NOT TO BE IN THIS FUNCTION - YOU CAN PUT
IT ANYWHERE
return "<" + queryid + ">," + "<" + account_creator_sluuid
+ ">," +"<" + account_creator_key1 + ">," +"<" + account_creator_key2
+ ">," +"<" + sql + ">";
}
//get rid of those leading '<' and trailing '>' characters that the primsqldata
server returns to sl
string psd_trim_row(string row)
{
//THIS FORMATTING CODE DOES NOT TO BE IN THIS FUNCTION - YOU CAN PUT IT ANYWHERE
return llGetSubString(row, 1, llStringLength(row) - 2);
}
default { state_entry()
{
//THE CODE WITHIN THIS EVENT CAN BE PUT ANYWHERE - ONTOUCH, TIMER,
REZ, MONEY, ETC
//IT IS IN THE STATE_ENTRY EVEN ONLY FOR DEMO PURPOSES
//LET'S QUERY THE SERVER
//FIRST DEFINE YOUR SQL QUERY
//TRY AN INSERT FIRST SO HAVE SOME DATA IN THE TABLE. AFTER YOU HAVE
DONE THIS SEVERAL TIMES,
//TRY ONE OF THE 'SELECT' QUERIES BELOW
string sql = "INSERT INTO _TAB_ (I0, D0) VALUES(1, GETDATE())";
//USE THIS TO INSERT DATA
//string sql = "SELECT I0, D0 FROM _TAB_"; //USE THIS TO SELECT
DATA
//CHECK THE MICROSOFT SQL 2005 T-SQL DOCUMENTATION FOR MORE QUERY COMMANDS
//WARNING! YOUR PRIMSQLDATA ACCOUNT ALLOWS YOU TO QUERY YOUR OWN DATA
_ONLY_. ALL QUERIES ARE
//LOGGED AND ANALYSED BY ADMINS.
//WARNING! ATTEMPTING ANY TO ACCESS ANY MSSQL OBJECTS OTHER THAN YOUR
_TAB_ TABLE WILL BE
//FLAGGED AND THE ACCOUNT DISABLED.
//next build the command that will be sent to the PrimSQLData Interface
script. You have to do this.
string PrimSQLDataCommand =
BuildPrimSQLDataCommand((string)queryid,
"872d6ef1-b183-4cbf-923d- 3cd758418ccb",
//THIS IS THE SL UUID OF THE AVATAR THE THAT CREATED THE
//PRIMSQLDATA ACCOUNT
"2FCF6BAA-FF91-4110-8803-2B597D12EFD8", //THIS
IS THE KEY1 ASSIGNED TO THE ACCOUNT
"8673C92F-01F2-4273-A9EA-5615429C39E6", //THIS
IS THE KEY2 ASSIGNED TO THE ACCOUNT
sql);
//now send it to the PrimSQLData Interface script. You have to send
it to interface script this way.
llMessageLinked(LINK_THIS, -1, PrimSQLDataCommand, "PSDDUMMY");
queryid ++;
//the results of your query will appear in the link_message event in
this script shown below
}
link_message(integer sender_num, integer num, string str, key id)
{
if (id == "PSD")
{
llOwnerSay("= BEGIN RESPONSE ======================");
list pdr_query_results = llCSV2List(str);
//THIS IS THE QUERYID THAT YOU PASSED TO THE INTERFACE
string psd_queryid = psd_trim_row(llList2String(pdr_query_results,
0));
//THIS IS THE ERROR CODE RETURNED FROM THE PRIMSQLDATA SERVER
string psd_error_code = psd_trim_row(llList2String(pdr_query_results,
1));
//THIS IS THE MESSAGE RETURNED FROM THE PRIMSQLDATA SERVER
string psd_message = psd_trim_row(llList2String(pdr_query_results,
2));
//THIS IS THE NUMBER OF DATA ROWS RETURNED FROM THE PRIMSQLDATA
SERVER
string psd_data_num_rows = psd_trim_row(llList2String(pdr_query_results,
3));
//THESE ARE THE DATA COLUMNS RETURNED FROM THE PRIMSQLDATA SERVER
(COLUMN SCHEMA)
string psd_data_colums = psd_trim_row(llList2String(pdr_query_results,
4));
//THIS IS THE DATA RETURNED FROM THE PRIMSQLDATA SERVER (A DATASET)
string psd_data_rows = psd_trim_row(llList2String(pdr_query_results,
5));
llOwnerSay("psd_queryid: " + psd_queryid);
//NOT NEEDED
llOwnerSay("psd_error_code: " + psd_error_code);
//NOT NEEDED
llOwnerSay("psd_message: " + psd_message);
//NOT NEEDED
llOwnerSay("psd_data_num_rows: " + psd_data_num_rows);
//NOT NEEDED
llOwnerSay("psd_data_columns: " + psd_data_colums);
//NOT NEEDED
llOwnerSay("psd_data_rows: " + psd_data_rows);
//NOT NEEDED
llOwnerSay("= END RESPONSE ======================");
}
}
}
--- SCRIPT END ----------------------------------------------------------------------------------------
Here we see the output from the insert query reponse generated from the script above.
It shows that it was successful and returned no rows or columns. You can use
the queryid to check to see if your query was processed or lost somewhere along
the way. This allows you to build retry code and try to make an aproximation
of transactionality.
[0:22] PrimSQLData_Test_Cube: = BEGIN RESPONSE ======================
[0:22] PrimSQLData_Test_Cube: psd_queryid: 0
[0:22] PrimSQLData_Test_Cube: psd_error_code:
NO_ERROR
[0:22] PrimSQLData_Test_Cube: psd_message: [12 ms]
[0:22] PrimSQLData_Test_Cube:
psd_data_num_rows: 0
[0:22] PrimSQLData_Test_Cube: psd_data_columns: <>
[0:22]
PrimSQLData_Test_Cube: psd_data_rows: <>
[0:22] PrimSQLData_Test_Cube: = END RESPONSE ======================
Here we see the output from a select query reponse generated from the script above.
It shows that it was successful and returned no rows or columns. You can dump
to psd_data_rows into a list to iterate through them like a dataset. The columns
returned are 'I0' and 'D0'. Your data table has the following schema:
- 10 Integer Columns (I0, I1, etc)
- 10 Bigint Columns (G0, G1, etc)
- 10 Float Columns (F0, F1, etc)
- 10 Bit Columns (B0, B1, etc)
- 10 Datetime Columns (D0, D1, etc)
- 12 Varchar Columns of various sizes (C0, C1, etc)
There are indexes on ALL of the columns.
[0:41] PrimSQLData_Test_Cube: = BEGIN RESPONSE ======================
[0:41] PrimSQLData_Test_Cube: psd_queryid: 1
[0:41] PrimSQLData_Test_Cube: psd_error_code: NO_ERROR
[0:41] PrimSQLData_Test_Cube: psd_message: 9 ROW(S) AFFECTED [0 ms]
[0:41] PrimSQLData_Test_Cube: psd_data_num_rows: 9
[0:41] PrimSQLData_Test_Cube: psd_data_columns: I0,D0
[0:41] PrimSQLData_Test_Cube: psd_data_rows: <1,6/3/2007 12:22:27 AM><1,6/3/2007
12:22:16 AM><1,5/31/2007 11:23:33 PM><1,5/31/2007 11:20:22 PM><1,5/31/2007
10:16:18 PM><1,5/31/2007 10:15:06 PM><1,5/31/2007 9:38:02 PM><1,5/31/2007
9:31:35 PM><1,5/31/2007 9:31:20 PM>
[0:41] PrimSQLData_Test_Cube: = END RESPONSE ======================