Valentina SQL FAQ

Q: What is the syntax for string searches in Valentina SQL?

Following toSQL 92 standard you can use the following syntax for string searches:

Q: How I can sort on column with aggregate function?

You should specify in the ORDER BY statement the numeric index of that column in the SELECT statement.
SELECT a, COUNT(a)
FROM T
GROUP BY a
ORDER BY 2

Q: How can I get the RecID value of a newly added row if I use VCursor.AddRecord() command?

You need explictly SELECT RecID field and after AddRecord() you can use its value. It contains RecID of just added record.

Q: What is the difference between Table Methods and Stored Procedures/Functions?

Well, Table Methods, Stored Procedures and User Defined Functions (UDFs) are quite different things:

1) History

Methods was in Valentina from the first year of its life, Procedures we have made only on 9th year at Feb 2007, UDFs at June 2007.

2) Portability

Table Methods is not common feature for RDBMS, while Procedures and UDFs are.

3) Power

Table Method is a SIMPLE EXPRESSION (yes based on SQL rules) that calculates SCALAR value, using VALUES OF the CURRENT record of the Table.

Procedures can contain practically any SQL statement: create new tables, drop them do SELECTs, INSERTs UPDATEs.

4) Invocation

Table Methods can be used by name, at any place where FIELD can be used:
SELECT fld, Mtd
FROM T1
WHERE fld = 1 AND Mtd = 2
ORDER BY fld, Mtd
Procedures can be called using CALL method of SQL.

UDFs can be used at any place where built-in functions can be used inside of some expression. UDFs always have syntax as FOO().

5) Name Scope

Table Methods share the same namespace with Table Fields in the table scope.

Stored Procedures have own namespace in the database scope.

User Defined Functions share namespace with built-in functions of Valentina engine (e.g. sin(), concat(), … )

6) Indexing

Table Methods can be indexed as well as regular fields.

Stored Procedures cannot be indexed.

UDFs can be part of Table Methods expression.