Switch to: V9V8V7V6V5

VProject Class: Report Factory Methods

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

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.

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. Can be NULL to use query stored in the VProject.
  • 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 by 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 the 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 the 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 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.

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