Hi,Does anyone know how the Update-PWSQL command works?I am trying to modify a table in the environment and it is impossible for me.
I can not find any information about that command.
Thank you very much
Traditionally the API has not allowed any SQL statement other than SELECT. There are other functions(commands) for modifying attributes (environment tables).
Thanks Dean,is that I must modify attributes of document versions. There is no other option than doing it directly with sql to the database.
I can see your problem, i.e. you want to modify the document attributes of document versions. The Update-PWSQL Cmdlet uses the PW SDK function aaApi_ExecuteSqlStatement() and as Dean pointed out, it will not let you UPDATE database tables managed by ProjectWise.
You can get around this limitation if you CHEAT. Please keep in mind that CHEATING means that if YOU break it, YOU fix it, as this workaround is not supported by Bentley. OK, the CHEAT is to create a database view of the table that you want to UPDATE. Then the call will work when you UPDATE the database view which actually UPDATEs your environment table.
*** PLEASE NOTE ***
You must also understand that as long as you only UPDATE existing rows in the environment table, and don't DELETE or INSERT any rows, the UPDATEing of columns for user attributes via a database view will work and is "safe". However, you WILL create data corruption if you INSERT or DELETE rows, or if you UPDATE any of the columns created by ProjectWise when it made the table into an Environment table (o_projectid, o_itemno, a_attrno, etc.). UPDATEing columns that represent user attributes is OK other than client applications don't know that the data has changed, so users won't see your changes until their client application fetches the data again (or they force a refresh).
Again, if you break it, you will be the one to fix it as it isn't a bug in ProjectWise since the CHEAT is bypassing the check that keeps you from UPDATEing a table managed by ProjectWise!
You could create a stored procedure in your database to update rows and call that procedure from Update-PWSQL. However, the "rules" for directly manipulating tables and their data still apply, it isn't supported, and if you break it, you fix it. Again, as long as you only UPDATE the data in columns that represent USER ATTRIBUTE data, and don't INSERT or DELETE any rows, this will work, but you will still have the issue that users won't see the changes until their client application fetches or refreshes the data.
Dan WilliamsSolution ConsultantBentley Systems, IncorporatedPortland, OR, USA (Pacific Time UTC-08:00)
Hmm, I forgot about anything that might be configured under "Update Value", i.e. values of attributes that automatically change when you update some other user attribute's value. Those won't be enforced (of course) if you bypass the API, so again, you are responsible for any changes that you make to your data.
Hello dan,what I intend to update are only user attributes in the environment table. I will not insert or delete rows.I think I will do a stored procedure that will be called with the Update-PWSQL command.
Thank you, Dan.