Microsoft SQL querys from ArchestrA script.
I use ODBC because it is portable from one pc to another. I just use custom property tags filled in with the ---. As an example "MachineTime" is a custom property. I also use Visual Studio 2010 Visual Basic to debug the code that runs in ArchestrA scripts. You just need to add the ; at the end of the statement. Also there is no using clause so the entire class name must be used.
Here is a sample that uses stored procedures to send data to a database table. This is the script that could go in a symbol script or a button script.
Dim myReader As System.Data.Odbc.OdbcDataReader;
Dim sConnString As String;
Dim oODBCConnection As System.Data.Odbc.OdbcConnection;
Dim oODBCCommand As System.Data.Odbc.OdbcCommand;
LogMessage("ODBC PutData");
sConnString = "Dsn=MyODBC;Trusted_Connection=Yes;";
oODBCConnection = New System.Data.Odbc.OdbcConnection(sConnString);
oODBCCommand = New System.Data.Odbc.OdbcCommand("{ Call PutData(?,?,?,?,?,?,?,?,?) }");
oODBCCommand.CommandType = System.Data.CommandType.StoredProcedure;
oODBCCommand.Connection = oODBCConnection;
Dim Machine As System.Data.Odbc.OdbcParameter;
Machine = oODBCCommand.Parameters.Add("@Machine", System.Data.Odbc.OdbcType.Int);
Machine.Value = MachineTime;
Machine.Direction = System.Data.ParameterDirection.Input;
Dim HydraulicPump As System.Data.Odbc.OdbcParameter;
HydraulicPump = oODBCCommand.Parameters.Add("@HydraulicPump", System.Data.Odbc.OdbcType.Int);
HydraulicPump.Value = PumpTime;
HydraulicPump.Direction = System.Data.ParameterDirection.Input;
Dim Extruder As System.Data.Odbc.OdbcParameter;
Extruder = oODBCCommand.Parameters.Add("@Extruder", System.Data.Odbc.OdbcType.Int);
Extruder.Value = ExtruderTime;
Extruder.Direction = System.Data.ParameterDirection.Input;
Dim Lube As System.Data.Odbc.OdbcParameter;
Lube = oODBCCommand.Parameters.Add("@Lube", System.Data.Odbc.OdbcType.Int);
Lube.Value = LubeCycle;
Lube.Direction = System.Data.ParameterDirection.Input;
Dim Maint24 As System.Data.Odbc.OdbcParameter;
Maint24 = oODBCCommand.Parameters.Add("@Maint24Time", System.Data.Odbc.OdbcType.Int);
Maint24.Value = Maint24Time;
Maint24.Direction = System.Data.ParameterDirection.Input;
Dim Maint120 As System.Data.Odbc.OdbcParameter;
Maint120 = oODBCCommand.Parameters.Add("@Maint120Time", System.Data.Odbc.OdbcType.Int);
Maint120.Value = Maint120Time;
Maint120.Direction = System.Data.ParameterDirection.Input;
Dim Maint480 As System.Data.Odbc.OdbcParameter;
Maint480 = oODBCCommand.Parameters.Add("@Maint480Time", System.Data.Odbc.OdbcType.Int);
Maint480.Value = Maint480Time;
Maint480.Direction = System.Data.ParameterDirection.Input;
Dim Maint2400 As System.Data.Odbc.OdbcParameter;
Maint2400 = oODBCCommand.Parameters.Add("@Maint2400Time", System.Data.Odbc.OdbcType.Int);
Maint2400.Value = Maint2400Time;
Maint2400.Direction = System.Data.ParameterDirection.Input;
Dim Maint4800 As System.Data.Odbc.OdbcParameter;
Maint4800 = oODBCCommand.Parameters.Add("@Maint4800Time", System.Data.Odbc.OdbcType.Int);
Maint4800.Value = Maint4800Time;
Maint4800.Direction = System.Data.ParameterDirection.Input;
oODBCConnection.Open();
MyReader = oODBCCommand.ExecuteReader();
MyReader.Close();
oODBCConnection.Close();
Here is the stored procedure
USE [MyDatabase]
GO
/****** Object: StoredProcedure [dbo].[PutData] Script Date: 03/08/2016 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PutData]
-- Add the parameters for the stored procedure here
@Machine int,
@HydraulicPump int,
@Extruder int,
@Lube int,
@Maint24Time int,
@Maint120Time int,
@Maint480Time int,
@Maint2400Time int,
@Maint4800Time int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Update statements for procedure here
UPDATE MyDatabase.dbo.Timers SET [Machine] = @Machine, [HydraulicPump] = @HydraulicPump, [Extruder] = @Extruder, [Lube] = @Lube, [Maint24Time] = @Maint24Time, [Maint120Time] = @Maint120Time, [Maint480Time] = @Maint480Time, [Maint2400Time] = @Maint2400Time, [Maint4800Time] = @Maint4800Time WHERE DataId = 1
END
This table only has one row but you can use an INSERT IGNORE INTO for more rows. ArchestrA 10.0 needs to have an invisible graphic on screen. I use a hidden rectangle.
