FWD » Core Development » Database

The goal of this task is to track the porting of native UDF functions for Microsoft SQL Server.

I already did partial work on this and saved the result in branch 9456a, together with other fixes of that task. My assumption was that the pieces of code are very separated and even if different developers work on that branch the result is easily merged or split. The UDF s are strictly contained within the udf/mssql/ directory of FWD.

I see that in T-SQL, statements like RAISERROR('Dates before October 15, 1582 are not yet supported', 16, 1);. could be used only in PROCEDURE, and not in FUNCTION.

What if you call a PROCEDURE from a FUNCTION?

Constantin Asofiei wrote:

What if you call a PROCEDURE from a FUNCTION?

This could be done. And error is raised.

At the time I wrote CREATE FUNCTION dbo.modulo_ii (@p1 INT, @p2 INT) RETURNS BIGINT AS I was not able to test it. As you have seen, I tried using THROW and RAISERROR, but they do NOT work. The UDF currently looks like this:

CREATE FUNCTION dbo.modulo_ll (@p1 BIGINT, @p2 BIGINT) RETURNS BIGINT AS
BEGIN
   IF @p1 IS NULL OR @p2 IS NULL
      RETURN NULL;
   IF @p2 <= 0
      -- This does not work:  THROW 99009900, '** The second argument to MOD must be positive. % is invalid. (89)', 0;
      -- This either:         RAISERROR('** The second argument to MOD must be positive. % is invalid. (89)', -1, -1, 'FWD00');
      RETURN CAST(CONCAT('FWD00: ** The second argument to MOD must be positive. ' , @p2,  ' is invalid. (89)') AS INT);
   RETURN CAST(@p1 AS BIGINT) % CAST(@p2 AS BIGINT);
END;

Now, with improvements in 9456a, I returned to that UDF and wrote the following testcase:

find first book where book-id modulo author-id eq 8.

insert into book (recid, book_id, book_title, author_id) values (101010, 99, 'my Book', 0)

When this converted 4GL code is executed on SQL Server, FWD is notified by the following exception:

com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Conversion failed when converting the varchar value 'FWD00: ** The second argument to MOD must be positive. 0 is invalid. (89)' to data type int.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:276)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer$FetchBufferTokenHandler.onDone(SQLServerResultSet.java:5455)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:103)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:42)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5558)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1821)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1079)
    at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:685)
    at com.goldencode.p2j.persist.orm.SQLQuery.uniqueResult(SQLQuery.java:549)
    [...]

As noted in the Monday technical meet, revision 16004 of 9456 contains full support for recognising and parsing custom errors raised in UDF code. For example, the following code:

find first book where int64(book-id) modulo int64(author-id) eq 8.

** The second argument to MOD must be positive. 0 is invalid. (89)

Ovidiu, should I commit my udfs implementation on 9456a?

Committed on 9456a, rev. 16006.

Add most of the remaining udfs. There still some edge cases to handle, especially the functions like tostring (@var datatype, @format NVARCHAR(50)).

Ovidiu, do you know whether we could run queries with debugger in SQL Server Management Studio?

Eduard Soltan wrote:

Committed on 9456a, rev. 16006.

Thank you, I am grabbing the changes right now.

Add most of the remaining udfs. There still some edge cases to handle, especially the functions like tostring (@var datatype, @format NVARCHAR(50)).

Yes, these must also be implemented. Please have a look at the UDFs I wrote, some of them are not complete (lack the exception cases).

Ovidiu, do you know whether we could run queries with debugger in SQL Server Management Studio?

I do not know. I used it only to write the UDF, but debugging was difficult since you SQL Server prohibit printing intermediary values/messages. My solution was to make the UFD temporarily return strings and force returning the intermediary values. Not the best debugging experience.... :(
I heard/read that the VS would be useful, but I am not a fan of it.

Have you tested the implementations? including edge cases like null and other? For example, to test my implementations of lte UDFs I used something like:

select dbo.lte_ss('aB ', 'Ab'), dbo.lte_ss('aB ', ' Ab'), dbo.lte_ss(null, null), dbo.lte_ss(null, ' Ab'), dbo.lte_ss('x', null);
select dbo.lte_ll(123, 123), dbo.lte_ll(123, 456), dbo.lte_ll(null, null), dbo.lte_ll(null, 123), dbo.lte_ll(456, null);
select dbo.lte_ii(123, 123), dbo.lte_ii(123, 456), dbo.lte_ii(null, null), dbo.lte_ii(null, 123), dbo.lte_ii(456, null);
select dbo.lte_rr(123.5, 123.5), dbo.lte_rr(123.5, 456.5), dbo.lte_rr(null, null), dbo.lte_rr(null, 123.5), dbo.lte_rr(456.5, null);
select dbo.lte_bb(1, 1), dbo.lte_bb(1, 0), dbo.lte_bb(null, null), dbo.lte_bb(null, 1), dbo.lte_bb(0, null);
select dbo.lte_dd(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + 0), dbo.lte_dd(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP+10), 
       dbo.lte_dd(null, null), dbo.lte_dd(null, CURRENT_TIMESTAMP), dbo.lte_dd(CURRENT_TIMESTAMP, null);
select dbo.lte_tt(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + 0), dbo.lte_tt(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP+10), 
       dbo.lte_tt(null, null), dbo.lte_tt(null, CURRENT_TIMESTAMP), dbo.lte_tt(CURRENT_TIMESTAMP, null);

Changed udfs names and comments formats in rev. 16007. I confused the formats with mariadb udfs, and it is a bit different with that from SQL Server.

Eduard,

Do you have any pending changes to 9456a as part of this task?

The reason to ask is that I have some of my changes to sqlserver/udf.sql meant to fix the issues I encountered while installing the UDFs into SQL Server. This is not a small changeset so I would prefer to merge them after yours.

Ovidiu Maxiniuc wrote:

Eduard,

Do you have any pending changes to 9456a as part of this task?

For now no, I will check for missed functions and will add them after your changes.

Question, can the tests from testcases/udfs be adopted to SQL Server and properly tested?

Eduard Soltan wrote:

Question, can the tests from testcases/udfs be adopted to SQL Server and properly tested?

These tests are what Igor used to check the original implementation when he first wrote the native SQL UDFs. At some point (not necessarily now but it would help) we need to migrate these to ABLUnit.

Independently, Marian's team built tests for Built-in Function (Database) Tests and WHERE Clause Tests which may overlap to some degree. These tests may not have been run on FWD yet, but will have been run on OE at one point.

Adopted the sql tests for T-SQL and posted on testcases/udfs/t-sql.

Committed functions fixups on 9456a, rev. 16040.

Manage to set up Hotel Gui with connection to a SQL Server instance running on a virtual machine. Did a smoke test with the application, did not enconter any major issue either in the application or the logs.

Next step is to set up a customer application with the SQL Server.

I think there is a issue with some _meta dmos.

The following field in _meta.MetaDb dmo:

@Property(id = 6, propertyId = 7, name = "dbMisc12", column = "db_misc1_2", legacy = "_Db-misc1", format = ">>>>9", initialNull = true, width = 96, order = 8, position = 7, index = 2, extent = 8, original = "dbMisc1")
   public integer getDbMisc12();

In latest trunk propertyId field in annotation.Property is not present anymore.

Eduard Soltan wrote:

I think there is a issue with some _meta dmos.

The following field in _meta.MetaDb dmo:
[...]

In latest trunk propertyId field in annotation.Property is not present anymore.

You need a new conversion.

Branch 9456a was rebased after 7020a was merged in. The denormalisation of extent fields was replaced with expanded columns.

I do not know (most likely not yet) whether 9457c was also rebased. Therefore they are most likely not compatible.

Ovidiu Maxiniuc wrote:

I do not know (most likely not yet) whether 9457c was also rebased. Therefore they are most likely not compatible.

I rebased it locally to latest trunk, otherwise I could not apply the 9456a changes on it. And it seems that indeed a new conversion will be necessary.

Ovidiu Maxiniuc wrote:

I do not know (most likely not yet) whether 9457c was also rebased. Therefore they are most likely not compatible.

The conversion will be available tomorrow with, for 9457c rebase with trunk 16029.

I think the cause for this exception is the absence from build.properties of the definition for db.import_threads. I see it in the bzr project so it might have been commented out or deleted from your file. The default value is 4:

db.import_threads=4