DIAdem crashes when displaying data from MySQL Database with

[iplonJochen]

Hello,

I want to display yield data from a solar powerplant, When I try to import the data into the data portal, the system crashes with a runtime error when executing StorageImport.

Error type: ACCESS VIOLATION

address: 0000D2C0

Module: myodbc3.dll

This table is huge. It contains 6.296.812 which are divided into 128 Strings

When I try to display a smaller Table, it works fine.

It there a way to specify the StringID before moving the data into the data portal?

Is it possible to display such big datasets at all?

Thanks in advance

[AndreasH_Ha]

Can you please describe in some more detail how the table is structured ?

You wrote " It contains 6.296.812 which are divided into 128 Strings"

How many columns and rows are in the table ?

What is meant by "which are divided into 128 Strings"?

6.296.812 what ? rows ?

You refer to a StringID. Is this the unique key in the table ? What other columns are in the table ?

Looking at the error message, I would assume that the ODBC DLL runs out of memory but that's only a guess by now

Thanks

[iplonJochen]

Thanks for your reply.

In this table, the Current (I_dc) of different strings (String-ID (int)) of solar panels is measured regularly (Timestamp (int))

Yes, the String-ID (int) is part of the Primary Key. There are 128 devices, which are identified by their String-ID. The other part is the Timestamp)

Every 15 minutes, every string enters a new current value into this table, together with it's Unique String ID. It's also possible to enter some more values, but I don't need to display them in DIAdem, yet.

What I currently want to do with DIAdem is, displaying the current of all or a selection of strings over a timeline

And yes, it contains over 6 million rows and... columns as well. Maybe around 15 (I don't have the database here right now) but most of them don't contain any important data yet..

When I export the data of the first and oldest string (String-ID 1 with about 55000 rows) into a seperate table, it is possible to display the data.

I also believe the problem is the huge amount of data imported. Do you think that MySQL or ODBC is the bottleneck?

Ah! I also forgot to mention, that the database runs on my local machine and that I connect via an ADO connection string.

[AndreasH_Ha]

This really sounds like a lot of data.

I assume the problem derives from the large amount of data returned from the query. I recommend to try a query which returns a smaller dataset. Perhaps you can define a set of queries which return teh data in smaller chunks. Then you would concatenate the chunks in DIAdem.

Andreas

[iplonJochen]

I'd love to, but how do I do that? When I drag the fields I want into the data thingy on the right, it tries to import everything and crashes.

I now split the Tables into several smaller tables and it works fine.

I also have another question: The primary key in my database is a unix timestamp like 1309890439. When I select timestamp as the datatype of the timestamp, it returns a date at around 40 AD. Does DIAdem count it's timestamps from year 0? Is there a workaround for that?

[AndreasK]

If you are not afraid of writing DIAdem Scripts it should be easy.

I have attached an example accessing an Excel sheet with some similiar setup via ADO.

It contains two load mechanism. One loading all channels that belong to one sensor in one call.

And one that loads channel by channel.

I hope thats could solve your issue.

[AndreasK · Accepted solution]

The time question. Your are right DIAdem starts 0 AD.

Option Explicit  'Forces the explicit declaration of all the variables in a script.

const SECONDS_01010000_TO_01011970 = 62167132800.0

dataDelAll 1

dim chn : set chn = data.Root.ChannelGroups.Add("new").Channels.Add("Time", DataTypeFloat64)
chn.Values(1) = 1309890439
Call ChnLinScale(chn,chn,1,SECONDS_01010000_TO_01011970) '... Y,E,ChnScaleFactor,ChnScaleOffset
chn.Properties("displaytype").Value = "Time"

 Shows how to adjust a channel from Unix time.