Skip to Content

Register Login

Industry Online Support

Technical Forum

Navigation

Retrieving historical data from CAS SQL Database

Created by: Mucosolvan at: 5/9/2008 12:12 PM (7 Replies)

Rating (0)

Thanks 0

8 Entries

5/9/2008 12:12 PM	Rate (0)
Mucosolvan Posts: 9 Rating: (0)	Hello, I am new in this forum. Currently I have project almost completed using PCS7, with Central Archive Server as historian. I need to create an excel format report, and I managed to link the data via Excel Data connection. However there is one table TAGCOMPRESSED that is in varbinary type unable to be displayed properly. Everything in hex, and my knowledge in sql is limited for compression. I am not sure if type cast will give the accurate result. Purchasing add-on software is not an option since there is no budget for this. Had anyone ever tried to retrieve information like this? please kindly share your knowledge.
Mucosolvan Posts: 9 Rating: (0)	Last edited by: Deteriorator at: 9/2/2008 6:56 PM
Suggestion To thank Quote Answer

5/9/2008 7:44 PM	Rate (0)
Dec Gold Expert Joined: 5/19/2006 Last visit: 9/16/2024 Posts: 549 Rating: (107)	What version of PCS7 CAS is running (6.1 or 7.x)? Dec

Suggestion To thank Quote Answer

5/10/2008 12:48 AM	Rate (0)
Mucosolvan Posts: 9 Rating: (0)	Hi Dec, It's version 6.1. Is there any difference with version 7.0? I tried out in excel vb, just to see the values. There seems to be logical relation between BinValues data length and the timespan (timeEnd - timeBegin). it gives me almost 2 sec gap between data if I calculate gap=timespan/dataLength. However, looking at the values in the array, I am not sure if there is conversion required, or scaling to max value (255) is enough. I don't think my data fluctuates every 2 sec though. please see attachment. Attachment test.JPG (614 Downloads)
Mucosolvan Posts: 9 Rating: (0)	Last edited by: Mucosolvan at: 9/2/2008 7:17 PM Last edited by: Deteriorator at: 5/10/2008 1:47 AM
Suggestion To thank Quote Answer

5/10/2008 9:41 AM	Rate (0)
agiber Posts: 9 Rating: (0)	maybe i dont understand something, but shouldn't you use WinCCOLEDBProvider with it's strange query syntax for accessing archieved (compressed)values?
agiber Posts: 9 Rating: (0)
Suggestion To thank Quote Answer

5/13/2008 9:44 AM	Rate (0)
Mucosolvan Posts: 9 Rating: (0)	I tried using WinCCOLEDB as Provider (or correct me if there is other thing to include) with the same result. I found a sample from web with the following piece code: exec @retval = sp_cctlg_decompress @CTimeBegin, @CTimeEnd, @BinValues, @ValueAct OUTPUT sp_cctlg_decompress is a procedure stored in master db, and it is from wincc I tried to fill in the parameter @CTimeBegin, @CTimeEnd, @BinValues, but the result gave me @retval=1 (not successfull?) and @Valueact=NULL. Also trying installation of connectivity pack, but seems it leads to the same ole-db thing.... help, anyone? feri
Mucosolvan Posts: 9 Rating: (0)
Suggestion To thank Quote Answer

5/13/2008 12:31 PM	Rate (0)
agiber Posts: 9 Rating: (0)	if you have Connectivity Pack then you have following options 1. using WinCCOLEDBProvider use query like "TAG:R, 1, 'time from', 'time to'" (check it in Connectivity Pack help - there is NO usual "exec blah blah blah" or "select a from b" queries when you use WinCCOLEDBProvider) 2. using SQL Server OLEDB Provider (or Native SQL Server Provider, or ODBC) call CC_SP_ReadTags from your Runtime Database using standard "exec CC_SP_ReadTags ..." approach.
agiber Posts: 9 Rating: (0)
Suggestion To thank Quote Answer

5/13/2008 5:30 PM	Rate (0)
Mucosolvan Posts: 9 Rating: (0)	Thanks everybody for your helps. I finally found the solution and it works ok for now. I am still connecting through sqloledb, and did the following: - register the BLOB sized to varbinary table 2048, and use the procedure sp_cctlg_registerval - read the information from tagcompressed using standard select ... from - then do the decompression using syntax: sp_cctlg_decompress @cTimeBegin, @CTimeEnd, @BinValues, @ValueAct OUTPUT - the returned value is ValueAct, which has to be translated using sp_cctlg_getvalue. see sample in fn_cctlg_GetValuesByID under programmability->functions I attached a snapshot in SQL query window for reference. Next I would just combine these codes to excel through ADO Cheers.. Feri Attachment SQL_retrieve.JPG (1066 Downloads)
Mucosolvan Posts: 9 Rating: (0)	Last edited by: Deteriorator at: 8/31/2008 3:17 AM
Suggestion To thank Quote Answer

3/9/2011 3:34 PM	Rate (0)
hellyesOo Posts: 1 Rating: (0)	Hi Mucosolvan I have the same problem I'm not a SQL programmer and put your code and did not work you have to declare variables? would be very grateful if you send me your code or a picture of all the code ... thanks in advance.
hellyesOo Posts: 1 Rating: (0)
Suggestion To thank Quote Answer

|

Share this page:

Share this page on...

Related topics by keywords:

WinCC Communication (OPC, S7, OtherSystem)

Follow us on