To understand this serie of 2 lessons about how to access SQL databases from a Croquet space, you need to have understood the previous lesson, about 3 tiers applications and did the exercice.

The new exercice we will do now, will be the creation of a database (name: Croquet101) having a file/table (name:students) with the fields/columns: name - 40 digits - and country - 20 digits - where you and the other students of this course can write their names for posterity - you are the pioneers in Croquet use in the world!

First of all we need to create the MS Access database (a .MDB file) and upload it to the server - in our case: www.dmu.com.

I will use the product MS Access but some webhosters, having the server facilities we talked at the previous lesson (Windows, ASP .NET and Access), have sometimes "on-site tools" and you - if you try to repeat the exercice - can not need to have the product.

Our idea is the creation of 2 Squeak projects that will be placed in the SOLO area.

At one you will type your name and country and, at the other, you will see also the names and countries of your colleagues. At this first lesson we will create the project to write data. We will have the objects of the figure:

The script will be glued to the object Click. So, we need to made " graphic instantializations" - like ever - of the others, inside the "Script Editor".

The code of the Squeak script will be (remember the "problem" of the versions...):

| page parsedPage thebody clean |
Msg  setCharacters:'Data sended.Wait...'.
page:=HTTPSocket httpGet:'http://www.dmu.com/croquet/writestudents.aspx?name=', Name getCharacters,'&country=',Country getCharacters.
parsedPage := HtmlParser parse: page. 
thebody := parsedPage body.
clean := thebody   contents at:1 .  
clean := clean  asString .
clean := clean copyReplaceAll: '[' with:''. 
clean := clean copyReplaceAll: ']' with:''. 
Msg setCharacters: clean.

CODE COMMENTS

• 1 - This script will run when the user clicks the button (a rectangle) so: we print a new message in Msg to confirm this.
• 2 - We saw, in the previous lesson, how we send data to the ASPX application - we will talk about it soon - in the server. Here we are sending 2 variables (name and country). We are picking the values from the fields typed. The separator is the symbol:"&".
• 3 - After sended, the application will run in the server and a message will return to the client. We need to clean it - the same way we did in the previous lesson - and print this message in the Msg field.

Look the code of the ASPX application (writestudents.aspx) in JavaScript .NET, uploaded to the Windows server:

<script language="JavaScript" runat="server">
function Page_Load(sender:Object, e:EventArgs) : void {
 var  name=this.Request.Params["name"];
 var  country=this.Request.Params["country"];
 name=name.Replace(" ","_");
 country=country.Replace(" ","_");
 var msg;
 msg = insertStu(name ,country); 
 Response.Write("DB MESSAGE="+msg); 
}
 
function insertStu(name,country){
 var oConn:System.Data.OleDb.OleDbConnection = new System.Data.OleDb.OleDbConnection(); 
 oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/data/croquet101.mdb"); 
 try{ 
  oConn.Open();
  var oCmd:System.Data.OleDb.OleDbCommand  = new System.Data.OleDb.OleDbCommand();
  oCmd = oConn.CreateCommand(); 
  oCmd.Connection = oConn;
   //Inserting the data
  oCmd.CommandText = "INSERT INTO students (name,country) VALUES('"+ name+ "','" + country + "')";
  oCmd.ExecuteNonQuery(); 
  return "Values registered!" ;
 }
 catch(e:Exception){
  //Write the error, if we have one
  return "Error: "+  e.ToString();
 }
 finally{
  //Having error or not we close the connection, a good pratice.
  oConn.Close();
 }
}
</script> 

• 1 - The program read the two variables and their values. If there are "blanks", they are replaced by "underscore".
• 2 - We created a function insertStu having these values by parameters. This function will return the message to be sended to the client.
• 3 - At the function, we begin opening the database. Look (in bold) the name and path of the database in the server (/data/croquet101.mdb). In some web-hosters the standard folder for Access databases is : db. At our web-hoster (http://home.verio.com/) it's: data
• 4 - The JavaScript block: try-catch-finally is for error management.
• 5 - Having an object-reference of the connection: oConn, we use it to open the db and to create an object oCmd for commands. The command is the usual SQL query: INSERT, mounted using the values received.
• If the INSERT was OK, the function returns the message: "Values registered", that will be sended to the user.

TIP: Having this ASPX script and changing the bolded part you can use .NET without to know too much about it... All is more or less the same...

You can test this project!

Its address is: http://www.dmu.com/Tealand/writestudents1.005.pr

And re-read the lesson about how to create a 3D window for a project. Use for "extent": 1400 x 1050.

After installed the application inside the Croquet environment, write your name and country in our database and you will read it in the future, using the application of the next lesson.

WARNING. If you don't have a powerfull computer and a fast communication line it's possible that you will not have a very responsible application. We are doing our tests at a: Notebook DELL Latitude D600 - Intel Pentium M755 2.0 GHZ - Memory: 512 MB - VideoCard ATI Radeon 9000 32MB DDR 4xAGP - Resolution 1400x1050 - Communication: ADSL 256 kbps (Download: 45 kBps, Upload: 14 kBps) - Windows XP Professional SP1

            PREVIOUS LESSON            NEXT  LESSON                 
T. CONTENTS           HOMEPAGE