Switch to: V11V10V9V8V7V6V5

WebOnSQLite Example

This example we consider as step by step Tutorial we did to get the working example “WebOnSQLite”. The database and Project with reports was prepared before in the Valentina Studio. They are present in the example folder when you install the Valentina for Xojo ADK.

In this example the usage of VProject and VReport classes of the Valentina ADK API is demonstrated.

This tutorial and example code were designed for Xojo, Inc's Xojo Web Edition.

Make New Web Project

  • Choose Menu “File→New Project…”. In the dialog choose template “Web” and click button “Choose”.
  • Save it into folder “V4RB/Examples/VReport/WebOnSQLite” with name “WebOnSQLite.rbp”.
Any examples or products associated with V4RB are related to compatibility with Xojo. This acronym refers to the previous name of Xojo.

Add Init/Shutdown

To develop using the Valentina Report DLL (or Valentina DB DLL), you must initialize it. After your application is finished using it, you also need to provide shutdown code. The best place to do this is the Xojo App class:

  • Double click App class
  • Choose method Open() and paste there the following code:
// This is a good place to init Valentina Components.
// In this example we will init ONLY Valentina Report DLL.
//
// We specify here empty strings of MAC, Win, Linux serials.
// You should specify here your serials instead.
//
// NOTE: You may want to use some smart things to split serial or even encode it
// to easily prevent its hacking from your compiled application.
//
// NOTE: In Close() handler call of shutdown of Valentina Reports is present
//
Valentina.InitReports( "", "", "" )  
  • Choose method Close() and paste this code:
// Finish the work with VREPORT DLL
//
Valentina.ShutDownReports()

WebPage - Add Properties

In Xojo, switch to the “WebPage” TAB-panel. Double click grey area of window to open one more TAB-panel where we can write code for this WebPage. We are going to add some properties to this class. Click the “Add Property” button and create these properties:

  • mProject as VProject
  • mReport as VReport
  • mDataSource_SQLite as String

WebPage - Open Project

Next we want to open “reports.vsp” Valentina Project file, prepared in the Valentina Studio. Let's add this file into the “WebOnSQLite” folder near to RS project file.

For this, click button “Add Method …” and copy the following code:

OpenProject()
 
Dim f as FolderItem
 
try
  // For simplicity in this example we show only the work with LOCAL Valentina Project file (.vsp),
  // But .vsp file can be under some [remote] VALENTINA SERVER,
  // and request data from another VSERVER or from mySQL/PostgreSQL/ODBC DB servers.
  //
  f = GetFolderItem("").Parent.Child( "reports.vsp" )
 
  mProject = new VProject( f )
  mProject.Open()
 
Exception err as VException
  // LogError Str(err.ErrorNumber) + ":" + err.Message
end

Here we also get info about how many reports are stored inside the project.

When you run this project, Xojo creates a “My Application.debug” file in sub-folder “My Application.debug”; this is why specify “../reports.vsp”.

WebPage - Make Report

Now we can make the object of a VReport class. To make it we should specify three things:

1) Name or Index of a report to specify which report to load from the Valentina Project file. In our example, we just take the first report from the project file.

2) Datasource from which the report engine will load data. This is just a string that tells the report engine where to find a local db file or how to connect to a remote DB server. Create a separate function “PrepareDatasource_SQLite”:

PrepareDatasource_SQLite()
 
dim db_lite_location as FolderItem = getFolderItem("").Parent.Child( "reports_db.sqlite" )
 
// Extract path as string:
dim db_lite_path as string = db_lite_location.ShellPath
 
// -----------------------------------------------------------
// ShellPath returns us escaped POSIX Path. We need NOT escape it.
// In your own code you should use free plugin http://code.google.com/p/macoslib/
// to get not escaped path.
// In this example we will just replace  "\ " to " " not to depend on third party plugin.
//
db_lite_path = ReplaceAll( db_lite_path, "\ ", " " )
 
// Produce datasource-string
mDataSource_SQLite = "sqlite://" + db_lite_path

3) SQL Query to be applied to that datasource. This query (usually a SELECT, but can be e.g. Stored Procedure call) returns a cursor with the same number of fields and with the same names of fields as the selected report expects.

Finally, make a function that generates VReport object. This function is very simple and has one line of code only.

MakeNewReport()
 
try
  mReport = mProject.MakeNewReport( 1, mDataSource_SQLite, "SELECT * FROM T1" )  
catch e AS VException
  // ... do something WITH this error.
  // e.g. WRITE it INTO log file OR display ON SOME HTML page
END 

WebPage - Generate Reports

Now we can prepare the function, which will generate report in this or that format and save it to disk. Make new function with name “GenerateReport” and copy paste the following code.

You need actually one line of code to generate a report: mReport.PrintToDisk().

GenerateReport()
 
//
// In this example we show how to generate PDF and JPG.
// In your app you will generate what you need.
//
 
dim f as folderItem
dim f2 as FolderItem
 
// Define location for generated reports:
f =  getFolderItem("").Parent.Child( "report.pdf" )
f2 = getFolderItem("").Parent.Child( "report.jpg" )
 
// MAKE PDF on disk.
mReport.PrintToDisk( f, EVReportPrintType.kToPDF, 1 )
 
// MAKE JPG on disk.
mReport.PrintToDisk( f2, EVReportPrintType.kToPicture_JPG, 1 )

WebPage - All Together

Now let's call all this from WebPage.Open() event handler. Click it in the tree on the left and copy paste this code:

PrepareDatasource_SQLite()
 
OpenProject()
 
MakeNewReport
GenerateReport
 
ShowReportsInWebPage 

ShowReportsInWebPage() – we will consider it below. Make it for now as empty function to be able to compile the project.

Testing

Now it is time to test your progress. At this state we should have a Xojo project and a folder containing three files:

  • reports_db.sqlite
  • reports.vsp
  • WebOnSQLite.rbp

We can try to run this project in debug mode. Click RUN button.

If you didn't make a mistake you should see two files created by your work:

  • report.pdf
  • report.jpg

WebPage - HTML Page

Create in the folder of example new text file with name “vreport_show.html” and the following text. This is the simplest page that is going to show our PDF and JPG of the report.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
 
<html lang="en">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title>Valentina Report</title>
	<!-- Date: 2012-09-16 -->
</head>
<body>
 
<h2>Report as PDF</h2>
	<a href="report.pdf">PDF File - Click here to see it...</a>
 
<h2>Report as Image/JPG</h2>
	<img src="report.jpg">
</body>
</html>

You can now double click it to open in a browser and see the link to generated PDF and the picture of generated JPG.

WebPage - Show Generated Reports

Now, when you have the reports generated to disk (or into RAM buffer), you may want to do something with them from your WEB application. Usually this can be

  • show to browser
  • send by email
Because web applications usually require special set ups based on website directories to allow references to HTML pages, we cannot guarantee this will work 100% of the time. In this case, we are assuming compatibility.

Now will will show you how to load HTML page using HTMLViewer in your WEB app.

Add control HTMLViewer into WebPage. Resize it to the whole window, and LockBottom, LockRight properties in the inspector.

Now copy paste the following code into function ShowReportsInWebPage(), which we have created before:

ShowReportsInWebPage()
 
  //
  // READ HTML file to String
  //
  dim str as String
  dim textStream as TextInputStream
 
  dim htmlPage as FolderItem = GetFolderItem( "" ).Parent.Child( "vreport_show.html" )
 
  //if htmlPage.exists then
  textStream = TextInputStream.Open( htmlPage )
  str = textStream.readAll
  textStream.close
  //end if
 
  HTMLViewer1.LoadPage( str )  

VProject under Valentina Server

If you want to work with Valentina Project (*.vsp), which is managed by a Valentina Server, then you should change few lines of code because we should open the project not from disk but from a connection to Valentina Server.

  • Add into class WebPage one more property 'mConnection as VConnection'.
  • Change OpenProject() method:
    • Add 2 lines to make and open connection to vserver.
    • GetFolder() should have just name of project, path to disk is not important.
    • Add mConnection into constructor of VProject.
OpenProject()
 
Dim f as FolderItem
 
try
  // We should establish connection to VSERVER
  mConnection = new VConnection( "localhost", "sa", "sa", 51432 ) 
  mConnection.Open() 
 
  // Now we can open project from that VServer:
  f = GetFolderItem("reports.vsp" )
 
  mProject = new VProject( mConnection, f )
  mProject.Open()
 
Exception err as VException
  // LogError Str(err.ErrorNumber) + ":" + err.Message
end