At this 2nd part of our serie about how to write and read from a Squeak-Croquet space to a SQL database, we will read the data from the Croquet101 database (table: students) and present it inside a ScrollingText.

The names of the objects used are in the figure. Important to note that StuListText is part of StuList - this last really is not used.

The script glued to the rectangle Click, after the graphic instantialization of the other object is:

  
| page parsedPage thebody clean |
StuListText setCharacters: 'Searching the database.Wait... '.
page:=HTTPSocket httpGet:'http://www.dmu.com/croquet/readstudents.aspx'.
parsedPage := HtmlParser parse: page. 
thebody := parsedPage body.
clean := thebody   contents at:1 .  
clean := clean  asString .
clean := clean copyReplaceAll: '[' with:''. 
clean := clean copyReplaceAll: ']' with:''. 
clean := clean  copyReplaceAll: '#' with: '\'.
clean := clean  copyReplaceAll: ':' with: ''.
StuListText setCharacters: clean withCRs.

• CODE COMMENT
• 1 - All is similar what we have seeing in the last two lessons. We will start an ASPX program (readstudents.aspx) in the server that will return all the data with some trash that need to be cleaned.
• 2 - This data will go to StuListText, respecting the "carriage returns".

The script in JavaScript, to be uploaded to the server having the database, is a little more sophisticated:

<script language="JavaScript" runat="server">
function Page_Load(sender:Object, e:EventArgs):void{
 var msg;
 msg = readDB();
 Response.Write(msg); 
}
function readDB(){ 
var select = "SELECT * FROM students";  
 //The connection
 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(); 
  //The DataSet creation
  var ds:System.Data.DataSet  = new System.Data.DataSet(); 
  var datadapt:System.Data.OleDb.OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(select, oConn); 
  datadapt.Fill(ds, "stu"); 
  var dsT:System.Data.DataSet  = new System.Data.DataSet();
  dsT = ds; 
  var list = new String("");
  var pad : char = " ";
  for(var i=0; i<dsT.Tables["stu"].Rows.Count; i++){
 list = list + dsT.Tables["stu"].Rows[i]["country"].ToString().Replace("_"," ").PadRight(20, pad) + ":"
            +  dsT.Tables["stu"].Rows[i]["name"].ToString().Replace("_"," ")  + "#";
  //All this needs to be in one line, please!
 }
 //To erase the last separator - we don't need it:
 list=list.slice(0,list.lastIndexOf("#"));
 return list;
}
 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>

CODE COMMENTS:

• 1 - We are creating a function readDb that will return the data to the variable msg to be returned to the user by the internet.
• 2 - Creating the object oConn, using the name of the database (bolded), we open it.
• 3 - ASP .NET has the concept of DataSet, an intermediary object that will receive the data. This DataSet here will have the name: stu and , after executed the SQL query: SELECT, it's full of our data. It's a kind of matrix that we will read doing a loop that finish in Rows.Count (the total of rows).
• 4 - We are doing an smart trick. We have wrote the fields of our table replacing "blank" by "underscore" - look the previous lesson. Now, we do a Replace("_"," ")
• 5 - Another trick: we would like that all the names of countries are edited in 20 digits. We use:

       var pad : char = " ";
       ...["country"].ToString().Replace("_"," ").PadRight(20, pad)...

• 6 - The last trick: after each line/register of student we add the symbol "#". Look that, after the transmission, in the Squeak script we replace "#' by "\", the symbol of carriage return - we can't use it directly here.
• 7 - We are using the block: try/catch/finally for error management.

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, ever...

You can test this project!

You can test this project!

Its address is: http://www.dmu.com/Tealand/readstudents12.006.pr.pr

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

Try to install this application and that of the previous lesson side by side in the space, like in the figure:

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