smarter Dimensions

Learn ODBC : Finding a record

Monday, February 1st, 2010 | Code Corner, Custom, IBM SPSS Professional, Learn ODBC, mrStudio

In this second article about ODBC we will begin to explore the ADODB.Recordset object. We will take what we have learnt previously and use it to write a function that will connect to a database table using the ADODB.Connection object and find a specific record, once found we will return its id.

The first thing we need is some metadata, we will use the same metadata as before but add a Name question to it. This will be the field we will use to find a specific record.

Metadata(en-AU, Question, Label)
 ConnectionInfo "{Info}"
    info;
 Name "Name" text;
End Metadata

Next we will start to create our routing. Enter the following

Dim oInfo
  Set oInfo = CreateObject("Scripting.Dictionary")
  oInfo.Add("Message","")
  oInfo.Add("ID",-1)

The first thing we have done is to create an object that will be used to pass information back and forth from our functions ( oInfo ). We are going to set this variable up as an object and it will be a Dictionary object. This type of object is like an Array , but unlike arrays you don’t have to write code to search for the item you have added to the array because the feature is already there. Next we add to items to the list ( Message and ID ) and we set their values to be ( “” and -1 ). Next add the following lines of code.

Set oInfo = FindVDataRecord("10.64.4.22","ADO",
     "VDATA","Respondent.Serial","Name = 'Test'",
     "sa","Passw2rd",oInfo)
ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
ConnectionInfo.Show()

The first new line that we have just added , might look complicated , but it is quite simple really. What we have said here is pass into the function ( FindVDataRecord ) , not created yet by the way , the server name , again this assumes the sql server is on the same machine as the Data Collection software. Next we pass in the Database Name , Table Name and then the variable in the database that is classed as the KEY. By Key we mean a field or set of fields that allows us to uniquely identify a record and in the case of a DataCollection survey the key variable is the respondent.serial. Next we have the WHERE clause.

"Name = 'Test'

So what is a where clause, well if we are looking for something we need to specify some rules on how to find it. So in our case we are saying Look at all the records where in the name variable we see the words ‘Test’. And then finally in our function we have the username and password that will be used to connect to the database followed by the info object that will be used to pass back information to our calling procedure. You might be thinking , why are we using an object , when we could just return the ID and to be honest sometimes we would do this, but in this instance , we want to pass back or return , more than one thing and the only way you can do that is to use an object that allows you to add more than one thing to it.

Once we have passed everything into our function and the function has done what it is meant to do , it sends back the info object so that we can see what has happened. And finally we do a label insert on our info question so that we can see the messages that have been returned.

And finally we are going to add the following to our routing.

If ( oInfo.item["ID"] <> -1 ) Then
' Do something
End If
Name.ask()

We have added this code at this stage, just to show you how to get at the ID that has been returned, and in this case we have put in some logic to say if the ID is not -1 then do something. We use -1 , and you will see where we set this in the function as a way of saying “Record Not Found”. So what does the function look like ,

Function FindVDataRecord(sServer,sDatabase,sTable,sKey,sWhere,sUser,sPassword,oReturnObject)
Dim oConnection, oRecordset
Dim sInfo , sConnection, sSQL
On Error Goto ErrorMessage
Set oConnection = CreateObject("ADODB.Connection")
sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword
sConnection = sConnection + ";Persist Security Info=True;User ID="
sConnection = sConnection + sUser + ";Initial Catalog=" + sDatabase +
sConnection = sConnection + ";Data Source=" + sServer
sConnection = sConnection + "';Initial Catalog=\\" + sServer +
sConnection = sConnection + "\SPSSMR_FMROOT\Master\" + sDatabase + "\"
sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase
oConnection.Open(sConnection)
If ( oConnection.State = 1 ) Then
    sInfo = "Connection to the Database worked"
    ' TODO : Add Recordset Code Here
Else
    oReturnObject.Remove("Message")
    oReturnObject.Add("Message", "Connection to the Database failed")
End If
oConnection.Close()
Goto EndOfFunction
ErrorMessage:
   sInfo = sInfo + "Error :<br />"
   sInfo = sInfo + Err.Description + "<br />"
   sInfo = sInfo + CTEXT(Err.LineNumber)
   oReturnObject.Remove("Message")
   oReturnObject.Add("Message", sInfo)
EndOfFunction:
Set oConnection = Null
Set FindVDataRecord = oReturnObject
End Function

Hopefully you should see that it looks very similar to the code that we wrote in the previous article. All we have done to change it is to add some “Error Trapping” and also code to add any items that we want sent back to the calling program via the info object.

Once you have all this code in place you should be able to activate your survey , we called it “ADO” and see some messages sent back. If all is working ok you should just see “Connection to the Database worked” if you get any messages that you cannot work out the please feel free to send us your MDD and we will help you fix them up.

Next add the following underneath the ‘TODO

Set oRecordset = CreateObject("ADODB.Recordset")
sSQL = "SELECT " + sKey + " FROM " + sTable + " WHERE " + sWhere
oRecordSet.Open(sSQL,oConnection,3,1)
If ( oRecordSet.EOF = true and oRecordset.BOF = true ) Then
    sInfo = sInfo + "<br />Name Not Found"
    oReturnObject.Remove("ID")
    oReturnObject.Add("ID", -1)
Else
    sInfo = sInfo + "<br />Name Found !!!"
    oReturnObject.Remove("ID")
    oReturnObject.Add("ID", oRecordset[sKey].Value)
End if
oRecordset.Close()
oReturnObject.Remove("Message")
oReturnObject.Add("Message", sInfo)

The first new line we have added has created the object that we will use to get at the records. and for us to use this object we need to provide it with some information. The first thing is the sql statement that will find us the record that we are looking for, which in our case is the record that has the name question set to the value ‘Test’. We are going to store this sql statement into a sting called sSQL and if we looked at it, the contents of the string would hold

"SELECT respondent.serial FROM vdata WHERE name = 'Test'"

Once we have our statement we then need to pass it to the oRecordset object , followed by the connection object that we have established. Next we pass it two values and these values tell the SQL server how we are going to read and lock the records. If you would like to find out more about these values then you can look here , or you can just take it from us that these will work fine for what we want to do.

Once we have opened our recordset , like the connection object we need to check to see if we have found or returned any records. We do this by checking the EOF ( end of File ) and BOF ( beginning of file ) properties of the recordset object. And if both these settings are true then it means that we have not found any records so we need to report the fact.

On the other had if one of them is false , then this means we have found a record or records and we need to set the value of the Key field found to the ID object of our info item. As we can see this is done with the following code using the, oRecordset[FIELDNAME].value , where FIELDNAME is a valid FIELDNAME used in the select statement we submitted earlier.

And finally we need to make sure that after opening our recordset we tidy up and close the recordset connection down. If you Run this code and you don’t find the record you are looking for then you will see a message like this one.

Otherwise if all is well you will see this.

And there you have it , it might be a lot of lines of code, but you have created a function that will find a record in a table on an SQL server. In our next article you will see what we could do with this ID once we have found it.

The full code for this article can be found in :http://CodeCorner.SmaterDimensions.Com @ Connect to a Data Collection Survey and find a record

No comments yet.

Leave a comment