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, which you have specify 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 have 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 use this report, you need 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 also. See Valentina API Reference for your programming language.

Project File

This command specifies a Valentina Project file by same rules as we work with a 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 file before use it. Then engine will know this file by its name.

b) working with a remote VServer, this command can self open/close the specified project by name if it was not opened, because VServer registers databases and projects in the masterdb. If project already is opened then this command just use it.

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