Valentina Server 6 as SQLite on Server Solution

Recent betas of Valentina Server 6 now include our implementation of SQLite.

We’ve updated our wiki with information on features supported, using it as a datasource for Reports, REST API, SNMP, and pricing.

Read more about it.

Today (10 July) we have ship 6.0b20 of Valentina products:
http://www.valentina-db.com/download/beta/

This b20 provides quite polished combination of
Valentina SQLite Server + V4RB plugin for Xojo + Valentina Studio

Above Lynn’s post contains like to Article page in our Wiki, which is entry point to learn about Valentina SQLite Server. It also contains links to at least 2 major wiki pages:

Good News: we have implement 100% compatible Prepare() method.

Your code, which uses standard Xojo’s SqliteDatabase.Prepare() method, which returns SqlitePreparedStatement class will work with our plugin without any changes.

* BindType( zeroBasedIndex as Integer, type as Integer )
* Bind    ( zeroBasedParam as Integer, value as Variant )

* SqlExecute( ParamArray inBinds as Variant ) as integer
* SqlSelect ( ParamArray inBinds as Variant ) ss RecordSet

In the same time, our VSqlitePreparedStatement class adds methods to bind values following to SQLite enging

* BindInt   ( zeroBasedParam as Integer, inValue as Integer )
* BindInt64 ( zeroBasedParam as Integer, inValue as Int64 )
* BindDouble( zeroBasedParam as Integer, inValue as Double )
* BindText  ( zeroBasedParam as Integer, inValue as String )
* BindBlob  ( zeroBasedParam as Integer, inValue as Variant )
* BindNull  ( zeroBasedParam as Integer )

Good News: We support both LOCAL and REMOTE work in the same way.

While cubeSQL plugin provided only client-access to cubeSQL server, with similar but somehow different API, we have implement for SQLite the same behavior as for Valentina DB in our products.

Using our V4RB plugin, you can use OUR SQLite engine (stored in the VKERNEL.dll) for LOCAL mode, and you can use our client to access SQLite Server. Both ways have 100% compatible API working with SQLite database.

In both cases you are using the same VSqliteDatabase class.

Good News: easy migration of existed Xojo apps that use local SQLite db to Valentina SQLite Server.

We have made wiki page, which demonstrate how it is easy.

You need to change only:

  1. Add 2 lines of code to init and shutdown V4RB plugin
  2. Change the name SqliteDatabase to VSqliteDatabase
  3. Write few lines of code which will care about connect() to Valentina Sqlite Server.

All other your code should work as is, including Prepare() and binding.

Good News: Every Xojo Database Developer can use Free Valentina SQLite Server with 10 connections to SQLite.

As well as 5 connections to Valentina DB Server.
As well as 5 connections to Valentina Report Server.
As well as Free Valentina Studio. See below.

Do you know any other free Report Server with at least 1 connection? :slight_smile:

Good News: One of Valentina SQLite Server advantage is Valentina Studio

Valentina Studio v5.x (yes, v5.x, not v6) provided access to

  • LOCAL Valentina DB and SQLite DB
  • Remote Serves: mySQL, postgreSQL, Valentina DB.

Valentina Studio 6.0 adds support to

  • Remote Valentina SQLite Server (mac, win, linux)
  • MS SQL Server Windows, you can now access from mac, win and linux.

But lets talk about SQLite.

Valentina Studio provides great support of SQLite engine, for example it do quite complex job to allow you edit existed tables, as empty, so with records. It is hard to find other such powerful tool on the market, yet free for most features.

And now, you can use Valentina Studio and its power to work remotely and collectively with your SQLite dbs under Valentina Server.

Some team or group can have Valentina Server, and develop in the same time

  • set of Valentina Reports
  • schema f SQLite db

Members of this group can be far away from DB Server location.

FAQ: Some people ask what advantage of SQLite Server, if WAL mode of SQLite engine allow to access db from different apps?

Answer: This mode of SQLite works on local machine for local processes only. Server opens way to work with that DB even overseas.

Exists many other advantages of Valentina SQLite Server. Some of them mentioned above:

  • Valentina Reports
  • Valentina Studio
  • unified API

Additionally Server provides GIFT features, e.g. REST API, which you can use to access to SQLite DB e.g. from Android or other mobile platforms.

btw, about REST API for Valentina Server 6.0 … which works for both Valentina DB and SQLite DB.

You can read article about it with examples on Python here:
REST for Valentina Server 6.0

We did not provide example how to work with REST from Xojo. If somebody can add some words about how it is easy to use REST from Xojo, it will be helpful.

Good News: Valentina SQLite Server 6.0 have Async Notifications (as postgreSQL)

Yes, this feature is new for v6.0 of VServer, and it works for both Valentina DB and SQLite DB.

Details in article Notifications in Valentina Server v6.0

[quote=199652:@Ruslan Zasukhin]Good News: Valentina SQLite Server 6.0 have Async Notifications (as postgreSQL)

Yes, this feature is new for v6.0 of VServer, and it works for both Valentina DB and SQLite DB.

Details in article Notifications in Valentina Server v6.0[/quote]

This is something I’ll be very interested in trying out.

:slight_smile:

[quote=199648:@Ruslan Zasukhin]Valentina Studio 6.0 adds support to:
Remote Valentina SQLite Server (mac, win, linux)
MS SQL Server Windows, you can now access from mac, win and linux.[/quote]

I was able to connect to MSSQL using Actual’s ODBC, but all I received back were system tables, and not the actual DB data that ODBC was configured for. I’m hoping for a more functional ODBC module than is currently in v5. Are you planning to implement this?

Hi Lee,

  1. if you are interested in MS SQL, then I invite you to download 6.0b20.
    MS SQL plugin also exists in FREE version. Nothing prevent you to just use it :slight_smile:

In 6.0 we have to add plugin for MS SQL, which works by native protocol. This is faster and better in many regards, because it is integrated into all main features of Valentina Studio.

I think there is no many sense to use ODBC plugin for MS SQL from now when you working with Valentina Studio. ODBC plugin exists as way to work with databases, which do not have so good implementation via own plugin in Valentina Studio.

  1. Of course we going to improve ODBC plugin also.

If you have see any troubles, the best way is to report into our bug tracker providing as many details as you can:
OS, version, ODBC driver version, URL to download it, what you do and how, may be snapshots.

With ODBC we have see many times, that different drivers implement THE SAME ODBC function in different ways. Or they have some side effects. So the only way in this case, in code write serape branch of code:

if( driver_XXX_verYYY )
{
}

Hi Ruslan,

Valentina Server 6 with the REST API seems interesting but I find Paradigma’s licensing confusing. If I developed a program that accessed Valentina Server 6 would each of my customers be able to have a free copy of the server too? How much do additional connections cost?

Hi Steve,

I think you are talking about application DEPLOYMENT to many customers. Right?

  1. For this scenario we have option VDN - Valentina Embedded Server. Its price is 599$.

For this price you get license file to deploy ANY number of your application to your customers to ANY OS (mac, win, linux). i.e. Royalty free.

Each such Valentina Embedded Server includes:

  • 10 connections to SQLite
  • 5 connections to Valentina DB
  • 5 connections to Valentina Reports.

Usually this is enough for 80-90% of application developers.
Does your application fits into this scenario? If not please describe your app requirements.

  1. Is this a good price? Well, you can check that cubeSQL asked: http://www.sqlabs.com/cubesql_pricing.php

Redistributable License of cubeSQL:
Up to 3 concurrent connections: $999/year.
Up to 5 concurrent connections: $3000/year.
Up to 10 concurrent connections: $4000/year.

So for 10 connections of SQLite DB, they asked 4000$, we do 599$.

Yet, our VServer includes Report Server and Valentina DB Server. Consider this as bonus. Actually as a big bonus :slight_smile: You can look around for prices of Report Server. They go usually $1500 - $15,000. Yet it is hard to find a report tool working on 3 OS.

P.S. In the article that points the first post from Lynn in this topic, you can find table with prices for Standalone Valentina Server and Valentina Embedded Server.

P.S2. Standalone VServer on cubeSQL price page they name as “Company Server”.
Company Server - CubeSQL - unlimited connections - $599
Valentina Server with unlimited connections to SQLite - $499

Hi Ruslan,

What if a customer requires more connections than the VDN server includes? Do they then just pay the price for the Valentina standalone server?

With regards to the REST interface your example shows this:

[code]Request:

{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}} {"vendor" : "Valentina", "database" : "db1", "Query" : "select * from t1"}[/code]

Is it possible to do something like this (note the addition of parameters)?:

[code]Request:

{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}} {"vendor" : "Valentina", "database" : "db1", "Query" : "select * from t1 where f1=? and f2=?","params":[123,"abc"]}[/code]

Thanks.

Hi Steve,

Q: More connections for VDN…

  1. Let me at first to show mySQL in this scenario (AFAIR). They specify price (can be not exact number, but it seems) $400-450 per server with unlimited connections number. Per server. They have only option with unlimited connection.

Sometimes people say – Valentina VDN license is confusing.

Let me answer – if you like mySQL way, we have the same:

  • VDN owner can buy from us UNLIMITED connections at price $399 per server.
  • Unlimited connections for SQLite we going to have at $250 it seems. I need to check with Lynn papers. See below
  1. But, we offer yet less expensive options, if you are okay with less connections. You can see exact number in our store:
    http://valentina-db.com/en/store/product/200-additional-connections-for-valentina-embedded-server
                        VDB           SQLite
 $99                   +10               +25
$125                   +25               +50 
$175                   +50               +100
$199                  +100             +250
$250                  +250             UNLIMITED
$399             UNLIMITED

So if some your customer can live with 50 connections, it will cost $125, instead $400.

P.S. please note, connection prices for v6.0 yet subject for our internal discussion.

P.S2 about your REST question. Can I ask you to ask it on Valentina forum? Or I will ask it our guys myself tomorrow.

Hi Steve,

Aha, you are asking about SQL binding params in the REST. Why you think this can be useful?

My guess is that this can be useful for Valentina DB, because we use pool of cached queries, so next REST command will find it. For SQLite this will not make advantage, because SQLite want prepare(). But I will ask develop of REST.

Do you see other advantages in such syntax? May be more easy to produce command?

Thanks for clarifying the pricing, Ruslan.

[quote=199802:@Ruslan Zasukhin]Hi Steve,

Aha, you are asking about SQL binding params in the REST. Why you think this can be useful?

My guess is that this can be useful for Valentina DB, because we use pool of cached queries, so next REST command will find it. For SQLite this will not make advantage, because SQLite want prepare(). But I will ask develop of REST.

Do you see other advantages in such syntax? May be more easy to produce command?[/quote]I understand for many DB’s this gives improved performance because the prepared query gets reused but even if that’s not the case I still prefer the syntax. You could even extend the idea further and have the option of an array of arrays for doing multiple inserts/updates which would also reduce network traffic, e.g.:

[code]Request:

{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}} {"vendor" : "Valentina", "database" : "db1", "Query" : "insert into t1 (f1, f2) values (?, ?) ","params":[[123,"abc"],[321,"cba"]]}[/code]

The Python fdb module for connecting to Firebird offers this kind of functionality (example from their docs):

[code]
import fdb

con = fdb.connect(dsn=’/temp/test.db’, user=‘sysdba’, password=‘masterkey’)
cur = con.cursor()

newLanguages = [
(‘Lisp’, 1958),
(‘Dylan’, 1995),
]

cur.executemany(“insert into languages (name, year_released) values (?, ?)”, newLanguages)

con.commit()[/code]

I will redirect this to our REST developer. It should be easy to implement anyway. Thank you for point. :slight_smile: