Introduction
Paradigma Software has created several database products for business and developers based on its original database technology, Valentina. Including the following products:
• Valentina Office Server, a standalone business ready database server for Apple Mac OS X and Microsoft Windows.
• Valentina Studio, a database browser and administration tool for creating, editing and administrating Valentina-based databases on a local system or on a network.
• Valentina Application Developer Kit products for extending developer solutions with Valentina database runtimes.
Throughout this article we will focus on the last one from the list, the Valentina DB Application Developer Kit product. But in order to use it, we must first to install the Valentina Server, which is a business-ready database server incorporating the ultra fast Valentina DB and Valentina Reporting Services.
All of the necessary software can be downloaded from the Paradigma Software site. To install the Valentina Server you need to follow the setup and after installing it you will find a folder with the following structure:
Valentina PHP
Valentina PHP represents the Valentina DB solutions for building PHP applications around Valentina DB. Valentina DB ADK is available for Linux, Mac OS X and Windows, as well as iOS. Valentina Application Developer Kit (ADK) is free if you are developing local database applications only.
Valentina PHP Features
→ mySQL Compatible API for Easy Porting (as you will see in the next section of this article)
→ PEAR API Option – Utilize a PEAR PHP class to access any relational database
→ ADOdb API Option – support lets you use the same methods used by many popular databases on the market, for easy porting of solutions to Valentina.
→ You can use with PHP 4 or PHP 5, Valentina PHP works with both.
Installation
1. Copy the installer to a directory on your computer.
2. Run the installer. Follow the instructions in the installer.
3. All Valentina ADKs installer for any OS tend to follow the next schema to install files (in my case C:Users…DocumentsParadigma SoftwareVPHP_5). Depending on your PHP version, you need to take the two corresponding dll’s and put them into your extension_dir folder.
4. Then open your php.ini and add the next two lines into the Dynamic Extensions section:
extension=php55_valentina.dll
extension=php55_pdo_valentina.dll5. Restart your Apache service and then check the Valentina support on localhost using the phpinfo() method. You need to see something like in the next figure:
Now you have the Valentina DB ADK for PHP installed on your machine. In a section below we will see the main methods used in working with Valentina PHP and see examples of how to use them.
Valentina Studio is a database tool used to create, administer, query and explore Valentina DB, MySQL, Postgre and SQLite. To install, you need to download it from here and follow the setup. After installation, you will find a folder with the next structure, placed also in the Paradigma Software folder, as for the Valentina Server:
Next, we will learn some important things about using Valentina Studio. After you open the Valentina Studio tool, you will discover a window containing three panels: servers, databases and project.
As you can see in the figure above, the bottom part of the Servers panel contains the Connect to… button. This will take you to a new window from which you can choose your Database Server: MySQL, ODBC, PostgreSQL or Valentina.
To connect to a MySQL Database Server from Visual Studio, you will need your MySQL user and password.
Fig6
In the next section we will create a report using Valentina Studio, applied to a MySQL database, tennis_players previously created by me using MySQL Benchmark.
Note: Before connecting to MySQL from Visual Studio make sure that your Apache service is started.
After pressing the “OK” button you will see the next figure, containing the tennis_players database and its table structure:
Double click on the players table to see the records it contains:
And now, using the players table, let’s create a report using the Valentina Studio tool (in the last section of this article we will create a new report from PHP). To do that you should follow the next steps:
1. Project->New Project->Local
Notice that C:Users…DocumentsParadigma SoftwareVPHP_5 ExamplesVReport is the path where your report will be created and also that the extension for the report is .vps. Name your project and press the Save button.
2. After pressing the Save button you will see something like in the figure below. From the Create menu choose the Report… option.
3. Follow next steps, using your own queries:
Select your fields for the report, group and sort and then choose the layout and style for your report:
After completing all the steps, you will finally see the Tennis_report report draft:
Press the Preview button to see how your final report will look:
Notice that the Visual Studio tool provides a very useful and complete tool in working with reports. All previous steps can be also used to create a report using Valentina Server. To connect to Valentina Server, installed first, the user and the password are both “sa” and the port is 15432.
Valentina’s PHP Main Functions
The reference for Valentina PHP ADK can be found here, but we will briefly present some of the most important methods together with some examples:
• valentina_connect()– Opens a connection to a Valentina and returns a Valentina Connection link identifier on success, or FALSE on failure. The arguments for this method are: [string server] – The Valentina server, but can also include a port number. e.g. “127.0.0.1” (the default value is localhost), [string username] – username, [string password] – password, [string encoding] – the encoding (the default value is UTF-8)
• valentina_close()– Closes the connection to the Valentina Server and returns TRUE on success, or FALSE on failure. The argument for this method is: [link_identifier] – identifier of a Valentina Server connection to close.
The First example uses the two functions above and connects to Valentina server and closes the connection. In case of failure it will display the error:
<?php
$connect = valentina_connect('localhost', 'sa', 'sa');
if (!$connect) {
die('Could not connect: ' . valentina_error());
}
echo 'Connected successfully';
valentina_close($connect);
?> • valentina_create_db() – Attempts to create a new database on the server associated with the specified link identifier. and returns TRUE on success or FALSEon failure. The arguments for this method are: [database_name] – The name of the database being created, [link_identifier] – The Valentina Server connection. If the link identifier is not specified, the last link opened by valentina_connect() is assumed.
This is a simple example of creating a database named new_database:
<?php
$connect = valentina_connect('localhost', 'sa', 'sa');
if (!$connect) {
die('Could not connect: ' . valentina_error());
}
$dbname = 'new_database';
if( valentina_create_db($dbname) === TRUE ) {
printf("Database %s created successfully", $dbname);
}
?> • valentina_select_db() – Sets the current active database associated with the specified link identifier on the server. Every subsequent call to valentina_query() will be made on the active database. Returns TRUE on success or FALSEon failure. The arguments for this method are: [database_name] – the database being selected, [link_identifier] – the Valentina Server connection, [string encription_key] – encryption key to be used if the database is encrypted, [int data_kind] – one of following constants: VALENTINA_DATAKIND_STRUCTUREONLY, VALENTINA_DATAKIND_STRUCTURE_AND_RECORDS, VALENTINA_DATAKIND_RECORDSONLY.
• valentina_query() – runs a SQL query and returns a positive VServer result resource to the query result, or FALSEon error. The arguments for this method are: [query] – the VServer query, [link_identifier] – the VServer connection, [bind_values] – An array containing values to be binded into sql query.
The next example uses the valentina_sakila database example designed to provide a standard schema that can be used for examples in books, tutorials, articles etc. To use this database in your Visual Studio, download the database, extract the archive and put the file in C:Program Files (x86)Paradigma SoftwareVServerdatabases folder then connect to Valentina Server from Visual Studio and you will see the database, then the last thing you should do is to press the Register button and after that you can open it. In a below example we will also use the valentina_salika_reports which can be placed into the C:Program Files (x86)Paradigma SoftwareVServerprojects folder after you downloaded and unzip the archive.
So, this example selects the valentina_salika database and creates a query on the table actor:
<?php
$connect = valentina_connect('localhost', 'sa', 'sa');
if (!$connect) {
die('Could not connect: ' . valentina_error());
}
$selectdb = valentina_select_db('valentina_sakila');
$query = 'SELECT first_name,last_name FROM actor';
$result = valentina_query( $query, $connect );
if (!$result) {
echo "DB Error, could not query the database".'</br>';
echo 'VServer Error: ' . valentina_error().'</br>';
exit;
}
valentina_free_result($result);
?> • vproject_create()– Opens the project specified by name (for vsever) or by path (for local). Returns ID of the VProject object. The arguments for this method are: [link_identifier] – The Valentina Server connection, [inPathOrName] –
The name of the project to be created under VServer or the Path for a new local project.
• vproject_make_new_report()– Creates a new instance of VReport based on the Valentina database. The arguments for this method are: [inVProjectID] – Identifier of a Valentina Project., [inIndex] – Index of VReport, [inReportName ] – Name of vReport, [inConnectionID] – Identifier of a Valentina connection., [inQuery] – The SQL string of a query.
• vproject_make_new_report_with_datasource()– Creates a new instance of VReport based on any supported datasource. The arguments for this method are: [inVProjectID] – Identifier of a Valentina Project., [inIndex] – Index of VReport, [inReportName ] – Name of vReport, [inDatasource] – The datasource to be used, [inQuery] – The SQL string of a query.
• vreport_print_to_buffer()– Prints VReport to the string. The arguments for this method are: [inReportID] – The VReport instance, [inType] – VReport print type (Metafile – 1, PostScript -2, PDF – 3, HTML – 4, LaTex – 5, SVG – 6, GIF – 100, JPG – 101, PNG -102, TIFF – 103, BMP – 104), [inStardIndex ] – start page index, [inEndIndex] – end page index.
• vreport_close()– Close VReport instance. inReportID – The VReport instance.
• vproject_close()– Closes the specified Valentina Project. inProjectID – Identifier of a Valentina Project to close.
The next example uses the valentina_sakila_reports.vsp project, the table film and the report Report_FilmCatalog and creates a PDF file, named report.pdf:
<?php
$connect = valentina_connect('localhost','sa','sa');
$project = vproject_create($connect, 'valentina_sakila_reports.vsp');
$datasource = "vserver://host='localhost', user='sa', password='sa', dbname='valentina_sakila'";
$query = 'SELECT * FROM film;
$report = vproject_make_new_report_with_datasource($project, 'Report_FilmCatalog', $datasource, $query);
$data = vreport_print_to_buffer($report, VALENTINA_REPORT_PRINT_TYPE_TO_PDF);
vreport_close($report);
vproject_close($project);
valentina_close($connect);
header('Content-Type: application/pdf');
header('Content-Disposition:inline; filename="report.pdf"');
print($data);
?> In the last example we will create a new report using our own database, tennis_players, we mention it in a previous section. Using the players table we will create an HTML report:
<?php
$connect = valentina_connect('localhost','sa','sa');
$report = "Tennis.vsp";
$project = project_create($connect,$report);
$datasource = vproject_make_new_report_with_datasource($project, 1, "mysql://host='localhost', user='root', password='', dbname='tennis_players',
'SELECT * FROM players'");
$data=vreport_print_to_buffer($report, VALENTINA_REPORT_PRINT_TYPE_TO_HTML, 1);
vreport_close($report);
vproject_close($project);
valentina_close($connect);
header('Content-Type: application/pdf');
print($data);
?> Conclusions
This article has explained about all the main Paradigma Software products and how to use Valentina PHP ADK as a PHP DB solution for your applications.
