You only need to remember two basic rules when dealing with the
UnityDB SQL database as we move forward:
First, all read queries must use views. Do not query directly on the
"raw" tables in SQL. Currently there are views for all the tables and
as of 4.0(2) they're a 1 to 1 mapping to the raw tables. However, in
4.0(3) there was a major revision in the back end that involved moving
the DTMFAccessID (extension) columns out of all the tables and
consolidating them into the DTMFAccessID table. The views for all the
tables affected continued to expose the DTMFAccessID by referencing
the DTMFAccessID table for you in the background. Folks that were
going directly to the raw tables had to go back and change their code
to account for this and were unhappy. I can say that for certain
since I was one of those folks who wrote code for 3.x versions of
Unity that didn't have views and I didn't bother to update all my code
to use views when 4.0(x) hit the streets. Don't be like me. If
you're going directly to the raw tables your applications may break
with every new release of Unity and you'll get real annoyed in a
hurry, so don't do that.
A quick note to all you new CUDLE users: you can switch back and forth
between looking at the raw tables and views under the "options" menu
using the "Use Views" and the "Use Tables" options. I highly
recommend leaving it in views mode for most of what you do.
Second, all adds and updates must go through stored procedures. While
it's tempting to just flip that value onto the raw table and hit the
update method, do not do this. Again, backwards compatibility as well
as some basic value checking will be done within stored procedures to
prevent problems with future versions of Unity breaking client
applications. As you'll see in the examples, the stored procedures
are also designed to make your life easier and offer you some options
that would otherwise be very tedious and error prone for you to do on
your own. If you're deleting an entire record in most cases you also
want to go through a stored procedure for that. There is only one
thing we'll be covering in here where I'll write you a note and allow
you to edit information directly on the table since there is no stored
procedure available at the time of this writing that updates the
MessagingRule (greeting) table. The release of 4.0(4) should include
that stored procedure in which case I'll be coming around to take that
note back.
Exercises
If you're like me, the easiest way to learn something is by simply
saddling up and wading through a bunch of examples. As such you may
have jumped right to this section without having covered any of the
previous material. As a fellow "just show me" guy I understand this,
however you really need to have covered at least two earlier chapters
before jumping into this. If you haven't read the Architecture
Overview and the Data Object Model chapters, please take a bit and go
do that before proceeding. All the examples laid out here assume you
know what is meant by having to synchronize data between SQL and the
directory, how all the collections and sub collections in the database
are used, how objects are linked together and the like. You will have
a hard time gleaning useful information here if you haven't got those
basics under your belt first.
We'll start with the simple connection basics, then we'll move on to
some fairly easy read only exercises to get our legs under us. Then
we'll progress to more advanced subscriber creation, import,
modification and deletion scenarios later.
Finding and connecting to a Unity server on the network
The first thing we'll need to do is figure out how to find and connect
to remote Unity servers on your network. If you know the domain and
server name of the Unity server you want to connect to, it's just a
matter of constructing the SQL connection string properly and you can
jump ahead a few sections to "Getting Version and License Info". If,
instead, you want to make your applications capable of "browsing" the
network and finding Unity servers to connect to dynamically, press
ahead with me.
You can download a sample application that has a full listing of this
code and includes nifty reusable modules for populating domain and
server lists for you. This is the "Attaching to Unity Remotely Code"
on the code samples page on www.CiscoUnityTools.com. We'll cover the
high points here.
Get a list of all servers running SQL on a domain
The easiest way to find Unity servers in a domain is to first start
with the list of all servers running SQL on them. The fine folks at
Microsoft were nice enough to include a snappy little WinAPI call to
do just exactly this type of thing. This function pulls all the
servers that are running MS SQL server (full SQL 2000, SQL 7, MSDE
etc...) on them and returns them as a collection. The calling function
will have to determine if Unity is installed on that box by attempting
to open the UnityDB database on that server. You'll find the full
listing of this function and examples of how it's used in the
"Attaching to Unity Remotely" code sample on www.CiscoUnityTools.com.
This particular function is a bit more advanced than most other items
we'll be covering since it involves dancing with the Windows API,
moving chunks of memory around and dealing with pointers in VB which
is not particularly trivial. If you're a newbie, don't freak out -
just use the code chunk I provide and move on with life. For you more
experienced VBers out there, this should establish some street cred'
for me before moving on.
'This function returns a collection that includes the names of all the
servers that are running SQL on them. A collection is a simple VB
object that is basically a fancy way of budling up a list of items you
want to iterate over without having to deal with creating an array of
objects on your own. You need to pass in the name of the domain you
want to look for servers in. In the full code sample I also show you
how to get a list of the domains you can "see" from the server you're
running the application on.
Public Function getServers(vDomain As Variant) As Collection
Dim pszTemp As String, pszServer As String, pszDomain As String
Dim nLevel As Long, i As Long, BufPtr As Long, TempBufPtr As Long
Dim nPrefMaxLen As Long, nEntriesRead As Long, nTotalEntries As Long
Dim nServerType As Long, nResumeHandle As Long, nRes As Long
'The SERVER_INFO_101 structure is defined in the full code listing -
it's an object that contains a number of pieces of information about a
server including, of course, it's name.
Dim ServerInfo As SERVER_INFO_101
Dim collServers As New Collection
pszTemp = vDomain
'The WinAPI calls to go fetch the servers we want to look at needs
unicode strings to be passed in. We have to manually construct that
here. VB provides a snappy little function to do just this type of
thing called "StrConv". The resulting Unicode string will be stored
in the pszDomain string.
If Len(pszTemp) = 0 Then
pszDomain = vbNullString
Else
pszDomain = StrConv(pszTemp, vbUnicode)
End If
nLevel = 101
BufPtr = 0
nPrefMaxLen = &HFFFFFFFF
nEntriesRead = 0
nTotalEntries = 0
'The nServerType flag tells the API call that we want to find all
servers in the selected domain that are running SQL server on it. All
the flags you can pass in are defined at the top of this module in the
full program listing. You can also use this to find all Exchange
servers, domain servers etc...
nServerType = SV_TYPE_SQLSERVER
nResumeHandle = 0
pszServer = vbNullString
'loop through all the list of servers returned and add them to the
collection of server names to be returned from this function. The
trick is the nEntriesRead value we pass in gets incremented each time
we call it - keep looping through until the number of entries read
equals the total number of entries found.
Do
nRes = NetServerEnum(pszServer, nLevel, BufPtr, nPrefMaxLen,
nEntriesRead, nTotalEntries, nServerType, pszDomain, nResumeHandle)
If ((nRes = ERROR_SUCCESS) Or (nRes = ERROR_MORE_DATA)) And
(nEntriesRead > 0) Then
'The function actually returns the server info as a pointer to a
block of memory parked somewhere. VB doesn't deal with pointers
natively so you need to use the RtlMoveMemory function to move the
info from memory into the ServerInfo structure.
TempBufPtr = BufPtr
For i = 1 To nEntriesRead
RtlMoveMemory ServerInfo, TempBufPtr, SIZE_SI_101
'Now the server info structure contains an address in memory
where a string that contains the name of the server is stored. Use
the PointertoString function to go fetch the string out of there and
stick it in our collection.
collServers.Add PointerToString(ServerInfo.lpszServerName)
TempBufPtr = TempBufPtr + SIZE_SI_101
Next i
Else
MsgBox "Error reading SQL servers from the domain"
Exit Do
End If
'free up the memory we allocated to read the SQL server info into.
NetApiBufferFree (BufPtr)
Loop While nEntriesRead < nTotalEntries
Set getServers = collServers
Exit Function
The calling party will get a nice collection of server name strings
they can iterate over to populate list boxes, drop downs, grids or
whatever here. With the server name string in hand it's just a matter
of constructing the appropriate connection string and attempting to
bind to the UnityDb SQL database, if it exists, on that server. We'll
cover that next.
Deciding on a Connection Method
In all my examples here, as in most of my actual projects, I use ADO
to connect to SQL. The ins and outs of using Dynamic vs. Keysets vs.
ForwardOnly vs. Static recordsets and choosing between client side and
server side "cursors" used by ADO here is a chapter unto itself.
There are a number of good ADO books out there that you would do well
to spend some time with if you plan to get into this with gusto.
Here's the very short version.
With ADO when you open a database you're actually creating a "cursor"
which is a temporary database structure on the local box your
application is running on. This cursor contains the actual data from
the database query you made back to the server or tables of pointers
back to the rows in the server side database with the data you
requested. When your application is looking for things, changing or
deleting rows this is actually done on the cursor structure locally
and then transmitted back to the Unity server's database for you via
the drivers under the ADO libraries you're using on the client side
here. RDO (Remote Data Objects), DAO (Data Access Objects) and other
database connection libraries use similar mechanisms, ADO just happens
to be the most mature, most widely supported and nicest to use in my
opinion so that's what I go with. You will catch me using DAO on rare
occasions when I'm pulling in lots of info from external text files
(i.e. when parsing a log file for reports). DAO has a noticeably
faster "add record" capability than ADO does and this speeds things up
a bit. However DAO and ADO are just different enough that you can get
yourself in trouble mixing and matching here so if you're going to go
that route, definitely get yourself a good book and understand the
differences.
A "client side" cursor does what it sounds like - the cursor is
maintained on the local machine your client is running on and makes
the necessary requests back to the server as necessary. This scales
well especially for large numbers of clients doing a lot of thrashing
since the minimum overhead is added back to the main server database.
This is what we want when dealing with Unity since it's real time call
processing capabilities are paramount here, administrative functions
are in the back seat so we want to add as little overhead to the
server as we can get away with.
A "server side" cursor sends all your queries back to the server and
the temporary results are generated there which can be a big
performance boost for your applications but adds overhead on the
server itself. This is bad, don't do this. We'll talk about indexing
tricks you can do on your client side cursor to help speed things up a
bit and since most of the types of things you'll need to do on Unity
are quick "in and out" operations such as adding, updating or deleting
individual users, client side cursors are ideal.
The four recordset types are designed for different types of
client-server applications. Here's my "elevator pitch" for the
reasons I used the different ADO recordset types:
* When reading in a large number of records into a bound data control
such as a grid for display/lookup functions, I use adOpenForwardOnly
with an AdLockReadOnly flag. This is known in the biz as a "firehose"
cursor since, as you may have gathered, it's very fast at slurping in
data and passing it off. You can't move around in this cursor, do
searches or filters or anything of the kind but if you're passing the
information into another structure such as a bound grid or list
control with it's own table handling capabilities or an array or
whatnot this cursor is lightning fast and is the way to go.
* Using the AdOpenKeyset flag actually populates the local cursor with
pointers back to the rows in the server side database using unique
identifiers, or "keys", for the tables in question. Only a subset of
the actual data for some rows are returned and it will go out and
fetch the info it needs if you reference rows not in it's local cache.
When other clients make changes to the information back on the server
a keyset recordset will pick that up for you when you reference it.
It wont, however, pick up new rows added by other clients and when you
reference a row that's been deleted out from under you it throws an
error you need to trap and handle. If you open a keyset recordset as
a client side cursor, however, it behaves like an updatable static
recordset which we'll talk about in a minute. This is the recordset I
use in almost all situations. Keyset and Static cursors are also the
only ones that support the RecordCount property which can be handy in
many situations. For instance if you search on an alias in a table
you expect to get only one row back and it's nice to be able to just
make a quick check up front.
* Using the AdOpenStatic flag opens a static cursor which, as the name
implies, is a static snapshot of the data that matches your query. It
pulls all the data over in one shot and you can do all the things you
would expect such as filtering, searching, moving forward and
backwards, getting record counts and the like. It does not pick up
changes made by other clients on the data in your result set and this
cursor is traditionally read only, although you can force it to do
updates. As noted above using the Keyset cursor on the client side
behaves very similarly but it more appropriate for making updates back
to the server.
* The AdOpenDynamic flag is similar to a keyset recordset except that
it picks up adds and deletes on the fly and simply adjusts the
recordset on the fly. Rather than only running out and updating the
rows in the cursor based on keys it reissues the query back to the
server and pulls over deltas. I have never had a need to use a
dynamic cursor in my applications I write for Unity. You can do
filters, searches, sorts and all that good stuff just as you can with
Keyset recordsets, however it does not provide a snappy count of the
records in the set.
Yes, some hard core DBers out there are howling about using mixed
cursor types. Yes these do exist. They are evil, behave
inconsistently over different providers and should be avoided. Get
out of the classroom (or your mother's basement) and go do something
productive.
Since most of my applications use client side keyset recordsets it's
up to me to check for updated info on the server or new and deleted
rows in the results of my query. You can use the "resync" command off
the recordset object which will refresh all the rows currently in your
recordset with updated info from the server's database or you can use
the "requery" method which will reexecute your original query that
produced the recordset in the first place and bring over the
differences (which includes removing rows that have been deleted and
adding new rows that match the query). The "requery" method is by far
the more useful of the two. Any changes you make to your local cursor
will not be written back to the server database automatically as they
would be with a server side cursor. It's up to you to execute the
"update" property on the recordset. The local cursor engine will
translate your changes into "action queries" (neat term for generating
some TSQL looking command strings) back to the server. In this way
you can make a number of changes locally and issue one large update
back to the server if you like which can be handy in some scenarios.
As noted this helps keep the overhead on the server itself to a
minimum which is a good thing. That said, you will only be making
changes directly in your recordsets in rare cases where there is no
stored procedure available to do what you need. In 4.0(3) there's
only one valid reason for that which we'll cover later in this
chapter's examples section. All other updates should be done via
calls to stored procedures.
Any recordset type can be opened with any of the read flag options
(AdLockReadOnly, adLockOptimistic, adLockBatchOptimistic or
adLockPessimistic). For my needs I only ever use the ReadOnly or
Optimistic record locking flags. The Audio Text Manager did initially
start using LockBatchOptimistic for dealing with rolling back changes
until a user had "committed" them and handling nicer logging of
updates and the like for which it can be very handy. However the
overhead involved with keeping this model flying got too great and we
simplified it to make changes to the back end immediately.
A quick word about client vs. server side recordsets and indexing. As
noted above you always want to force recordsets to run on the client
side when dealing with Unity to preserve as many server side CPU
cycles for handling calls and other important voice server activity.
Once nice thing about client side cursors is you can easily force it
to build a keyed index for you which will dramatically improve lookup
performance if your application is going to be doing a lot of lookups
in the result set. For instance if you're slurping in a large text
file and then doing reports processing on the results, indexing your
lookup keys is essential for performance. If you're adding and
reading data to tables in, say, an Access databases you're
constructing on the fly or you've made ahead of time you can simply
mark keys for indexing in the database itself. This is what I've done
with some of the report generation applications such as the Bridge
Traffic Analyzer. If you're constructing cursors from SQL, however,
you have to explicitly tell it to index one or more columns if you
want. There's a small hit when constructing the cursor but the
increase in performance when reading will more than make up for it if
you're going to be doing any kind of repeated lookups in the result
set.
To force a particular column or columns to be indexed in your cursor,
you need to use the "fields" type and flag each column for indexing.
The following sample shows how I open some cursors in the "Port Usage
Analyzer" tool:
Dim rsRawData as ADODB.Recordset
Dim f as ADODB.Field
Set rsRawData = New ADODB.Recordset
rsRawData.CursorLocation = adUseClient
rsRawData.Open "Select * from RawPortData" ORDER BY Port",
strConnectionString, adOpenKeyset, adLockReadOnly
Set f = rsRawData("Port")
f.Properties("Optimize") = True
Set f = rsRawData("CallType")
f.Properties("Optimize") = True
Notice that I force the cursor type to be on the client side before
opening the database. The default is for server side cursors so be
sure to do this in your applications as well. Setting the "Optimize"
property on the fields collection forces the cursor to create a local
index table for all columns you flag that for. The difference in
performance on the port data crunching was in the neighborhood of a
%40 to %50 increase since it looks for rows based on their port number
and the call type often in the process of creating it's reports.
A parting word about opening recordsets in your applications. Always
make an effort to keep the number of columns you ask for to a mnimum.
Always using "SELECT * ..." may be easy but in the case of tables with
large number of columns it'll put a real hitch in your performance
get-along if you're pulling lots of rows over as well. All that
information needs to be carted over from the Unity server onto your
local cursor so be sure to keep your queries tightly bound and pull
over only what you need.
Making The Connection
OK, once you've decided on your cursor style and you have the name of
a server in the domain that's running SQL, determining if Unity is on
that box is pretty straight forward. Simply try to open the UnityDB
database on that server and if it succeeds you know it's running Unity
3.0(1) or later. If it fails either it's not running Unity or the
account you're running your application against does not have rights
to connect to that box.
To actually connect (or attempt to connect) to the remote Unity server
it's just a matter of constructing the proper connection string and
opening a recordset directly or a connection object depending on what
you want to do. There are a lot of options in a connection string and
some handy tools out there for helping you construct them to meet your
needs. For my purposes I basically use two types of connection
strings in my applications:
When an application is running on the local Unity server I use this
simplified string:
strConnectionString = "Driver={SQL Server};server=;database=UnityDb"
And then I can open databases directly through recordsets such as this:
Dim rsSubscribers As New ADODB.Recordset
On Error GoTo Connection_Error
rsSubscribers.Open "SELECT Alias, DisplayName FROM Subscriber WHERE
Alias='Eadmin',strConnectionString, adOpenKeyset, adLockReadOnly
<...>
For simply checking to see if we can establish a connection to a
remote box it's necessary to use a somewhat more robust connection
string than the on box one we're using above. This code chunk is from
the remote connection example:
Dim cnAttempt As New ADODB.Connection
strConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=UnityDb;Data
Source=" + strServerName
On Error GoTo Connection_Error
cnAttempt.Open strConnectionString
'if we're here, the connection must have completed ok
<open databases and go wild...>
Exit Sub
Connection_Error:
Select Case Err.Number
' Cannot open database requested in login 'UnityDb'
Case -2147467259
MsgBox strServerName & " does not appear to be a Unity server
or you do not have rights to log into UnityDB on that server"
Set cnAttempt = Nothing
Exit Sub
Case Else
MsgBox "Error in cmdConnect: " + Err.Description + "
code="+str(Err.Number)
Debug.Assert False
Resume Next
End Select
The error handling is the trick there. When attempting to connect to
a database remotely there could be any number of reasons the
connection fails. We're specifically interested in the most common
reason which is the "logon fails" error which is error reason
0x80004005 (-2147467259 decimal). This means simply the DB is not
there or you don't have rights to connect to it. Any other failure is
just treated as a generic failure and reported as such.
A quick work about security here. You'll notice that the connection
string does not contain a login name or password as you may have seen
in other samples. Without them the connection uses the credentials of
the currently logged in user account which, as we discussed earlier in
this chapter, needs to be a member of a security group that has rights
to UnityDB (such as the Local Administrators group on the box) or be
explicitly granted rights to the database. Using SQL authentication
instead of Windows authentication and passing in a hard coded login/PW
string in your connection here is not generally a good idea but it's
your server and your network so go wild.
The connection attempt should go pretty quickly here so if you wanted
to hit a bunch of servers in a row and create a list you can do that.
It would be a little nicer to find one Unity server and then do a
query on that box to find all other servers it's found in the
directory for you, particularly if there are a lot of Unity servers on
the network. Unity is already gathering such information in the
background via the directory monitors anyway and you might as well
leverage that work rather than repeating it yourself. We'll cover an
example of how to do that a little later.
--
*
ข้อสอบครูชำนาญการพิเศษ *
ข้อสอบครูผู้ช่วย,สอบบรรจุครูผู้ช่วย,แนวข้อสอบครูผู้ช่วย,สอบครูผู้ช่วย,แนวข้อสอบครูผู้ช่วย,ครูผู้ช่วย,ข้อสอบบรรจุครู,ข้อสอบครูชำนาญการพิเศษ
*http://www.oopps.bloggang.com*
*
* ฟิสิกส์ ข้อสอบฟิสิกส์ บทเรียนฟิสิกส์ ฟิสิกส์ออนไลน์
โจทย์ฟิสิกส์ แบบฝึกหัดวิชาฟิสิกส์ โจทย์วิชาฟิสิกส์ โจทย์วิชาฟิสิกส์
http://thaiphysics.blogspot.com
* บทเรียนบทรัก ความรัก บทความความรัก ดูดวงความรัก นิยามความรัก
กลอนรัก เพลงรักhttp://love8love9.blogspot.com
* ความรัก บทความความรัก ดูดวงความรัก นิยามความรัก กลอนรัก
เพลงรักhttp://kongkoymusic.blogspot.com
ไม่มีความคิดเห็น:
แสดงความคิดเห็น