VProject Class: Report Factory Methods [Valentina Database Wiki v10]

VProject Class: Report Factory Methods

The following pair of methods was added into v4.9 of all Valentina ADKs.

This pair of methods allows to specify any datasource to be used in a report with the help of inDatasource parameter. This parameter is a string starting with prefix, which specifies a kind of datasource, and suffix that contains information, required to open or connect that datasource.

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
vsqlite_local://	path_to_local_db

You can also define a connection to any supported server datasource via SSH tunnel, just add a set of parameters to your inDatasource string, depending on 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 datasource 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 datasources support SSL connection, just add ssl = 'true' to the datasource 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 into single quotes.
For PostgreSQL datasource 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 datasource to be used.
inQuery - The SQL string of a query.
inBinds - The array of bind parameters. Usually this is an array of Strings or array of VARIANTs. See API Reference specific to your language.

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:

Datasource that will be used to get data.

Query that should be executed to get data.

Parameter inQuery has to contain any SQL that returns a VCursor. Usually this is a SELECT statement, although it can be a SHOW statement or a CALL procedure that returns cursor.

Parameter inQuery can be NULL on default. In this case the Report will use the original query, which is stored in the VProject, i.e. the same query used in the Report Editor, when this report was designed. You still can provide another query with the help of this parameter. For example you can change WHERE statement to select other records. In fact you can use very different database and table, the only thing important is that the cursor should have fields with same names as report expects.

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 during 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 datasource to be used.
inQuery - The SQL string of a query.
inBinds - The array of bind parameters. Usually this is array of Strings or array of VARIANTs. See API Reference specific to your language.

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 on 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" )

Table of Contents