How to access the Citect Scada database?

[Mehak]

Hello Everyone,

I want to ask how can I access the Citect SCADA database or How can I connect with SQL Server? I tried to connect but it didn't work for me. Kindly someone guides me?

Thank you

[MartinChartrand]

Hi @Mehak ,

Citect SCADA is not one of the products typically used by strict members of the Remote Operations (formerly SCADA & Telemetry) community so you might not get much help here. I would recommend contacting their support group directly.

There's also a Knowledge Base that contains Citect articles on Exchange that might have an answer for you:

https://community.se.com/t5/Industrial-Automation-Knowledge/tkb-p/industrial-automation-external-kb

However, you might also get lucky with one of experienced members here!

Regards,

Martin Chartrand

[BevanWeiss]

You're going to have to provide FAR more information if you want any meaningful help.

What version of Citect SCADA do you have?

What methods have you actually tried to connect to it?

What made you think that this was an option at all?

I mean, I'd never think to try to connect SQL Server with my cat.. since my cat has never advertised any kind of SQL interface.

[Mehak]

Hi @BevanWeiss,

I am using Vijeo Citect version 7.40. I am working on reports as customers demand whenever they want to generate the report, just press the button and generate the report with previous data. That's why I need to connect it with an SQL server to store my data in the database. 

Thank you

[BevanWeiss]

Citect reports

https://www.youtube.com/watch?v=1ZAWxu9AuvY

Citect cicode SQL functions (for Citect to connect to an SQL server)

https://johnwiltshire.com/citect-help/Subsystems/CicodeReferenceCitectHTML/Content/SQL_Functions.htm...

You should use Citect reports.

Otherwise you'll need to find a way to either push data into your SQL server from Citect using the SQL functions I've linked you to, or to use Citect as an OPC server connected to Kepware Datalogger to write the data to an SQL database.

[Mehak]

Hi @BevanWeiss I do as you say to connect it with SQL using the following code

FUNCTION DATA_LOCK()


INT hsql;
INT hexec,i;
STRING Time_1;
STRING Date_1;
STRING Time_DATE;
STRING svar1,sVAR2,sVAR3,sVAR4,sVAR5;




hsql=SQLConnect("dsn=DATA_DB_REPORT")
Date_1=Date(2);
Time_1=Date(10);
Time_Date=Date_1+Time_1;



SQLSet(hsql,"INSERT INTO REPORT_TABLE(DateTime,Data1,Data2,Data3,Data4) VALUES('");

SQLAppend(hsql,Time_1+"','");
SQLAppend(hsql,RealToStr (one,12,2)+"','");
SQLAppend(hsql,RealToStr (two,12,2)+"','");
SQLAppend(hsql,RealToStr (three,12,2)+"','");
SQLAppend(hsql,RealToStr (four,12,2)+"')");
//SQLAppend(hsql,RealToStr (FF_LINE_1,12,2)+"','");


SQLExec(hsql,"");

SQLDisconnect(hsql);
END

But it is not logging the data into SQL as the connection is successfully established.

[BevanWeiss]

Your code makes no sense:

SQLAppend(hsql,RealToStr (one,12,2)+"','");

'one' is not a valid REAL.

You haven't mentioned when you call this Cicode function, you haven't mentioned what the DATA_DB_REPORT dsn connects to or provided any evidence that any part of this was correct.

You should provide a screenshot of the query string as reported by Citect immediately before it's calling SQLExec.

You should also show the exact same thing from a 3rd party ODBC client when connecting using exactly the same DSN and when executing exactly the same SQL query that you have from your Citect (i.e. exactly the same string shown in the screenshot).

[Mehak]

Hello @BevanWeiss,I am new to Citect SCADA Cicode. SQLAppend(hsql,RealToStr (one,12,2)+"','"); In this expression One is local variable that I defined and give datatype Real. I called the Cicode function in the button that I made in the graphic builder window. Below are the screenshots which show my ODBC connection is successfully established.

 

[BevanWeiss]

You haven't provided the query string as printed from the Cicode, nor a 3rd party ODBC client successfully executing that same query string from the Citect machine.

[Mehak]

This is the query I passed in SQL.

3rd party ODBC client successfully executing that same query string from the Citect machine. I cannot understand this. Can you explain more?

[BevanWeiss]

1. Using SQL Management Studio is NOT a 3rd party ODBC client.  It's not even using ODBC.
2. That is not the same query as you have shown you're trying to use in Citect.
3. You have not shown the query string immediately before calling SqlExec in your Cicode (i.e. via message box or similar).

Perhaps you should engage an experienced contractor to perform this work for you.