We have add into v4.3 support of default parameters for stored procedures.
CREATE PROCEDURE( IN param INT = 2 ) …
Category: Valentina SQL
‘OR REPLACE’ Warning. ALTER VIEW Added.
Most Valentina SQL commands of kind CREATE SOMETHING have clause ‘OR REPLACE’. You should understand that this clause forces DROP of existed object before new will be created. This means that all its sub-objects also will be deleted. To avoid this use ALTER command.
For 4.3 we have add ALTER VIEW command.
Valentina SQL get powerful FORMAT() function.
Ivan have add into Valentina engine new very powerful FORMAT() function for SQL.
It provides great ability to convert numbers and date/times into string format.
Please read detailed syntax in the Valentina WIKI:
http://valentina-db.com/dokuwiki/doku.php?id=valentina:vcomponents:vsql:reference:expr:funcs_string#format
The same algorithms will be added in nearest time to Valentina Reports.
Improvements for stored procedures and binded queries
It was a huge improvement to solve the problem with a “second run” in stored procedures and binded queries. Sometimes there was an incorrect behavior because of “over-prepared” nodes. There was a workaround with “EXECUTE” statement (which is actually developed to prepare any SQL statement and execute one from another SQL statement). So now this workaround could be omitted.
“LIMIT N” now can be used in additional places.
Was found a problem that in a stored procedure the assignment statement do not accept SELECT with LIMIT. This problem is fixed now. So you can have
procname
BEGIN
var = SELECT f1 FROM T WHERE … LIMIT 1
END
Hierarchical (Recursive) Queries in Valentina SQL
There are three new kind of queries specially designed for recursive (hierarchical) tables.
Such kind of tables are widely used. It could be human relationships, threaded forums and anything which might be described as tree-like data.
There are few common SQL ways to operate with such structures but all of them are too far from perfect.
Valentina introduces special kind of query which looks much more clear, natural and readable.
Syntax:
vext_recursive_table -- v4.1
: {ANCESTORS | BROTHERS | DESCENDANTS} OF vext_root_objects
[{TO|ON} LEVEL UINT] USING link_name [WITH {ROOT | ROOTS}]
vext_root_objects
: UINT
| variable_name
| ( search_condition )
Examples:
ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1 BROTHERS OF 4 ON LEVEL 2 USING l1 SELECT * FROM (ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1) WHERE f1 = 'Branch2'
See also:
* WIKI: Valentina SQL Reference: Hierarchical (Recursive) Queries for description of exact syntax of these commands and examples.
* WIKI: Article Hierarchical (Recursive) Queries in Valentina SQL
Using of “Instead-of” triggers
Such kind of trigger may be applied to views only. It could be used for insert, update and delete operations as usual.
Example:
CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t2 VALUES( 1 );
END ;
“trigger1” will be fired on attempt to insert record in the view “v1”.
Starting from v 4.1 you may use ‘NEW’ and ‘OLD’ descriptors within this kind of trigger.
Views are always read-only – so using the trigger is a nature way to overcome this restriction.
Example:
CREATE TABLE t1 (f1 LONG, f2 TEXT(1024));
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES( NEW.f1, NEW.f2 );
END ;
—
INSERT INTO v1 VALUES ( 1, ‘abc’);
SELECT * FROM v1;
————-
f1 f2
————-
1 abc
————-
RAISE now can accept variables
RAISE statement now can accept not only NUMER and STRING constants, but also variable of a stored procedure.
RAISE errNumber, errMsg
RAISE now can re-throw exception
Up to now the statement RAISE did have syntax as:
raise errNumber [, errMsg]
Now it is
raise [errNumber [, errMsg]]
Except syntax also is implemented additional logic:
raise without parameters means throw the same exception forward.
BEGIN
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(1);
EXCEPTION
WHEN 23503 THEN
PRINT 'Unique violation'
RAISE;
END;