how to create a database view in onbase

[Noel_Jos_de_Fre]

hello, I'm developing an application that works with onbase, for this application to work I need access to a database view, but I have little experience with onbase and I don't know how to create a view, could you explain to me how I can create it this view of the bank?

[Bill_Schoby]

If you have a local installation (not GCS) and SQL Server then SSIS might be a good option.  If that does not work, this is likely less than ideal, but if you have Reporting Dashboards, you can create a Data Provider and use Management Console to create a scheduled task to export the data from the provider; unfortunately I believe the only export format is Excel and I don't know how "real-time" you need your data to be.  Third option, if you have someone who is Unity API certified, you can use the API to execute a WV filter then do whatever you need with the results.

[Joel_Moore2]

Hi @Noel Jos de Freitas Teixeira , 

This likely goes without saying but I'd recommend exercising caution when executing custom SQL against OnBase. The tables by default are indexed very well for the queries OnBase will generate, but not necessarily for custom SQL. Executing inefficient SQL can cause serious performance issues, even if you're only selecting data.  

When I've done this in the past, I'd follow these steps that use SQL accounts internal to OnBase: 

1. Create the view with the 'hsi' internal SQL account 
2. Grant the 'viewer' internal SQL account access to the new view
   1. Ex: GRANT SELECT ON hsi.mynewview TO VIEWGP 
3. Configure the application calling the SQL View to utilize the 'viewer' account in its connection string 

The 'hsi' account is the power user of the OnBase database, which will have the appropriate rights to create the SQL view. The 'viewer' account only has SELECT access to the OnBase database tables, which is why I'd recommend using this to actually call the view. You can find more about these internal SQL accounts in the Database Reference Guide that pertains to your version of OnBase. 

I hope this helps!

Thank you,

Joel 

[Tia_Brott-Silke]

The "how" depends on your RDBMS - there are minor differences between SQL Server and Oracle.

Curious why your application needs a veiw per se?  A view just creates a live table-like structure that can have simple or extensive code behind it to pull data together from many other objects, function/package calls, etc. From the client side it looks just like a table.

I would contact FLOS to be sure whatever you do is not violating your EULA with Hyland, but I can say general best practice is to create such "custom objects" in your own user/schema not directly in the OnBase user/schema such as HSI. You arere not allowed to make any changes under HSI without engaging Hyland database resources.

Finally, be aware there is a VIEWER read only user/schema built in which is ideal for such things as it cannot manipulate HSI data. Maybe you can devise a query to just use the VIEWER user.

[Joel_Moore2]

Creating a SQL view is essence is fine, however creating it with the SCHEMABINDING option would be against the terms of use as it locks the underlying tables from having their schema altered.

[Scott_Johnson3]

Hi @Noel Jos de Freitas Teixeira 

I set up a separate DB on the same server to put all my table views in.  That way I do not ,have to mess with the the OnBase DB per say.

Good Luck

Scott