hmwright

Citect Cicode database write line length restriction

9 posts in this topic

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

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

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 (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 by hmwright

Share this post


Link to post
Share on other sites

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

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

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

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now