ComponentData and ComponentAttribute structure
Hi everybody!
Maybe these question have already been asked on another post but I must admit that I did not find nothing specific..
I'm developing an app (Visual Studio and C#) that connects to a selected database and then updates component data stored in an Excel file the user opens.
I have opened the Components.mdb and I have found these two tables:
- ComponentData (the list of components)
- ComponentAttribute (the list of attributes of each component)
The basic idea is to search the component (Article Number field is the key) and, if available, updates a set of custom attributes. If a certain attribute is not in the table for that component, create the record and then update the values.
In very simple words I need to execute SQL SELECT, UPDATE (or INSERT) into these two tables.
My questions are:
1) I want to update the most recent version of the component. I saw the field VERSION but how I have to use it?
2) To connect attributes to component I have to use the ENTRY field of the second table but if I have to create a new record into ComponentAttribute table, which values I have to store in fields like FLAGS or ID?
Is there anty docs that describes the meaning of all fields in this two tables?
Thanks a lot to everyone!
Best
Michele
Was this Helpful?
-
The best way to do this would be to use the dbupdate tool that comes with the install files. The code and settings for the library would be taken into account and updated of created based on the existence of the component by the dbupdate tool rather than editing the database directly.
Depending on the version of E3.series in use some of the entries in columns will be different and could potentially create problems.
Was this Helpful?
0 -
Thanks Sanu for your quick reply.
I understand what you are saying and I agree with you but let's say that I would like to use my own procedure to update components.
I don't want to perform complex operations like create components. I only need to update a very limited number of custom attributes if that particular component is available on the table.
That's why I need support about the structure of those two tables.
Another option.
Do you think that would be possible to design an interface (using .NET) that createS the necessary files and then launch the standard DBUpdate tool?
Thanks a lot for your time
Was this Helpful?
0 -
Michele,
Yes, you can create .net or even an excel interface that creates .ini and bat files to launch and run the DBupdate automatically from the interface. Since your data is in excel for the parts you want to check against it would be easy. The attributes you want to look up are supposed to be part of the ini file with the option of create or only update if available. The help file has detailed descriptions.
Was this Helpful?
0 -
Hi Michele
Everyone wants to know the schema (I would too) but Zuken just won't provide it.
Some parts are easy like ComponentData and ComponentAttribute tables but there are relations that that extend beyond these tables that are difficult to follow. It's not often but they have been making changes to the schema.
Years ago I attended an E3 Library training session when one of the A/E's received a call from a customer in panic that tried to insert entries into their MS Access database. Don't remember how they did it but the database was completely foobarred and the customer was requesting help to fix it. The point that was stressed during training was don't ever mess with the MS Access database. Do all actions via E3.
The *ONLY* thing I am comfortable with doing is querying the database for data. I am not comfortable at all with action queries (create, update, delete). I wouldn't want the responsibility of breaking a database. Better have backups if you do.
What might be possible (I haven't tried this) is to use the DbeApplicationInterface to open components and modify attributes. I have no experience with this set of interfaces. I imagine it will be slow but if it works it should be safer to perform updates. When the schema changes, so will E3.
Was this Helpful?
0 -
Ok, thanks! Maybe this combo is the best option.
Two quick questions:
1) It is possibile to specify to only update the component if the Article Number of the Excel file in present on the component Database (i.e. no do not create new component!)
2) If the cell of the column Article number is empty, that line is simply skipped? When the DBUpdate tools stops reading the excel file and terminate its operation?
Sorry but I did not know this tool but obviously I will read the PDF with the technical specifications..
Thanks again and have a nice weekend.
Was this Helpful?
0 -
Thanks Bob for your post. Ok, I got it.. DON'T MESS WITH THE DB!! :)
I almost forgot.. This is the interface of the tool. Everything is working and I only miss the final step: update the component! Below a screenshot just to share some more info with you.
Was this Helpful?
0 -
Hi everybody!
I have a problem running E3.DBUpdate using C#.
This is the full command line and it works correctly when launched from the Command Prompt:
DBupdate -i "C:\Users\michele.mura\Desktop\test_dbupdate\E3.DBUpdate_Config.ini" -d "C:\Users\michele.mura\Desktop\test_dbupdate\test_import_02.xlsx" -u -b -lang 44 -l "C:\Users\michele.mura\Desktop\test_dbupdate\dbrams_log_20190624.log" -t 2
If I call the same thing using C# and ProcessStartInfo:
ProcessStartInfo startInfo = new ProcessStartInfo
{
FileName = f,
Arguments = a
};
Process myProcess = Process.Start(startInfo);
myProcess.WaitForExit();
ec = myProcess.ExitCode;The parameter "a" (string) contains this value =
-i \"C:\\Users\\michele.mura\\Desktop\\test_dbupdate\\E3.DBUpdate_Config.ini\" -d \"C:\\Users\\michele.mura\\Desktop\\test_dbupdate\\test_import_02.xlsx\" -u -b -lang 44 -l \"C:\\Users\\michele.mura\\Desktop\test_dbupdate\\dbrams_log_20190624.log\" -t 2
I get a generic error code: &H7FFFFFFF which means a generic Exception in the table contained in the official technical specifications of the tool (the .PDF file)
Thanks for your time
Michele
Was this Helpful?
0 -
HI Michele,
I don't know about you C# code - this is what I use in a .bat file.
SET DBUpdate="C:\Program Files\Zuken\E3.DBUpdate\DBUpdate.exe" 'define the location of the exe
SET IniFile=C:\test\db_config.INI 'location of the ini file
SET XLSFile="C:\test\db_data.xlsm" 'location of the excel file
SET LogFILE="C:\testt\db_result.log" 'log file for results' command line using the variable above
%DBUpdate% -i %IniFile% -d %XLSFile% -l %LogFile% -lang 01 -p 7 -t 2
From your code i am not sure if you are setting all those parameters.
Was this Helpful?
0 -
HI Sanu!
Thanks for your post. I managed to fix my code (it was a problem with "escapes") and now everything works fine.
Just a question. I read that only code 44 and 49 are available for languages but you are using 01.. why?
And another question (last). You are using "-p 7" but without the parameter "-o" to specify the protocol file.. is that correct?
Thanks again
Bye
Note: From your example I am discovering that DBUpdate supports also .xlsm file (with macro).. That's very good!
Was this Helpful?
0 -
Hi Michele,
The language code 01 is the same as 44, it provides the info back in English
'- P 7' is an optional parameter, the -o is required if referring to a file for the protocol in this case. I am only using defaults so, It has had no effect and I must have forgotten to remove it. Don't remember how long that has been there !! I could be wrong but I believe without the parameter the default action is P 7 i.e all changes, additions and delete actions are recorded.
Was this Helpful?
0
Please sign in to leave a comment.
Comments
10 comments