Home page Company Name

Work with SQL

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 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

' 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.

                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
    ' 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
               While Not RS.EOF 
                    For i=1 To 24 
                         If i<=9 Then

                         End If
    ' Add a new record to the "Load schedule" table and fill it
                        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

                LoadedList = LoadedList + CStr(ConsNum) + " [" + ConsName + "]" + vbCrLf

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

    End If
' after the end of the work, the database will be closed and the user will be informed 
' about the work is done

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

Powered by Bitrix Site Manager Copyright © 2001-2006 Bitrix