Guidelines for Working with Clients

Crystal Reports allows you to create reports easily through its experts and wizards. Refer to the Crystal Reports documentation for more details. When working with Crystal Reports, be aware of the following items:

• Crystal does not support the SET command. You must use a WHERE clause in a SELECT statement to specify query parameters.
• A single Crystal Report can only retrieve data from one server, but you can create subreports from different servers within a report.
• The Crystal Reports application does not display milliseconds in timestamps.
• If you want to report on numeric data in the Value or Quality columns of the ihRawData table, you may want to convert all Variant to Float data types from the Report Expert, so that Crystal Reports displays them correctly in the report. This also enables the correct formatting.
  Note: See Displaying Variant Data Type Columns as Floats in Crystal Reports for more details.
• Analysis of the ihTrend and ihAlarm tables in Crystal Reports is not supported.

Using the Historian OLE DB provider with VisiconX, you can do the following:

• Use table or SQL queries.
• Insert multiple controls into a picture to the same or different servers.
• Pass a user name and password or be prompted when opening a picture.

To access the Historian OLE DB provider from VisiconX, follow the steps outlined for OLE DB providers in the Using VisiconX Help manual.

After you follow the steps outlined in the Using VisiconX manual, you need to perform one other task with the FixUserPreferences.ini file in the Dynamics/Local folder. This additional step is necessary because the provider does not support asynchronous (ASYNC) executes. By updating one line in the FixUserPreferences.ini file, you can make all of your VisiconX controls use synchronous (SYNC) executes. See Updating FixUserPreferences.ini for details.

For more information on VisiconX and the SQL Wizard, refer to the VisiconX Using VisiconX Help manual.

You can access the Historian OLE DB provider as you would any other OLE DB provider by using Microsoft?? ActiveX Data Objects (ADO). This approach is more generic than using the Historian SDK.

Asynchronous Connection Support

Visual Basic supports asynchronous (ASYNC) connections. You can open multiple ADO connections to the same data source from within a Visual Basic program. You are limited to one server per connection, and one username and password. A different user can make another connection to the same server, however, by using a different username and password.

Client-Side Cursor Recommendation

It is recommended that you use client-side cursors instead of server-side cursors in Visual Basic.

If you use a server-side cursor, the RowCount property on the recordset object will always be -1 instead of the actual row count.

Retrieve Milliseconds

The following snippet of code shows how you can use Visual Basic to retrieve milliseconds within timestamps.

Public Function Time_To_String_With_Milliseconds(TheTime As Double) As String
Dim Temp As String
Dim TimeFraction As Double
Dim Msc As Long
Dim TempTime As Date

On Error GoTo errc

If TheTime = 0 Then
Time_To_String_With_Milliseconds = ""
Exit Function
End If

TimeFraction = TheTime * 86400#
TimeFraction = TimeFraction - Fix(TimeFraction)

Msc = CLng(TimeFraction * 1000)

TempTime = TheTime - (TimeFraction / 86400#)
If Msc = 1000 Then
Msc = 0
TempTime = DateAdd("s", 1, TempTime)
End If

Time_To_String_With_Milliseconds = LCase(Format$(TempTime, "dd-mmm-yyyy hh:nn:ss") + "." + Format$(Msc, "000"))

errc:

End Function

Throttle Results With the ADO MaxRecords Property

The following example shows how you can use the MaxRecords Property in ADO to throttle the number of rows returned from your query.

SET rstTitles = New ADODB.Recordset
rstTitles.MaxRecords = 10
strSQLTitles = "SELECT Tagname FROM ihTags"
rstTitles.Open strSQLTitles, strCnxn, adOpenStatic, adLockReadOnly, adCmdText

Parameterized Queries in Visual Basic

The following example shows how you can use parameterized queries in your VB script.

Private Sub SampleParameterizedQuery()
    Dim ihConnectString As String
    Dim ihRecordSet     As ADODB.Recordset 
    Dim ihConnection    As ADODB.Connection
    Dim ihParameter     As ADODB.Parameter
    Dim ihCommand       As ADODB.Command

    'Set Up the Historian Connect String...
    Set ihConnectString = "Provider=ihOLEDB.iHistorian.1;User Id=;Password="

    'Create Our Other Objects...
    Set ihConnection = CreateObject("ADODB.Connection")
    Set ihRecordSet = CreateObject("ADODB.Recordset")
    Set ihCommand = CreateObject("ADODB.Command")

    'Open the Connection to the Historian Archiver...
    ihConnection.ConnectionString = ihConnectString
    ihConnection.Open
    'Set up the Command Object
    With ihCommand

        'Set the Active Connection to the Historian Connection Opened Above..
        .ActiveConnection = ihConnection

        'Set the Command Text to a Parameterized Sql Statement....
        .CommandText = "select * from ihTags where datatype = ?"

        'Set the Type of the Command...
        .CommandType = adCmdText

        'Refresh Our Parameter List...
        .Parameters.Refresh
    End With

    'Create a Single Parameter Object...
    Set ihParameter = ihCommand.CreateParameter("Temp", adChar, adParamInput, 100)
    'Set the Parameters Value...
    ihParameter.Value = "SingleFloat"
    'Add the Parameter to the Command Object...
    ihCommand.Parameters.Append ihParameter
    'Run the Command!
    Set ihRecordSet = ihCommand.Execute
End Sub

You can import Historian data into Oracle by using an ADO program. A sample program is provided in the Historian/Samples/Oracle folder.