Hello all,
I need to find out how to send DateTime info as part of a MS SQL query to INSERT a new record into a table that contains a [datetime] type column. We are currently sending a string hoping the conversion will happen on the MS SQL side of things. The system we're testing this on has been upgraded from citect scada 7.3 to 2018 R2. I have attached the Cicode function we are using to test this transaction as well as a table creation file from MS SQL.
FUNCTION TestInsert()
STRING sYear, sMonth, sDay, sTime, sHour, sMinute, sSecond;
sManOvrd = "Manual Override";
st26 = "'TEST INSERT'";
st1=MachNumbDSK;
st2=LotNoDSK;
//st3=Date(10);
sYear = StrPad(IntToStr(DateYear(TimeCurrent(),1)),"0",-4);
sMonth = StrPad(IntToStr(DateMonth(TimeCurrent())),"0",-2);
sDay = StrPad(IntToStr(DateDay(TimeCurrent())),"0",-2);
sHour = StrPad(IntToStr(TimeHour(TimeCurrent())),"0",-2);
sMinute = StrPad(IntToStr(TimeMin(TimeCurrent())),"0",-2);
sSecond = StrPad(IntToStr(TimeSec(TimeCurrent())),"0",-2);
st3= "'"+sYear+"-"+sMonth+"-"+sDay+"T"+sHour+":"+sMinute+":"+sSecond+"'";
//st3="GETDATE()";
Message("Date",st3,64);
sUserName = vsUserName;
st4=RealToStr(4.4,7,3);
st5=RealToStr(5.5,7,3);
st6=RealToStr(6.6,7,3);
st7=RealToStr(7.7,7,3);
st8=RealToStr(8.8,7,3);
st9=RealToStr(9.9,7,3);
st10=RealToStr(10.0,7,3);
st11=RealToStr(11.1,7,3);
st12=RealToStr(12.2,7,3);
st13=RealToStr(13.3,7,3);
st14=RealToStr(14.4,7,3);
st15=RealToStr(15.5,7,3);
st16=RealToStr(16.6,7,3);
st17=RealToStr(17.7,7,3);
st18=RealToStr(18.8,7,3);
st19=RealToStr(19.9,7,3);
st20=RealToStr(20.0,7,3);
st21=RealToStr(21.1,7,3);
st22=RealToStr(22.2,7,3);
st23=RealToStr(23.3,7,3);
st24=RealToStr(24.4,7,3);
st25=RealToStr(25.5,7,3);
st27=RealToStr(27.7,7,3);
st28="111-222";
st29=Sublots1DSK;
st30=Sublots2DSK;
sqlAppStr1 = " ('"+st1+"','"+st2+"',"+st3+",'"+sUserName+"','"+sManOvrd+"',"+st4+","+st5+","+st6+","+st7+","+st8+","+st9+","+st10+","+st11+","+st12+","+st13+","+st14+","
sqlAppStr2 = st15+","+st16+","+st17+","+st18+","+st19+","+st20+","+st21+","+st22+","+st23+","+st24+","+st25+","+st26+","+st27+",'"+st28+"','"+st29+"','"+st30+"')"
hSQL=SQLConnect(vsSQLDataSourceLocal);
SQLBeginTran(hSQL);
SQLSet(hSQL, "INSERT INTO [SterilizationVesselData].[dbo].[ProcessDataDetails] VALUES");
SQLAppend(hSQL,sqlAppStr1);
SQLAppend(hSQL,sqlAppStr2);
Status=SQLExec(hSQL, "");
MinuteDataReadPLC = 1; ! C830
Status=SQLExec(hSQL, "");
IF Status <> 0 THEN
Message("Status Error During MM Data", SQLErrMsg(), 48);
ErrLog("ERROR: SQL MINUTE MARKERS STATUS ERROR!");
SQLRollBack(hSQL);
ELSE
SQLCommit(hSQL);
END
SQLEnd(hSQL);
SQLDisconnect(hSQL);
END ! END of InsertMMSQLData
USE [SterilizationVesselData] GO /****** Object: Table [dbo].[ProcessDataDetails] Script Date: 3/13/2023 12:37:18 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProcessDataDetails]( [DetailID] [int] IDENTITY(1,1) NOT NULL, [VesselID] [varchar](50) NULL, [LotNumber] [varchar](50) NOT NULL, [UserName] [varchar](50) NULL, [ManualOverride] [varchar](50) NULL, [DetailDate] [datetime] NOT NULL, [Rtd1A] [float] NULL, [Rtd1B] [float] NULL, [Rtd2A] [float] NULL, [Rtd2B] [float] NULL, [Rtd3A] [float] NULL, [Rtd3B] [float] NULL, [Pressure1] [float] NULL, [Pressure2] [float] NULL, [Fo1A] [float] NULL, [Fo1B] [float] NULL, [Fo2A] [float] NULL, [Fo2B] [float] NULL, [Fo3A] [float] NULL, [Fo3B] [float] NULL, [Heating] [float] NULL, [Cooling] [float] NULL, [WL] [float] NULL, [Steam] [float] NULL, [Cpp] [float] NULL, [Hxp] [float] NULL, [CppHxp] [float] NULL, [Flow] [float] NULL, [Statement] [nvarchar](50) NULL, [ProcessTime] [float] NULL, [SubLots] [varchar](50) NOT NULL, [Sublots1] [varchar](50) NOT NULL, [Sublots2] [varchar](50) NOT NULL, CONSTRAINT [PK_ProcessDataDetails] PRIMARY KEY CLUSTERED ( [DetailID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO
