Posted 20 May 2020 I am having a problem getting citect to write to my sql database. Whilst it will write I am restricted by the length of a single cicode command. I think its 255 characters. For my problem I am trying to write 48 tags to the database in one write command to obtain 1 time stamp. Is it possible to write multiple times to the same tables row ? Currently when I write a new row is created at the given timestamp. the cicode is attached. A solution would be to do 4 writes of 12 tags at a time but all would need to go in the same MSSQL table row. I'm ok using the data instead of time and date as this write only happens once each day Any help would be awesome.. *********************************************************************************************************************************** /* ** FILE: SpurLoad.CI ** * Load Tags from Citect in TO temp array */ STRING tagname[24]="M340_PwrCtrl_SLD0000","M340_PwrCtrl_SLD0030","M340_PwrCtrl_SLD0100","M340_PwrCtrl_SLD0130","M340_PwrCtrl_SLD0200","M340_PwrCtrl_SLD0230","M340_PwrCtrl_SLD0300","M340_PwrCtrl_SLD0330","M340_PwrCtrl_SLD0400","M340_PwrCtrl_SLD0430","M340_PwrCtrl_SLD0500","M340_PwrCtrl_SLD0530","M340_PwrCtrl_SLD0600","M340_PwrCtrl_SLD0630","M340_PwrCtrl_SLD0700","M340_PwrCtrl_SLD0730","M340_PwrCtrl_SLD0800","M340_PwrCtrl_SLD0830","M340_PwrCtrl_SLD0900","M340_PwrCtrl_SLD0930","M340_PwrCtrl_SLD1000","M340_PwrCtrl_SLD1030","M340_PwrCtrl_SLD1100"; /* add array TO hold values */ STRING tagval[24]; INT hSQL; FUNCTION SQL_INSERT_lds() /* Loop FOR number of tags (13 lots of 30 min) TO be read adding to array Variables */ INT i; FOR i = 0 TO 21 DO tagval = TagRead(tagname); END /* Open ODBC connection TO CitectDB configured in ODBC Manager with DSN_SQL */ hSQL = SQLConnect("DSN=DSN_SQL"); /* IF connection IS OK, insert in TO table "lds" the values */ IF hSQL <> -1 THEN SQLExec(hSQL, "INSERT INTO lds (dateandtime,S0000,S0030,S0100,S0130,S0200,S0230,S0300,S0330,S0400,S0430,S0500,S0530,S0600,S0630,S0700,S0730,S0800,S0830,S0900,S0930,S1000,S1030) VALUES (SYSDATETIME(),'"+tagval[0]+"','"+tagval[1]+"','"+tagval[2]+"','"+tagval[3]+"','"+tagval[4]+"','"+tagval[5]+"','"+tagval[6]+"','"+tagval[7]+"','"+tagval[8]+"','"+tagval[9]+"','"+tagval[10]+"','"+tagval [11]+"','"+tagval[12]+"','"+tagval[13]+"','"+tagval[14]+"','"+tagval[15]+"','"+tagval[16]+"','"+tagval[17]+"','"+tagval[18]+"','"+tagval[19]+"','"+tagval[20]+"','"+tagval[21]+"')"); SQLEnd(hSQL); SleepMS(100); SQLDisconnect(hSQL); ELSE Message("Error Message","SQL TRADING DAY DATABASE WRITE FAIL",48); END END SpurLoad.ci Share this post Link to post Share on other sites
Posted 20 May 2020 Our system does something very similar to what you are doing. I didn't the write the code to do it, but they got around the issue by writing some of the tags (and creating a new row in the SQL database), then updating that row with some more tags, on so on until all your tags are written. It required a custom SQL script on the server for each time the tags were written. I'll have a dig through the code and see if I can paste an example. 1 person likes this Share this post Link to post Share on other sites
Posted 20 May 2020 Below is the important part of the Cicode file (I have taken part 3 and 4 out for the sake of brevity): // Connect to the database and log the action // The data is sent in 4 parts due to string size being greater then 255 // The 4 parts are User info, Tag Info, Comments and Computer info //Part 1 User info, (Date and user name) sCmd = "qryInsertOperatorLog"; sCmd = sCmd + "('" + sDateTime + "', '" + sUser +"', '"+ sPCNode +"')"; hRecordSet = CSV_DB_Execute(sCmd, gsPrimaryConnection); TraceMsg("Executing " + sCmd); IF hRecordSet = -1 THEN //Message("ERROR", "There was a problem creating a new operator log entry in function 'WriteSQL()'.",48); DBLogError("WriteSQL/SQLExec", hRecordSet, 0); CSV_DB_Close(hRecordSet); RETURN; END CSV_DB_Close(hRecordSet); //Part 2 Tag Info, (Action taken, tag name, new value) sCmd = "qryUpdateOpLogTagInfo"; sCmd = sCmd + "('" + sDateTime + "', '" + sAction + "', '" + sTag + "', '" + sValue + "', '"+ sPCNode +"')"; hRecordSet = CSV_DB_Execute(sCmd, gsPrimaryConnection); TraceMsg("Executing " + sCmd); IF hRecordSet = -1 THEN //Message("ERROR", "There was a problem creating a new operator log entry in function 'WriteSQL()'.",48); DBLogError("WriteSQL/SQLExec", hRecordSet, 0); CSV_DB_Close(hRecordSet); RETURN; END CSV_DB_Close(hRecordSet); This is the custom SQL scripts that exist on the server (insert, part1 and update, part2): ALTER PROCEDURE [dbo].[qryInsertOperatorLog] (@dEventTime AS datetime, @sUser AS nvarchar(20), @sPCNode AS nvarchar(50) ) AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[OperatorLog] ([EventTime],[Operator],[PCNode]) VALUES (@dEventTime, @sUser, @sPCNode) END ALTER PROCEDURE [dbo].[qryUpdateOpLogTagInfo] (@dEventTime as datetime, @sAction nvarchar(30), @sTag nvarchar(79), @rValue real,@sPCNode AS nvarchar(50)) AS BEGIN SET NOCOUNT ON; UPDATE [dbo].[OperatorLog] SET [Action] = @sAction, [Tag] = @sTag, [Value] = @rValue WHERE ([EventTime] = @dEventTime) AND ([pcNODE] = @sPCNode) END Hope that helps, but let me know if you have any questions. 1 person likes this Share this post Link to post Share on other sites
Posted 21 May 2020 (edited) Thankyou very much. I see a different way of connecting to the MSSQL instance. Whilst trying to understand what this code is I cant reference "CSV_DB_Execute" which appears to be the db connect? is this cicode or cicodeVBA? Also is you application running a seperate query ? "qryInsertOperatorLog" or is this just a tagger event? can you also clarify what "gsPrimaryConnection" is. Sorry to be a pain but I connected using DSN and this is a new method to me which I cant find in cicode reference. Really appreciate the guidance here. Edited 21 May 2020 by hmwright Share this post Link to post Share on other sites
Posted 21 May 2020 The gsPrimaryConnection is an SQL connection string, see example below, you will need to replace SERVERNAME etc with your details: STRING gsPrimaryConnection = "Driver={SQL Server};Server=SERVERNAME;UID=USERNAME;Pwd=PASSWORD;Persist Security Info=True;Database=SQLDATABASENAME"; CSV_DB_Execute is a built in Cicode function (at least in 2015 SP1 that we are using). There is some info for it here: https://gcsresource.aveva.com/Citect/WebHelp/citect750sp1/default.htm#CSV_DB_Execute.html?Highlight=csv_db_execute qryInsertOperatorLog is the custom SQL script that I have shown above. 1 person likes this Share this post Link to post Share on other sites
Posted 21 May 2020 Thanks; It looks like "CSV_DB_execute" was a citect 2015 command and appears in that help. We are suing 2018R2 and this CSV set seems to be missing. If I master the concept then I may be able to get it to work with the "SQLExec" command. In the scripts I also note a few @dEventTime and @sAction. I am assuming the d and s refer to datatype ? IS this referenced anywhere ? Thanks again for the assistance Share this post Link to post Share on other sites
Posted 21 May 2020 Yeah the d and s are just part of our naming convention that has s for string, d for dateTime, i for Int etc. All of those are just variable names for information we want to store. In your case you could just use something like @tagval1 , @tagval2 etc. but it might help to name them something more descriptive if you have to line them up with a particular column in your SQL database. 1 person likes this Share this post Link to post Share on other sites
Posted 21 May 2020 Great news slimskism. I used your concept and switched to the newer citect SQL commands and ended up only needing the one script (below) and using SQLappend to keep making the SQL text string longer. Even amazed myself. Thanks for your help. In the first part I still read tags in to an array as initial post but function now as below:- FUNCTION SQL_INSERT_lds() // Loop FOR number of tags (16 lots of 30 min) TO be read adding to array Variables INT i; FOR i = 0 TO 31 DO tagval = TagRead(tagname); END // Open ODBC connection TO CitectDB configured in ODBC Manager with DSN_SQL INT hSQL; hSQL = SQLConnect("DSN=DSN_SQL"); //* IF connection IS OK, set the begining of a SQL transaction IF hSQL <> -1 THEN SQLSet(hSQL, "INSERT INTO lds (dateandtime,S0000,S0030,S0100,S0130,S0200,S0230,S0300,S0330,S0400,S0430,S0500,S0530,S0600,S0630,S0700,S0730,"); SQLAppend(hSQL, "S0800,S0830,S0900,S0930,S1000,S1030,S1100,S1130,S1200,S1230,S1300,S1330,S1400,S1430,S1500,S1530) VALUES "); SQLAppend(hSQL, "(SYSDATETIME(),'"+tagval[0]+"','"+tagval[1]+"','"+tagval[2]+"','"+tagval[3]+"','"+tagval[4]+"','"+tagval[5]+"','"+tagval[6]+"','"+tagval[7]+"','"+tagval[8]+"','"+tagval[9]+"','"+tagval[10]+"','"+tagval[11]+"','"+tagval[12]+"','"+tagval[13]+"','"+tagval[14]+"','"+tagval[15]+"',"); SQLAppend(hSQL, "'"+tagval[16]+"','"+tagval[17]+"','"+tagval[18]+"','"+tagval[19]+"','"+tagval[20]+"','"+tagval[21]+"','"+tagval[22]+"','"+tagval[23]+"','"+tagval[24]+"','"+tagval[25]+"','"+tagval[26]+"','"+tagval[27]+"','"+tagval[28]+"','"+tagval[29]+"','"+tagval[30]+"','"+tagval[31]+"')"); SQLExec(HSQL, ""); SQLEnd(hSQL); SleepMS(100); SQLDisconnect(hSQL); ELSE Message("Error Message","SQL TRADING DAY DATABASE WRITE FAIL",48); END END Share this post Link to post Share on other sites
Posted 21 May 2020 Good to hear you got it sorted. Will have to keep that in mind for when we upgrade to the latest version. Share this post Link to post Share on other sites