Switch to: V11V10V9V8V7V6V5

SELECT ... FOR REPORT

  • [NEW for V4.0]
  • [v5] - added [ENCODE BASE64], and modified that for HTML we get back TEXT field.



Syntax

SELECT ... 
[FOR REPORT project_name.report_name 
  [AS {PDF|HTML|JPG}] 
  [PAGES start_page TO end_page] ]
  [ENCODE BASE64]

Description

This non-standard SQL extension allows you to produce a Valentina Report via SQL command.

This feature will be important for the following cases:

  • Access via ODBC driver - because it can talk to a Valentina Server only via SQL.
  • Stored Procedures - because only SQL can be used in a procedure. This also opens your way to:
    • Reports on a Schedule Event.
    • Reports on a Table or Database level trigger.
This command returns a one-row cursor with a single field, which contains the report.

The SELECT query is passed to report engine. So it should SELECT fields as your report expects. Of course, this query can have WHERE clause to select set of records on which report will be generated. This query should not have ORDER BY, because report has own mechanism of sorting.

The type of field will be:

  • BLOB - for binary-format reports, such as PDF/JPG.
  • TEXT - for text-format reports, such as HTML. Additionally, if you have specify ENCODE BASE64 argument, then you also get TEXT field, because even PDF and JPG will be encoded into text format.

To be able to use this report, you have to extract it using API commands of your programming language, e.g.

curs.BLOBField(1).ToFile( path )

Some ADKs allow to extract the report to a RAM buffer as well. See Valentina API Reference for your programming language.

Project File

You should specify a Valentina Project file (following the same rules as you do for database), i.e. just the name without path. Therefore,

a) working with the local Report Engine and local .vsp file, your application must open project before use it.

b) working with a remote VServer, this command can open/close the specified project itself (if it was not opened yet), because VServer registers databases and projects in the masterdb and only that dbs/projects available under vServer.

Arguments

  • project_name - The name of a Valentina Project (.vsp) file.
  • report_name - The name of a report template from that project file.
  • PDF - Creates the report in the PDF format.
  • HTML - Creates the report in the HTML format.
  • JPG - Creates the report in the JPG format.
  • PAGES - specifies the range of pages of report to produce.
    • start_page - specifies the number of start page for report.
    • end_page - Specifies the number of end page for report.
  • encode - allows to encode result into
    • base64 - textual format.

Errors

  • PROJECT_NOT_FOUND - Valentina engine or Valentina Server did not find opened project with the specified name.
  • REPORT_NOT_FOUND - The specified Valentina Project do not contains report with the given name.

Examples

Example

SELECT * 
FROM T1 
FOR REPORT Project_1.Report_1 AS JPG
SELECT * 
FROM T1 
FOR REPORT Project_1.Report_1 AS JPG PAGES 1 TO 10