Table of Contents
VProject Class: Report Factory Methods (on ANY Datasource)
The following pair of methods was added into v4.9 of all Valentina ADKs.
This pair of methods allows specifying any data source to be used in a report with the help of inDataSource parameter. This parameter is a string starting with the prefix, which specifies a kind of data source, and suffix that contains information, required to open or connect that data source.
| prefix | suffix |
|---|---|
| mssql:// | host = '192.168.1.2', port = 1433, user = 'root', password = 'root', dbname = 'db1' |
| mysql:// | host = '127.0.0.1', port = 3306, user = 'sa', password = 'sa', dbname = 'db1', timeout = 5 |
| odbc:// | dsn = 'access', user = 'name', password = '123' |
| postgresql:// | host = '127.0.0.1', port = 5432, dbname = 'db1', user = 'sa', password = 'sa', timeout = 0 |
| sqlite:// | path_to_local_db |
| valentina:// | path_to_local_valentina_db |
| vserver:// | host = '192.168.1.100', user = 'sa', password = 'sa', dbname = 'db1', port = 15432, timeout = 10, ssl=false, options='ABCDEFG12345678' |
| vsqlite_server:// | host = '192.168.1.100', user = 'sa', password = 'sa', dbname = 'db1', port = 15532, timeout = 10, ssl=false |
You can also define a connection to any supported server data source via SSH tunnel, just add a set of parameters to your inDataSource string, depending on the type of authentication.
| authentication | parameters |
|---|---|
| password | ssh = 'true', ssh_host = '192.168.1.5', ssh_port = 22, ssh_user = 'root', ssh_auth = 'password', ssh_password = 'some_passw' |
| public key | ssh = 'true', ssh_host = '192.168.1.5', ssh_port = 22, ssh_user = 'root', ssh_auth = 'public key', ssh_key_path = 'path_to_key', ssh_passphrase = 'some_phrase' |
MySQL and PostgreSQL servers support connection via Unix socket (or named pipe on Windows). You can find the examples of data source strings for this kind of connection below.
| prefix | suffix |
|---|---|
| mysql:// | socket = '/tmp/mysql.sock', user = 'sa', password = 'sa', dbname = 'db1' |
| postgresql:// | socket = '/tmp', port = 5432, dbname = 'db1', user = 'sa', password = 'sa' |
Valentina Server, MySQL, and PostgreSQL server data sources support SSL connection, just add ssl = 'true' to the data source string. PostgreSQL will search for certificate and private key at the standard paths in the user's home directory. For MySQL it is possible to define these paths explicitly, using following parameters: ssl_key_path, ssl_cert_path, ssl_ca_path.
NOTES:
- Parser of this connection string allows you to write string literals with OR without single quotes. Inside of single-quoted literal, you can use escape character to escape single quote.
- You can use ',', ';' or spaces as delimiters of parameters. Delimiting spaces are allowed only for strings with values, that are single-quoted.
- If a value contains any delimiter (',', ';', ' '), either escape it or take the whole value to single quotes.
- For PostgreSQL data source you can use any parameter, supported by PostgreSQL native connection string.
VProject.MakeNewReport( index )
Declaration:
MakeNewReport(
inIndex as integer,
inDataSource as string,
inQuery as String = NULL,
inBinds() as StringOrVariant ) as VReport
Parameters:
- inIndex - The index of a report in the range 1 .. ReportCount.
- inDataSource - The data source to be used.
- inQuery - The SQL string of a query or NULL to use query prepared by VStudio and stored in the VProject.
- inBinds - The array of bind parameters. Usually, this is an array of Strings or array of VARIANTs. See API Reference specific to your language.
Currently, the preferred way to pass data to the database instead of putting the values directly into the SQL statement is to use Report Parameters, they can be used both in the Valentina Studio and ADK code for reports of any complexity.
Description:
This method plays the role of a VReport class factory. It returns a VReport class instance for the Nth report of this project. It will return NULL if the specified report is not found.
- To create a report instance, the VREPORT DLL has to know:
IMPORTANT: When designing a report in Valentina Studio Pro, you assigned a SQL SELECT query to this report. You used the fields returned by that cursor to build the layout of this report. But that was in DESIGN mode.Now, in RUNTIME mode, you can provide a completely different database and use a completely different query. The only requirement is that the query used produces a cursor with the same field names as the field names used by the report layout. If not the report will produce nothing for 'unmatched' fields.
See the description of VDatabase SQL Methods for details and example on bound parameters.
Example:
dim theReport as VReport theReport = my_project.MakeNewReport( reportIndex, "sqlite://c:/somedb.sqlite", "SELECT fldName, fldPhone FROM tblPerson" )
VProject.MakeNewReport( name )
Declaration:
MakeNewReport(
inName as string
inDataSource as string,
inQuery as String = NULL,
inBinds() as StringOrVariant ) as VReport
Parameters:
- inName - The name of a report.
- inDataSource - The data source to be used.
- inQuery - The SQL string of a query or NULL to use query prepared by VStudio and stored in the VProject.
- inBinds - The array of bind parameters. Usually, this is the array of Strings or array of VARIANTs. See API Reference specific to your language.
Currently, the preferred way to pass data to the database instead of putting the values directly into the SQL statement is to use Report Parameters, they can be used both in the Valentina Studio and ADK code for reports of any complexity.
Description:
This method does the same as the above method, except that the report is specified by its name. Please see the detailed description above.
See the description of VDatabase SQL Methods for the details and example of bound parameters.
Example:
dim index as Integer dim theReport as VReport theReport = my_project.MakeNewReport( "report_1", "sqlite://c:/somedb.sqlite", "SELECT fldName, fldPhone FROM tblPerson" )