Using LSL to Exchange Data With SQL Server

A scripted object can initiate an HTTP request and either transmit data to a SQL Server instance on some other server, or request data which will be returned by the HTTP response. There is quite a variety of applications for this in Second Life.

For one example, retail sites may wish to keep track of visitors and customers, getting an idea of when peak traffic hours occur. Additionally, there may be a need to track the spending habits of customers, and to identify those who return and buy more often. Alternatively, the right of a particular avatar to operate a scripted object might be determined by querying a remote database list of authorized users. Either way, it is fairly straightforward to provide this kind of capability by having the scripted object use HTTP.

Acquiring Data for Transmission and Storage

LSL provides several ways to acquire data. One of the most common approaches is to use the llSensor() and llSensorRepeat() functions. As one might imagine, the latter function will cause repeated sensor scans, while the former performs a scan only once. Arguments for both provide for filtering according to name, key, and type of object, or avatar, to be sensed, as well as two parameters to determine the scope of the scan. The llSensorRepeat() function additionally provides an argument to set the rate of scanning.

In both cases, when something meeting the filter criteria is sensed, the results are returned to an event handler, with the signature

sensor(integer total_number)

where the argument gives the total number of objects, or avatars, which were sensed.

There are a series of functions which can be called in this event handler which can then provide the acquired data. One typically sets up a loop to get the desired data for all of the objects sensed:

sensor(integer total_number)
{
integer i;
for(i=0;i<total_number;i++)
{
key detectedKey = llDetectedKey(i); // key of this detected object or avatar.
string detectedName = llDetectedName(i); // name of detected object or avatar
<additional processing and/or transmission of data acquired …>
}

}

Other similar functions get a variety of other data including position, velocity and type of each object or avatar detected.

Additional data can be acquired within the script of vendor objects. The scripts for vendors already have the key and name of the avatar making a purchase, as well as the amount paid and the name of the object being sold. Vendor scripts may be modified to add the HTTP communication to be described below.

Transmitting Data Using HTTP

Once acquired, data is transmitted to a remote website using the function

key llHTTPRequest(string URL, list parameters, string body)

where:

URL is the location of the web page receiving the request,

parameters is an LSL list of pairs, each pair containing a constant identifying a pair and a string value.

body is a string containing the body of the request.

The reader is referred to the LSL Wiki for a full description of the llHTTPRequest() function. However, a relatively simple example should give you the idea. Note that the body of the request is empty since the GET method is used, hence the data name/value pairs are appended to the URL. This function would be a global one, not included in any of the states, and would be called within one of the states of the script. It assumes that the key and name of an avatar have been acquired and are sent as arguments to the function.

sendHTTPRequest(key avatarKey, string avatarName)

{

string body = “”

string URL = “http://www.YourDomain.com/data.asp?” + “key=” + (string)avatarKey+”&name=”+avatarName

list parameters = [HTTP_METHOD, "GET"]

llHTTPRequest(URL, parameters, body)

}

Receiving the Data at the Web Site

Note that the script is transmitting to an ASP page rather an ASP.NET page. In this simple case that is all that is required. Of course, if the web site provider has ASP.NET available, or in fact has the appropriate level of the .NET framework, you could even use LINQ, but that is overkill. the webpage doesn’t have to return anything, although it could. In this case the page could simply contain sufficient server side script to connect to the database and perform an INSERT to save the data. The example ASP page assumes that there is a table named VISITORS which has two fields, Name and Key, both of which are declared as type varchar. The page uses the default VbScript for its server side code.

<http>

<body>

<%

‘Create the SQL string
Dim strSQL1,strSQL2,strSQL
strSQL1=”Insert into VISITORS (Name, Key) Values(“
strSQL2=Request.QueryString(“name”)+”,”+Request.QueryString(“key”)+”)”
strSQL=strSQL1 + strSQL2
‘Make the connection to SQL Server
Dim objConn, strConnection, objCommand
strConnection = <insert the connection string for your site here>
Set objConn = Server.CreateObject(“ADODB.Connection”)
Set objCommand = Server.CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = strConnection
objCommand.CommandText = strSQL
objCommand.CommandType = adCmdText
objCommand.Execute
%>
</body>
</html>

The connection string structure will depend on where your website is and how you have to connect to the SQL Server instance. In 1and1.com, typically one has a single database and its name is provided. Connection is made using user name and password as opposed to Windows authentication. Refer to the documentation provided by your provider.

Acquiring Data From SQL Server

A scripted object can initiate an HTTP request to get data, the request to be handled by an ASP page which executes a SELECT statement and returns data which the scripted object will get in the http_response() event handler. We will show the details of this in a subsequent page.