Vije Citect ODBC to Excel problem

Outrage

Member
O
Join Date
Jul 2006
Location
Nottinghamshire
Posts
173
Hi All,

I'm trying to set up a connection to move data from Citect to Excel using ODBC and SQL statements but i'm not having much luck. I'm using Citect version 7.0 and Excel 2007.

for a test I have the following cicode script and i'm running it on a simple button press:

INT
FUNCTION
Logtosql()
INT Test;
INT hSQL;
hSQL = SQLConnect("DSN=leestest");
Test = SQLExec(hSQL, "INSERT IGNORE INTO Sheet1 (test, value2) VALUES ('" + IntToStr(TAG1) + "','" + IntToStr(TAG2) + "')")

RETURN 0;
END

I've gone through several amalgamations of this with our rep with no forward progress.

The SQLConnect statement appears to link to the spreadsheet as the spreadsheet becomes read only when the script is running but no data is actually transferred to the sheet.

I've had a look at the related Knowlege Base items but there's a bit of uncertainty about the syntax for different versions of excel and the 2007 edition doesn't get a look in.

I get the following error when I execute the script:

SQL Interface - SQL Databae Error - SQLExec() (which i've read directly off the alarms summary)

I've tried contacting Citect technical but my licence is out of support and I'm not an SI (we don't do enough SCADA to make it worthwhile plus we have to use whatever our customer specifies which isn't Citect a lot of the time).

Can anyone spot where i'm going wrong? or post up some working SQL script that i can paste into my application

Best Regards,

Lee
 
Last edited:
As you said yourself the problem does not lie within citect but its the syntax of the SQL statement thats the problem.

I have never interfaced with excel in this matter. using excell as server and citect connecting to it as SQL client to push data. I don't think there is much in the help about that. But if you find the right SQL stament I am shure it would work. But for the syntax I think you will need too search in the Excel help and use another SQL client to test the sql query.

Another way is to turn things around and let excel make a connection to citect trough ODBC. that procedure is described clearly in the citect help.

Regards,

Marsupilami
 
What happens when you replace the references to TAG1 and TAG 2 with actual values ? so the code reads something like this..
SQLExec(hSQL, "INSERT IGNORE INTO Sheet1 (test, value2) VALUES ('12','24')")
Also have a read about Citect's error fn's,it may be that the above function may return an error number that will shed some more light on what's happening.
 
I Believe SQL is for Databases
Try DDE commands for Excel

In Saying that you might need to add the SQLbegintran(hSql)after the connect and before the action SQLExec() call
B
 
kellian said:
I Believe SQL is for Databases
Try DDE commands for Excel

In Saying that you might need to add the SQLbegintran(hSql)after the connect and before the action SQLExec() call
B
Click to expand...


Hi Kellian, you can use sql to spreadsheets, DDE is no good as you need to specify the destination cells, whereas the insert into command will append data onto a new row.
 
Marsupilami said:
As you said yourself the problem does not lie within citect but its the syntax of the SQL statement thats the problem.

I have never interfaced with excel in this matter. using excell as server and citect connecting to it as SQL client to push data. I don't think there is much in the help about that. But if you find the right SQL stament I am shure it would work. But for the syntax I think you will need too search in the Excel help and use another SQL client to test the sql query.

Another way is to turn things around and let excel make a connection to citect trough ODBC. that procedure is described clearly in the citect help.

Regards,

Marsupilami
Click to expand...

Hi Marsupilami, yes it looks like the syntax, opened up another spreadsheet and ODBC's data from one to the other perfectly, tried the same code in citect with good results but it only works with actual values not tags.....
 
Dua Anjing said:
What happens when you replace the references to TAG1 and TAG 2 with actual values ? so the code reads something like this..
SQLExec(hSQL, "INSERT IGNORE INTO Sheet1 (test, value2) VALUES ('12','24')")
Also have a read about Citect's error fn's,it may be that the above function may return an error number that will shed some more light on what's happening.
Click to expand...

Hello Dua Anjing,

ive tried that and it works ok in the following format:

INT
FUNCTION
Logtosql()
INT Test;
INT hSQL;
hSQL = SQLConnect("DSN=leestest");
Test = SQLExec(hSQL, "INSERT IGNORE INTO test (value1, value2)VALUES (500, 300)");

RETURN 0;
END

which just transfers fixed values of 500 and 300. i now need to know how to insert tags where the 500 and 300 are....?

Cheers,

Lee
 
You need to build the insert string first.
for example

FUNCTION
Logtosql()
INT Test;
INT hSQL;
STRING sValue1;
STRING sValue2;
STRING sInsert;

sValue1 = tag1;
sValue2 = tag2;
sInsert = "INSERT IGNORE INTO test (value1, value2)VALUES (" + sValue1 + ", " + sValue2 +")";

hSQL = SQLConnect("DSN=leestest");
Test = SQLExec(hSQL, sInsert);

RETURN0;
END

This should get you started



 
GeoffC said:
You need to build the insert string first.
for example

FUNCTION
Logtosql()
INT Test;
INT hSQL;
STRING sValue1;
STRING sValue2;
STRING sInsert;

sValue1 = tag1;
sValue2 = tag2;
sInsert = "INSERT IGNORE INTO test (value1, value2)VALUES (" + sValue1 + ", " + sValue2 +")";

hSQL = SQLConnect("DSN=leestest");
Test = SQLExec(hSQL, sInsert);

RETURN0;
END

This should get you started
Click to expand...


Hey GeoffC / Marsupilami,

Thanks for your replies, managed to crack it last thing on Friday - good start to the weekend!

For the benefit of future reads A good tool for sorting out the SQL statements is to use MSQuery, an application that runs off the back of excel, you can build a query and test it (spreadsheet to spreadsheet) then cut and paste into citect but it obviously doesnt do the citect strings etc.

This is what worked (very similmar to GeoffC's posted code!)

INT
FUNCTION
Logtosql()
INT Test;
INT hSQL;
STRING sArg1;
STRING sArg2;
sArg1 = tag1
sArg2 = tag2
hSQL = SQLConnect("DSN=leestest");
Test = SQLExec(hSQL, "INSERT IGNORE INTO test (value1, value2, value3)VALUES ('" + sArg1 + "', '" + sArg2 + "', 'leeoutram')");
SQLDisconnect(hSQL);
RETURN 0;
END

Despite what my local Citect technical guy said it wasn't capable of, it does work realtime when the spreadsheet is open in Excel in a similar manner to DDE, but gives you a lot more flexibility with your queries.

Thanks for your help!

Lee
 
one side note I should have mentioned before.

there are known problems with the sql connection functions in citect memory problems connections not closing correctly and so on.

they created the PLUSADO sql functions as a workaround. It uses a dll to make SQL calls and works more stable.

So if you are seeing problems with your project replace the sql functions with the plus ADO functions. they can be found in the citect toolbox.

Regards,

Marsupilami
 
Marsupilami said:
one side note I should have mentioned before.

there are known problems with the sql connection functions in citect memory problems connections not closing correctly and so on.

they created the PLUSADO sql functions as a workaround. It uses a dll to make SQL calls and works more stable.

So if you are seeing problems with your project replace the sql functions with the plus ADO functions. they can be found in the citect toolbox.

Regards,

Marsupilami
Click to expand...

Thanks for the heads up! i'll keep an eye on it.

Cheers,

Lee
 
I have many excel files on my PC. And one day I lost everything on unknown cause. For luck I promptly solved out this issue with the aid of a program. It has quite good capacities for resolving other composite troubles on my view - repair recovered Excel file.
 

Similar Topics

N
Citect V5.42 and S7 400
Hello, I have a running project on Citect v5.42 and simatic net v6.4 I have created a new spare PC and loaded all software like Citect, station...
Replies
0
Views
68
neeru805241
N
M
Help - Creating a "Backup Project" with Citect 7.20
Hello everyone, In a factory where we installed Citect 7.20 the computer began to show the first signs of end of life. They never considered...
Replies
0
Views
73
mrcrnc
M
N
S7 400 and Citect v5.42 Ethernet connection is not working
I have a running backup of Citect and plc and I want to make a spare PC station so I have installed the win XP and somatic net v6.0 and import the...
Replies
3
Views
121
JesperMP
JesperMP
P
Citect Aveva Plant SCADA - Display a variable from cicode on graphics page
I am trying to display a variable within a cicode function onto a graphics page. This function queries a SQL database. I can get the value onto a...
Replies
3
Views
288
LegacyLee
L
emomorales
Citect 2018 to Top Server V6 connection issues.
Looking for some help with the connection issue between Citect 2018 and Top Server v6 for Opto22. I’m setting all this up to communicate with some...
Replies
0
Views
120
emomorales
emomorales
Back
Top Bottom