How to fill fact table in DeepSee via JDBC
Hi all.
I have some problem with getting data from external source (PostgreSQL) via JDBC and load it in DeepSee and I resolved some problems while writing this question :)
So..Below I proved my steps of working:
- Created SQL Gateway Connection
- Created class for loading data via JDBC
S Connection=##class(%Library.SQLGatewayConnection).%New() S sc=Connection.Connect("jdbcurl","user","pass",0) If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit S sc=Connection.AllocateStatement(.Statment) S query="SELECT * FROM students" S sc=Connection.Prepare(Statment,query) S sc=Connection.Execute(Statment) //Getting result set
- In 2 line I got exception (not saved it) and I exclusive this option
- Next I Created projections via SQL wizard and now all tables stored in Cache and I get needed data via
##class(%ResultSet.SQL).%Prepare() - After that I create model (StudentRating) in sequence by result set
- Manually in Architect rebuild fact table
- Next if need to update information I clear data from StudentRating by method
%KillExtent() and repeat 3 and 4 points.
What I want to change in current situation?
1) Get needed information by JDBC without projections (If I understand correctly it's not recommended option https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...)
2) Auto-rebuild fact table (2-3 times in day)
Please advise me how to resolve 1 and 2 point or suggest best practices for my question.