|
The Data Script functions of Pocket PC Creations are accessed via the Data
menu of the toolbar when a project is open.
Pocket
PC Creations supports COM/OLE Automation, making its internal object model
accessible in a variety of ways, including for Custom
Points, Data Script, and
external applications. The
object model is the same for these areas, however the
different methods require slightly different set ups:
Programming
Pocket PC Creations from Other Applications (such as Microsoft Access,
SQL Server, Microsoft Visual Basic, etc)
In
order to control and interact with Pocket PC Creations (PPCC), it must first be
running on the PC. The
first step is to retrieve the PPCC Application object.
In VB or VBScript:
Set
oPPCC = CreateObject(
"PPCC.Application" )
It
may be helpful to add "PPCC" to the Access (or Excel) or VB project's
References,
so that the full type information of PPCC is available in the Access design area
(with the helpful auto-complete, etc). This also allows use of strongly
typed variables, e.g. the above would become:
Dim
oPPCC As PPCC.Application
Set
oPPCC = CreateObject( "PPCC.Application"
)
This
is not necessary, but it can be helpful.
Brief
coverage of the Pocket PC Creations Object Model
(Application,
Project, Database, Session) follows.
Application
Once
you have retrieved the PPCC.Application object, you can communicate with PPCC
and achieve most common (and some uncommon) tasks.
For example, the following VB/VBScript code would iterate through every
project open in PPCC, and synchronise it:
--
BEGIN --
'
Code to synchronise all projects in PPCC
Sub SyncPPCC()
' Get the running PPCC application
Dim
oPPCC As PPCC.Application
Set
oPPCC = CreateObject( "PPCC.Application"
)
' Iterate through each project open in the application
Dim
oProject As PPCC.Project
For Each oProject In oPPCC.Projects
' Synchronise the project
oProject.Synchronise
Next
End
Sub
--
END --
Project
As
you can see if you set up the References, the PPCC.Application object contains a
Projects property which is a collection of the projects open in PPCC. You can
reference a project by number, or by filename if desired, eg:
oPPCC.Projects("C:\Projects\Test.ppc")
or oPPCC.Projects(1)
Additionally,
if a project is not yet open in PPCC you can open or create
a project from file, using:
oPPCC.Projects.Open("C:\Projects\Test.ppc")
The
PPCC.Project object exposes a number of properties that can be used
to edit and access the project. Two key properties are:
Beneath
the Pages property is obviously a lot of project-design
specifics, which if desired can be accessed to learn about the structure
of a project, modify a project on the fly or even create a project from
scratch (possibly based on a database table, etc).
Database
The
PPCC.Database object is a collection of PPCC.Session objects, which represent
sessions stored in the project. It can be enumerated or accessed by index. Also a new session
can be created with the Add method.
Here
is an example that takes the first open project, synchronises it with
a mobile device, then iterates through each session in its database and displays
the contents:
--
BEGIN --
'
Code to synchronise a specific project, and read the session results
Sub
ShowProjectPPCC()
' Get the running PPCC application
Dim
oPPCC As PPCC.Application
Set
oPPCC = CreateObject( "PPCC.Application"
)
' Get the first loaded project
Dim
oProject As PPCC.Project
Set
oProject = oPPCC.Projects(1)
' Synchronise it
oProject.Synchronise
' Get the session database
Dim
oDatabase As PPCC.Database
Set
oDatabase = oProject.Database
' Iterate through the sessions in the database
Dim oSession As PPCC.Session
For Each oSession In oDatabase
' Here, something would be done with the session data. For this
example, the
fields will be listed in a big MsgBox
strMessage = ""
For Each strItem In oSession
strMessage = strMessage & strItem & vbCrLf
Next
MsgBox strMessage
Next
' Here it may be appropriate to delete the sessions, for example, if they
have now
been moved into Access
End
Sub
--
END --
Session
The
other object that has been used in this example is the PPCC.Session object,
which represents a stored project session (data). The Session object is similar
to a dictionary, storing a set of key-value pairs that correspond to the ID
names of points in the project. It has some other information properties (Date,
LastSaved, Name, UnitID) which reflect where and when the session was created
and modified.
The
keys can be accessed by index o.Key(1), as can the values, o.Value(1),
up to o.Count. The more common way to look up values however is to index them by
their key, for example if the project has a point named "Rank", then
you could retrieve the value with o("Rank") or o.Item("Rank").
The session can also be iterated through, in which case it will return key/value
pairs in the form "key=value" (strings).
Sessions
also have some methods that can be of use. The Save method commits
changes to the session to the database, or adds the session to the database if
it is not already in there. (You can tell if a session is in the database via
the Archived property). The Clone method makes a copy of the session, with all
data intact. The new session is of course not "archived", i.e. not in
the database, although you could call its Save method to add it. You can also
use the Delete method, which removes a session from the database. This has no
effect if the session isn't in the database to begin with.
Furthermore,
the SendToLocal method sends the session to a locally connected
Pocket PC. The SendToClient method on the other hand transmits it over the
TCP/IP wireless network to the specified client.
Data
Script
Programming
Pocket PC Creations from Data Script
(See
also: Object Model reference material)
In
some cases a more convenient way to program/script PPCC for tasks is to write script directly in the PPCC Data Script window.
This avoids having to create a separate application in Access, SQL Server or
VB, or another development environment.
The
Data Scripting facility allows PPCC events to be handled by complex
VBScript, to extend the functionality beyond what is offered in the normal
processing pathways (which is limited to XML, HTML, ADO, Excel output and XML
input).
The
Data Script runs on the PC only, and is used to respond to project
events relating to the processing of data (sessions).
While a developer
can create any Subs or Functions he or she wishes, there are four which have
special meaning, and are run automatically by PPCC in response to events:
*
OnIncomingSession is executed when a new project session (data) arrives
from a Pocket PC, either via the ActiveSync cradle or via a wireless TCP/IP
connection. The
session is passed as an argument (for more
information about the PPCC.Session object, see below).
If this function
returns True, normal processing proceeds (as specified in the Results Pathway
dialog). If False,
no further processing is performed.
*
OnSourceSession is executed when a new XML input file is detected
and processed. This
applies only when XML input is enabled, typically
for collaborating with Microsoft BizTalk server.
Once again the
session is passed as an argument, and returning True will allow default
processing (as configured in XML Input options) to continue.
*
OnIdle is executed every few seconds, and is not passed any arguments.
The script can use this opportunity to perform any tasks it wishes.
A good example would be to query a database, or web data source,
and transfer data into PPCC project sessions for dispatch to Pocket PCs.
Any scriptable COM objects can be used to perform these tasks,
such as ADO for accessing data sources.
*
OnTelegram is executed when a telegram is received from a Pocket PC
connected via TCP/IP. Telegrams
can be used for real-time communication
between the server and wireless clients and are supported by the
Telegram custom point.
There
are several differences between working in the Data Script
window and working from an external application.
Firstly,
there is no need to obtain the Application object using GetObject()
or other means. Both
the Application and Project objects are
provided automatically. So
for example:
'
Sync the project:
Project.Synchronise
'
Or even easier:
Synchronise
Both
of the above methods will call the Project object's Synchronise method.
Because the project is the default context, there is no need to type
"Project." when accessing its members.
Similarly,
when scripting within PPCC, objects are often passed as
arguments (such as the session object in the OnIncomingSession procedure).
VBScript
is the language used for writing PPCC Data Script, thus
strong types are not allowed, and
there is no need to declare variables.
The following lines could be used in Visual Basic or Access, but are not appropriate in VBScript:
Dim oProject As PPCC.Project
Set oProject = oPPCC.Projects(1)
Instead
it should just be:
Set oProject = oPPCC.Projects(1)
The Data Scripting functionality can
be used to programmatically store information in a database, using ADO
(rather than ODBC). Data scripting uses the VBScript language.
Example: Adding
Data to a Database
Function
OnIncomingSession (theSession)
' Create database
object and open
Set
oDB = CreateObject( "ADODB.Connection" )
oDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\Database.mdb"
' Create
recordset object and open a table
Set oRS = CreateObject( "ADODB.Recordset" )
oRS.Open "SomeTableName", oDB, 3, 3, 2
'
Add a new record
oRS.AddNew
oRS("Field1") = theSession("PointName1")
oRS("Field2") = theSession("PointName2")
oRS("Field3") = theSession("PointName3")
oRS("Field4") = theSession("PointName4")
oRS.Update
' Close the recordset
object
oRS.Close
Set oRS = Nothing
' Release
the database
Set oDB = Nothing
' Allow default
session processing to continue (change to False to disable
'
normal processing)
OnIncomingSession
= True
End Function
Note:
The UnitID and
Date are a property of the session, rather than a point in the project, and thus
UnitID is:
oRS("userid") = theSession.UnitID
Example: Updating
a Database
Example
for updating a row in a database identified by a customer number:
Function
OnIncomingSession (theSession)
'
This sub demonstrates updating an Access database table row based on the
' supplied session data
' Create an ADO connection and open the database "C:\Access\Customer.mdb"
Set oDB = CreateObject( "ADODB.Connection" )
oDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Access\Customer.mdb"
' Create a SQL string to select the row to update
sSelect = "SELECT * FROM Table1 WHERE CustomerID=" &
theSession("CustomerIDPoint")
' Open a recordset, and execute the select statement
Set oRS = CreateObject( "ADODB.Recordset" )
oRS.Open sSelect, oDB, 3, 3, 1 ' Note the "1" as the last
argument
' Make sure we found an existing row
If Not OrS.EOF Then
' Update the database fields matching specified session point ID
names
oRS("Data11") = theSession("Data11")
oRS("Data27") = theSession("Data27")
oRS("Data32") = theSession("Data32")
oRS("Data46") = theSession("Data46")
' Commit the update
oRS.Update
End If
' Close and release
oRS.Close
Set oRS = Nothing
Set oDB = Nothing
End Function
Example: Adding
Data to Multiple Tables in a Database
Example
for inserting rows of data in two or more tables:
Function OnIncomingSession
(theSession)
' Create database object
and open
Set oDB = CreateObject( "ADODB.Connection"
)
oDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\Database.mdb"
' Create a first
recordset object and open a table called "NameOfTheFirstTable"
Set oRS = CreateObject( "ADODB.Recordset" )
oRS.Open "NameOfTheFirstTable", oDB, 3, 3, 2
' Add a new row to the "NameOfTheFirstTable"
table
oRS.AddNew
oRS("Field1") = theSession("PointName1") ' Add one field
oRS("Field2") = theSession("PointName2") ' Add another field
oRS.Update
' Close the first
recordset object
oRS.Close
Set oRS = Nothing
' Create a second recordset object and open a table called "NameOfTheSecondTable"
Set oRS = CreateObject( "ADODB.Recordset" )
oRS.Open "NameOfTheSecondTable", oDB, 3, 3, 2
' Add a new row to the "NameOfTheSecondTable" table
oRS.AddNew
oRS("Field3") = theSession("PointName3") ' Here we add some other
field
oRS("Field4") = theSession("PointName4") ' And another
oRS.Update
' Close the second recordset object
oRS.Close
Set oRS = Nothing
' Release the database
Set oDB = Nothing
' Allow default session
processing to continue (change to False to disable
' normal processing)
OnIncomingSession = True
End Function
Example: Sending a
Telegram
The Telegram
point (see
Skills tutorial page 4) is the interface for sending and
receiving telegrams. Any incoming
telegrams for a project are passed to the OnTelegram function in
data-script:
Function
OnTelegram(theTelegram)
End Function
The argument "theTelegram"
is the received telegram, just like in OnIncomingSession with "theSession".
The script code can extract values from the telegram in the usual
way:
MsgBox "The TelegramName
is " & theTelegram("TelegramName")
MsgBox "The GUID
is " & theTelegram("GUID")
MsgBox
"The value of the XYZ point is " & theTelegram("XYZ")
MsgBox
"The telegram was sent from unit " & theTelegram.UnitID
Note the special
syntax for UnitID, as UnitID is a property of the telegram, rather
than data in the telegram.
It's also simple
for data script to send telegrams to Pocket PCs. A telegram is
created, then data values are added to it, then it is sent to one or
more Pocket PCs:
Set t =
Project.NewTelegram
t("TelegramName")
= "MyTelegramName"
t("PointName") =
"New Point Value"
t.SendToClient "UnitIDGoesHere"
t.SendToClient
"SendingToAnotherUnitID"
Note how the
UnitID of the target Pocket PC is specified as an argument to the
SendToClient method. This makes it possible to send telegrams upon
any event. However in the common case that a telegram is being sent
in response to one that was received, in the OnTelegram function,
one would usually send the response telegram back to the same unit
that sent the request.
A full example
then:
Function
OnTelegram(theTelegram)
Set reply =
Project.NewTelegram ' Make a new telegram
reply("TelegramName") = theTelegram("TelegramName") ' Use the same
name
reply("PointName") = "Hello, " & theTelegram("PointName") ' Change a
point value
reply.SendToClient theTelegram.UnitID ' Send the reply to the unit
that sent us a telegram
MsgBox
"Replied to the telegram!" ' Show a debugging message box on the
server
End Function
This code
snippet will work with a simple project that contains one
Telegram point, and a single Edit point named "PointName".
Telegram to a
Text File
Here is some example data-script that can save a couple of values
from a telegram to a text file (many could be saved):
Function
OnTelegram( theTelegram )
Set fso = CreateObject( "Scripting.FileSystemObject" )
Set file = fso.CreateTextFile( "c:\filename.txt", True )
file.WriteLine "Received a telegram from " & theTelegram.UnitID &
":"
file.WriteLine "PointName1 = " & theTelegram("PointName1")
file.WriteLine "PointName2 = " & theTelegram("PointName2")
file.Close
End Function
Telegram to a
Database
Function OnTelegram( theTelegram )
' This code demonstrates updating an existing row in a database when
a ' telegram arrives with new information.
' Only respond to a particular TelegramName, "UpdateJob"
' Any other telegrams will be ignored
' (other telegrams could be handled differently, updating different
data) If theTelegram("TelegramName") <> "UpdateJob" Then Exit
Function
' Create an ADO connection and open the database "C:\Access\Jobs.mdb"
Set db = CreateObject( "ADODB.Connection" ) db.Open
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Access\Jobs.mdb"
' Create an SQL string to select the row to update select = "SELECT
* FROM Job WHERE JobID=" & theTelegram("JobIDPoint")
' Open a recordset, and execute the select statement Set rs =
CreateObject( "ADODB.Recordset" ) rs.Open select, db, 3, 3, 1 ' Note
the "1" as the last argument
' Make sure we found an existing row
If Not rs.EOF Then
' Update the "UnitIDAttending" field to match the unit that sent the
telegram
rs("AttendingUnitID") = theTelegram.UnitID
' Update several fields in the database with data from the telegram
rs("Data17") = theTelegram("Data17")
rs("Data22") = theTelegram("Data22")
rs("Data36") = theTelegram("Data36")
' Commit the update
rs.Update
End If
' Close up
rs.Close
Set rs = Nothing
Set db = Nothing
' NOTE: We could also send a reply telegram to the unit, supplying
it with ' some fresh data from the database.
End Function
Example: Sending a Confirmation
Email
Example for sending an email to confirm an
order taken in the field using an email test Marketing
Report project (download both EmailTest.ppc
and EmailTest.ppr files).
' Process incoming sessions
from Pocket PCs
Function OnIncomingSession (theSession)
' Check if the user indicated a confirmation was desired
If theSession("EmailCustomer") = "Yes" Then
' Send the confirmation email using Outlook on the
' PC (not recommended).
SendOutlookEmail theSession
' Send the confirmation email using JMail, a free, dedicated
' email sending object often used in ASP websites.
' UNCOMMENT to test
' SendJMailEmail theSession
End If
' Set the return value to true to indicate that normal
' processing should continue
OnIncomingSession = True
End Function
' This sub sends a confirmation email via Outlook Automation,
' which is less preferrable than using a dedicated email object
' such as JMail.
Sub SendOutlookEmail (theSession)
' Get the email address from the session
sEmail = theSession( "Customer.Email1Address" )
' Set up some text to include in the message
sBody = "Dear " & theSession("Customer.CompanyName") & "," & vbCrLf & vbCrLf
sBody = sBody & "Thank you for placing your order with us:" & vbCrLf
sBody = sBody & "Purchase: $" & CCur( theSession( "TotalCostofOrderbeforeTax"
) ) & vbCrLf
sBody = sBody & "Tax: $" & CCur( theSession( "Taxat10percent" ) ) & vbCrLf
sBody = sBody & "Total: $" & CCur( theSession( "TotalCostofOrderIncludingTax"
) ) & vbCrLf & vbCrLf
sBody = sBody & "Regards," & vbCrLf
sBody = sBody & "CreativityCorp Pty Ltd" & vbCrLf
' Create an instance of Outlook
Set oOutlook = CreateObject( "Outlook.Application" )
' Create a new mail message
Set oMail = oOutlook.CreateItem( olMailItem )
' Add a "to" recipiant to the message
Set oRecip = oMail.Recipients.Add( sEmail )
oRecip.Type = olTo
oRecip.Resolve
' Set the subject and message body
oMail.Subject = "Your Marketing Report Order"
oMail.Body = sBody
' Send the email
oMail.Send
' Clear the objects
Set oMail = Nothing
Set oOutlook = Nothing
End Sub
' This sub sends a confirmation email via JMail, which is
' a free, dedicated email sending object often used in ASP
' enabled web sites.
Sub SendJMailEmail (theSession)
' Get the email address from the session
sEmail = theSession( "Customer.Email1Address" )
' Set up some text to include in the message
sBody = "Dear " & theSession("Customer.CompanyName") & "," & vbCrLf & vbCrLf
sBody = sBody & "Thank you for placing your order with us:" & vbCrLf
sBody = sBody & "Purchase: $" & CCur( theSession( "TotalCostofOrderbeforeTax"
) ) & vbCrLf
sBody = sBody & "Tax: $" & CCur( theSession( "Taxat10percent" ) ) & vbCrLf
sBody = sBody & "Total: $" & CCur( theSession( "TotalCostofOrderIncludingTax"
) ) & vbCrLf & vbCrLf
sBody = sBody & "Regards," & vbCrLf
sBody = sBody & "CreativityCorp Pty Ltd" & vbCrLf
' Create an instance of the JMail object
Set JMail = Server.CreateObject( "JMail.SMTPMail" )
' Add a recipient
JMail.AddRecipient sEmail
' Set the email server
JMail.ServerAddress = "smtp.webcentral.com.au"
' Set the return address
JMail.Sender = "sales@pocketpccreations.com"
' Set the subject and the body
JMail.Subject = "Your Marketing Report Order"
JMail.Body = sBody
' Send the message
JMail.Execute
' Clear the object
Set JMail = Nothing
End Sub
' Process source sessions from XML
Function OnSourceSession (theSession)
OnSourceSession = True ' Default processing
End Function
' Idle processing
Sub OnIdle
' Insert code
End Sub
' Process incoming telegrams
Sub OnTelegram (theTelegram)
' Insert code
End Sub
Example:
Outputting a value based on the
value of a point
In some projects it is necessary to
generate an output in data script based on the value of a point in the
project. In this example, when processing via the Data Pathway, either
"PASS" or "FAIL" is placed in a "Result" Edit point based on the value of a
"ResultMath" Math point.
' Process incoming
sessions from Pocket PCs
Function OnIncomingSession (theSession)
If CStr(theSession("ResultMath")) = "3.0" Then
theSession("Result") = "PASS"
Else
theSession("Result") = "FAIL"
End If
OnIncomingSession = True ' Continue normal processing
End Function
See:
Object Model reference material
|
