Execute a sql query in the server

[AriSilva]

Is it possible to execute a sql query directly using the csdb file in the server, without having to do a synctada(get)?
In other words, I was thinking in a batch application to be used by the field supervisors to access the data in the server and producing some reports, without having to download the data itself.
I do not want for them (supervisors) to have the data base on their computers, that is, I want to preserve the data, but allowing the supervisors to have some statistics directly form the server.
The idea would be to send them a .pen application (not the .bch and the .apc), the login and psw informed in the .pen, that would query the server and produce the reports.

[Gregory Martin]

This will be possible in the next version of CSPro, 7.5, because the case data on CSWeb will be stored in easily accessible (queryable) tables. For now, though, there is not an easy way to do what you are talking about.

[josh]

You could create a new dictionary that stores the computed indicators. Then create a batch application that downloads the raw data from the server to a PC somewhere, computes the indicators, copies to the new dictionary and syncs them to the server. You could use a Windows scheduled task to run this at regular intervals. Then the supervisors can download the computed indicators from the server and view the report.

Alternatively you can some of the open source systems that take data from CSWeb and convert it to relational tables :
https://github.com/IstatCooperation/CSPro2sql
https://medium.com/@nahomt/working-with ... 6161b84ffa

[AriSilva]

Thanks, Josh and Greg.
This idea of a database of indicators is good, and implementable, I´ll think about that, it should not be very difficult, since everything is inside cspro´s scope.
Alternatively, I could export my database to another minidict with only the variables needed for the control, no critical or sensitive data.
Another one should be to use the paradata, but I´m afraid I´m still very short on the paradata theme. I do not have any "intimacy" with it, if you know what I mean.
And last, what about those examples you´ve mentioned? Have you ever had a look at them? Are they easy to understand and adapt?
Is it worthwhile to invest time on looking at them?
https://github.com/IstatCooperation/CSPro2sql
https://medium.com/@nahomt/working-with ... 6161b84ffa

[josh]

I have worked a a bit with the first one (the ISTAT solution). We helped the ISTAT team a little bit with the initial implementation in Ethiopia and then I helped set it up and customize it for the census of Malawi. It is quite powerful but setting it up takes some work, especially if you are not familiar with the technologies that is uses (Java, Tomcat, MySQL). Customizing the reports requires someone with decent Java programming skills and knowledge of MySQL although if you just want to get database you wouldn't need the reporting component. I know that recently ISTAT was doing some work to simplify the setup but it is much more complex than CSWeb.

The second solution is newer and I am less familiar with it. It uses Python which is much more familiar to those coming from a data science background. Python is a bit more friendly than Java. They are planning to use it for an upcoming census but it is not as battle tested as the ISTAT solution which has already been used in multiple countries.