Home

Work with SQL

Print version

This script was designed for the "ActOpus" software to enter data from a SQL database (using ADO). It could be used as an example of the interaction with a SQL databases.

Script "Download charts with SQL" is designed for data input of the end users consumption graphics in "ActOpus" software.

The database file for actualization of the required day must be downloaded in the software for the work of the script. The script parses the contents of the "Layout Code" in the "Consumers2" table and this code provides loading schedules of consumers to the table "Load schedule", carrying out the necessary indexing by number of points and numbers of consumers. For the right script work it is required the communication with the SQL database. The script is providing access to the database using ADO interface.

The script must be located in the "Macro" subfolder of the installation directory of the software.

Run of the script is exercised by using the "Run" command in the "Calculation" folder of the main menu. In addition, it is possible to run a script using the shell of the macro - the command "Macro..." in the "Calculation" folder of the main menu. The selection window for selection of the date displays the date for which the load schedule of consumers will be got from the database when script is run.

The script operates the following algorithm for each user during loading the schedules:

• The layout code from the "Consumer2" table is got and the sample from SQL database is made to check the presence of the data.
• The check of the presence of the base customers given loads in the table "Consumer2" (Fields Pmax, Pmid, Pmin) is performed. In case of the all three fields are empty, the schedule will not be loaded and a warning will appear. It is possible to make the script artificially does not download the schedule for selected customers with the cleanup of these fields.
• The check of the availability of the schedule for the current user in the database is performed. The request to update the schedule will issue if at least one point of the user schedule is present in the table "Load schedule". If user confirms the issue, the schedule in the table "Load schedule" for this user will be deleted and replaced from the SQL database. If user rejects the issue, schedule will be not changed, which is available in the table "Load schedule".

The script may take up to three message boxes at the end of its work:

1. The list of the users for which the schedule was loaded successfully. It is derived in case of the presence of the successfully replaced schedules.
2. The list of the users for which the schedule was present in the table of "Load schedule" and the user has refused to update it from the SQL database. It is derived in the presence of consumers on which the schedule was not updated.
3. The list of the consumers for which base loads does not exist. It is derived in case of the presence of such consumers

In these windows are displayed only consumers who had a schedule in the SQL database on the selected by the user date.

The script uses one query for each user by the "PDG_h" table during the access the database:

     strSQLquery="SELECT * FROM PDG_h WHERE (kpo_obj='" & MktCode &_
                 "') And (data='" & strDate & "') And (id_pok=33)"

It is mean that the code of the parameter "The hourly schedule of consumption bid" has "33" value (id_pok = 33).

' This script allows to upload to the "ActOpus" database data from the SQL-server database 
' according to the consumers schedules. It uses ADO.
' (C) Mashalov E. Ural CDA, 2003


' Create the data access objects
Set ADO = CreateObject("ADODB.Connection")
Set RS  = CreateObject("ADODB.Recordset")

' Describe the parameters of a database connection 
DSN = "Driver=SQL Server;UID=user;PWD=letmein;SERVER=sqlserver;DATABASE=Graphic;Mode=Read"
' Open the SQL database
ADO.Open(DSN)

' Executes the query of the date for which you need to enter data on schedules
strDate = Rastr.SendCommandMain(7,"","",0) 

' Construct a date string for the SQL-query
DateDD  = Left(strDate,2)
DateYY  = Right(strDate,2)
DateMM  = Mid(strDate,3,2)
strDate = DateMM & "." & DateDD & "." & DateYY


' Initialize the list of reports
LoadedList = vbCrLf
RejectedList = vbCrLf
PresentList = vbCrLf

' Connect to the "Consumer2" table and "Load schedule" in the "ActOpus" database 
Set ConsumerTable = Rastr.Tables("Consumer2")
Set PLoadTable    = Rastr.Tables("Pload")

' Looking for users schedules in the SQL-database

for ConsNdx = 0 to ConsumerTable.Size-1
    ConsNum  = ConsumerTable.Cols("Num").Z(ConsNdx)
    MktCode  = ConsumerTable.Cols("MktCode").Z(ConsNdx)
    ConsName = ConsumerTable.Cols("Name").Z(ConsNdx)
    ConsPmax = ConsumerTable.Cols("Pmax").Z(ConsNdx)
    ConsPmid = ConsumerTable.Cols("Pmid").Z(ConsNdx)
    ConsPmin = ConsumerTable.Cols("Pmin").Z(ConsNdx)


' Prepare and execute a SQL-query on the model code of a table "Consumer2"
    strSQLquery="SELECT * FROM PDG_h WHERE (kpo_obj='" & MktCode &_
                 "') And (data='" & strDate & "') And (id_pok=33)"
     RS.Open strSQLquery,ADO


' Check the presence of the schedule for this user
     If Not RS.EOF Then         
        PLoadTable.SetSel("Num=" & ConsNum)
            Answ = vbYes

    ' If the schedule for this user is exist in the "ActOpus" database, than ask the user:
    ' "Should it to upgrade the schedule?"

            if PLoadTable.Count > 0 Then
                Answ = MsgBox ("By the user " & ConsNum & " [" & ConsName & "] "the schedule is already loaded. Update?", vbYesNo)
                if Answ = vbYes Then 
    ' If the user wants to update the schedule, the current schedule in the "ActOpus" database will be erased.

                    PLoadTable.DelRows
                End if
            End if
    ' If the user declined to update the schedule, go to the next
          If Answ = vbYes Then
    ' If the user wants to update the schedule, the existence of the user base load will be checked

          if ConsPmax <> 0 Or ConsPmin <> 0 Or ConsPmin <> 0 Then
               PLoadTable.SetSel("")
    ' Load the schedule into the "Loads schedule" table

    ' The entire schedule is stored in the one record, in the fields h0 - h23
    'Therefore, we choose fields for the RS (recordset) object dynamically
               RS.MoveFirst
               While Not RS.EOF 
                    For i=1 To 24 
                         If i<=9 Then
                              RetVal=RS.Fields("h0"&i).Value
                         Else

                              RetVal=RS.Fields("h"&i).Value
                         End If
    ' Add a new record to the "Load schedule" table and fill it
                        PLoadTable.AddRow
                        PLNdx = PLoadTable.Size-1
                        PLoadTable.Cols("Num").Z(PLNdx)     = ConsNum
                        PLoadTable.Cols("NPoint").Z(PLNdx)     = i
                        PLoadTable.Cols("P").Z(PLNdx)        = RetVal
                        PLoadTable.Cols("Q").Z(PLNdx)        = 0

                    Next
                    RS.MoveNext
                Wend
                LoadedList = LoadedList + CStr(ConsNum) + " [" + ConsName + "]" + vbCrLf
           Else

                RejectedList = RejectedList + CStr(ConsNum) + " [" + ConsName + "]" + vbCrLf
       End if
       Else
             PresentList = PresentList + CStr(ConsNum) + " [" + ConsName + "]" + vbCrLf
       End If

    End If
    RS.Close
Next
' after the end of the work, the database will be closed and the user will be informed 
' about the work is done
ADO.Close

if Len(LoadedList) > 2 Then
    MsgBox "The schedules downloaded for:" & LoadedList
End if

if Len(RejectedList) > 2 Then
    MsgBox "Do not have a base loads :" & RejectedList

End if

if Len(PresentList) > 2 Then
    MsgBox "Already have a load schedules:" & PresentList
End if