mORMot Open Source

OK. Here is quick and dirty prototype: https://github.com/dominikcz/custom-serialization-idea
I've tried to make it as close to my original code as possible, so there are some parts that have nothing to do with simple types we discuss here (just ignore them). However in few places I had to remove some functions and substitute them with default Delphi counterparts (to reduce dependency at the cost of performance).

Please note that only parts of writer's code is implemented by registry. Ideally all code from WriteValue() function should be implemented like that. And last but not least, there are no readers implemented.

Tell me what do you think about it

Yes it would partly solve the problem, but inheritance is almost never a good choice, at least not in long term. And you won't achieve full DRY principle with hundreds of models, it will be maybe a little less wet

That's why I prefer to encapsulate serialization/deserialization of simple types and this way aid serialization of models that contain them. It's may be a strategy pattern or template method, but either way it's more about composition than inheritance.

dc

Good but what about classes? Will it work with classes too?

I have some "data models" in my application, some of them are records but most are classes. From my perspective it's not that important, let's assume I can't change it and would like to be able to work with both.

I need to have much more control of serialization/deserialization than just looking at published. That's why I use RegisterCustomSerializer() to begin with, so I can alert the way serialization and deserialization works. My point is that having only RegisterCustomSerializer() leads to ugly code, which could be easily made better by using some register for simple types. From this perspective using TSynPersistent won't help either.

By simple data types I mean all possible native Delphi data types but records, objects and classes (i.e. integer, currency and so on). To that group I also add custom types like TDecimal2 = Currency from my example code.

Maybe a use case will show it better:

Let's say I need to serialize numbers differently based on precision. Numbers with precision up to 2 fields are supposed to always serialize with two decimal places, and numbers with precision up to 3 always with 3 decimal places.

Then in an application I would just declare two new types TDecimal2 and TDecimal3, booth as Currency, and use a custom reader and writer for serialization and deserialization. It separates my business needs from implementation details and even gives me opportunity to use parts of my code in different places. It can work with JSON serialization but also XML as well.

dc

Thank you for merging

JWT are just as much vulnerable to MITM attacks as any other authentication scheme and OAuth has nothing to do with it. However solutions do exists, like using HTTPS only cookies (HttpOnly; Secure). You should also consider prevention of CSRF attacks and replay attacks. For CSRF you may consider using another cookie (domain cookie, accessible from JavaScript and send it alongside request). For replay attacks prevention you can use exp and iat claims of JWT. There is also jti claim (unique JWT ID) which can be used for example to blacklist used tokens.

If this information is too condensed I can try to elaborate a little

OK, I've made some tests with different ways of handling output parameters in mORMot and it looks like mORMot used to work better in 2013 :(

First of all here is my test procedure:

create procedure test_output_params @param1 varchar(30) OUTPUT, @param2 int OUTPUT, @param3 varchar(30) OUTPUT
as
begin
  SET NOCOUNT ON
  set @param1 = 'echo: '+@param1
  set @param2 = 100 + @param2
  set @param3 = 'test param3'
end

It accepts 3 parameters @p1, @p2 and @p3. Values of @p1 and @p2 are changed depending on input, while value of parameter@p3 is only set inside procedure.

You can test it with SQL statement like this one:

declare
  @p1 varchar(30),
  @p3 varchar(30),
  @p2 int;

set @p1 = 'test'
set @p2 = 100

exec test_output_params @p1 OUTPUT, @p2 OUTPUT, @p3 OUTPUT

select @p1 as p1, @p2 as p2, @p3 as p3

Query above returns:

p1                                        p2             p3
------------------------------ ----------- ------------------------------
echo: test                             200            test param3

Ok, now back to mORMot. Here is complete test code:

program stored_proc_output_params;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils, SynDB, SynOleDB;

var
  stmt: TSQLDBStatement;
  param1, param3: string;
  param2: integer;
  outParam: Variant;
  vConnProp: TOleDbConnectionProperties;
  conn: TSQLDBConnection;

begin
  vConnProp := TOleDBMSSQLConnectionProperties.Create('localhost', 'master', '', '');
  conn := vConnProp.ThreadSafeConnection;
  conn.Connect;
  stmt := conn.NewStatement;
  stmt.Prepare('exec test_output_params ? output, ? output, ? output', true);

  param1:= 'test 123';
  param2 := 123;

  stmt.BindTextS(1, param1, paramOut);
  stmt.Bind(2, param2, paramOut);
  stmt.BindTextS(3, ' ', paramOut);
  stmt.ExecutePrepared;

  Writeln('expected stored procedure output:');
  Writeln(format('    param1: "echo: %s"', [param1]));
  Writeln(format('    param2: %d', [param2 + 100]));
  Writeln('    param3: "test param3"');
  Writeln('actual stored procedure output:');

  stmt.ParamToVariant(1, outParam, false);
  param1 := outParam;
  stmt.ParamToVariant(2, outParam, false);
  param2 := outParam;
  stmt.ParamToVariant(3, outParam, false);
  param3 := outParam;

  Writeln(format('    param1: "%s"', [param1]));
  Writeln(format('    param2: %d', [param2]));
  Writeln(format('    param3: "%s"', [param3]));
  stmt.Free;

  Writeln('press [ENTER] to exit');
  conn.Free;
  Readln;
end.

As in SQL example expected output would be:

expected stored procedure output:
    param1: "echo: test 123"
    param2: 223
    param3: "test param3"

instead in current version of mORMot we get:

actual stored procedure output:
    param1: "test 123"
    param2: 123
    param3: "t"

It looks like param3 here is the problem and4sh wrote earlier. If you bind ' ' (space) you only get first letter as output. In this example it's only "t" instead of "test param3". If you bind a longer string you get longer result.

In old version of mORMmot (1f0ccd0924 from 2013) there were same problems for param1 and param2 but param3 used to work as expected. Here is an output from old version:

expected stored procedure output:
    param1: "echo: test 123"
    param2: 223
    param3: "test param3"
actual stored procedure output:
    param1: "test 123"
    param2: 123
    param3: "test param3"

Uff... hope it clarifies this topic a bit. Sorry for making it that long.

dc

Or... maybe it's a bug/missing feature of handling output params correctly in mORMot SynOleDb? I've encountered something very similar in post from 2013-03-16 18:59:40

dc

Can I count on a response? This patch is important for me to stay in sync with current mORMot sources.

Regards,
dc

I have found a way to reproduce this error and thought I leave a comment here for those struggling with it. In our case reason for this error was using multiple statements in one, like this:

  stmt := dbConn.NewStatementPrepared('set ROWCOUNT 1 '
    +' select * from yourtable order by id desc '
    +' set ROWCOUNT 0 ', false, true);
  stmt.ExecutePrepared;

First call to BeginTransaction after this query always generates above error. In this case we could easily change query to use "select top 1 * from yourtable order by id desc" instead.

Hope this saves someone's time.

dc

Thank you, it's really good news for me

I understand your points about KISS strategy and agree with them, so maybe it's better to implement nested transactions in user code. On the other hand naming transactions doesn't seem to be in conflict with KISS principles and it could be easily implemented in TSQLDBConnection.StartTransaction.

As you said

As Microsoft states in their roadmap from January 2013 (http://msdn.microsoft.com/en-us/library/ms810810.aspx):

- OleDB will be supported only up to SQLServer 2012 (but corporations are usually slowly adopting, so I suspect that version 2012 will be still in use for next 5-10 years, as I know some big companies that are slowly moving from version 2005 to 2008 nowadays)
- ODBC will be supported "better" from now on - that's good because now it's not as good as OleDB

From user point of view it would be probably better to switch to ODBC for future versions of code, however I would expect OleDB and ODBC in mORMot to behave identically, so when I change connection from OleDB to ODBC for the same database it would just work. What I mean here is inconsistent behavior of:
- datetime 
- bigint
- stored procedure parameters and result codes http://synopse.info/forum/viewtopic.php?pid=7056#p7056
and so on...

dc

Yeah   I know... in my defense I don't release statements in both approaches and it gives errors only when using dbConnection.Execute (or as I suppose NewStatementPrepared).

dc

Hi, I've finally found some time to play with mORMot and stored procedures in SQL Server. Now I can confirm that ODBC drivers work correctly and are not affected by SET NOCOUNT. Unfortunately there is another issue I found, have no idea how accessing output result from stored procedure should be done. Here is example procedure I used for my tests:

create procedure test_params 
     @param1 smallint, 
     @param2 int OUTPUT
as
begin
	set @param2 = @param1 + 1000;
	return(18)
end

as you can see it takes 2 parameters, param1 as input and param2 as output. It also returns value 18 as result. You can check it by running this script in SQL Server Management Studio:

declare 
	@rc int,
	@param2 int;

exec @rc = test_params 123, @param2 output
select @rc as result, @param2 as param2

when using dbexpress one would use something like this code:

ds.CommandType:= ctStoredProc;
ds.CommandText:= 'test_params';
ds.Params[1].AsInteger:= 123;
ds.ExecSQL;

rc := ds.Params[0].AsInteger;
param2 := ds.Params[2].AsInteger;

to acces both params and return value, but how do you do it using mORMot?
I've tried this way:

  stmt.Prepare('exec ? = test_params ?, ?', false);
  stmt.Bind(1, 0, paramOut);
  stmt.Bind(2, 123, paramIn);
  stmt.Bind(3, 0, paramOut);
  stmt.ExecutePrepared;
  stmt.ParamToVariant(1, rc, false);
  stmt.ParamToVariant(2, param1, false);
  stmt.ParamToVariant(3, param2, true);

It works when I use TOleDBODBCSQLConnectionProperties (rc = 18, param1 = 123, param2 = 1123). However when i switch to TOleDBMSSQLConnectionProperties i get rc =18 and param1 = 123, but param2 = 0

Is it a bug in OleDB drivers or am I doing it wrong?
And another question: do you have any plans to use IMultipleResults with mORMot?

regards
dc

Hi,

I did some tests and it looks like there really is a problem with mORMot transactions in MS SQL Server.

As far as stored procedures are considered there is a problem too, however it's not the temporary table problem.
Thing is that SQL Server sends (as a part of result set) messages with number of rows affected by each statement done in stored procedure. This is default behavior, and can be changed with "SET NOCOUNT ON" directive. In other words, if your stored procedure does some other operations than just using select statements (and it usually does much more) it will not show resultset in mORMot (ISQLDBRows.Step returns false). There is also another problem (missing feature?) with SynOleDB.TOleDbConnectionProperties.Execute, what if you get multiple result sets? How do you handle them?

Here is a simple script to show those problems:

create database dev;
go
use dev;

create table dummy_table(
	id	integer not null identity,
	col1	integer
)
go

-- this procedure returns 3 resultsets
create procedure test_resultsets(@withNoCount smallint = 0)
as
begin
	if IsNull(@withNoCount, 0) = 1
		SET NOCOUNT ON
	insert into dummy_table(col1) values(1);
	select 'resultset1: aqq 1.1' as col1, 1100 as col2
	union
	select 'resultset1: aqq 1.2' as col1, 1200 as col2;
	
	select 'resultset2: aqq 2' as col1, 2345 as col2;
	
	select 'resultset3, from dummy_table:' as col1, id from dummy_table;
end

To test this you can now open SQL Server Management Studio and connect to your local server instance. After that, press Ctrl+N (it will open "New Query" tab), paste above code and click "execute" button or press F5. It creates new database "dev", table "dummy_table" and stored procedure "test_resultsets". You can now open new query window and type "exec test_resultsets". When you press F5 you see 3 resultsets as here:
When you now switch to "Messages" tab you see messages that SQL Server send to client during execute precess (first message is from insert statement). Here is a screenshot:

Of course second and third result sets are not necessary to show that mORMot handles messages incorrectly (it's enough to execute insert in stored procedure and then select statement), they are here just to show another problem: handling  multiple result sets.

Here is project to test how mORMot handles it:

program mORMot_StoredProcTest;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SynOleDB,
  SynCommons,
  SynDB,
  SysUtils;

var
  dbConnection: TOleDbConnectionProperties;
  i: integer;

procedure printResultSet(rows: ISQLDBRows);
var
  s, ss, pad: string;
  i: integer;
begin
  pad := ' ';
  s := StringOfChar(' ', 5) + pad;
  ss := StringOfChar('-', 5) + pad;
  for i := 0 to rows.ColumnCount - 1 do
  begin
    s := s + Format('%30s', [trim(rows.ColumnName(i))]) + pad ;
    ss:= ss + StringOfChar('-', 30) + pad ;
  end;
  writeln(s);
  writeln(ss);
  i := 0;
  while rows.Step do
  begin
    inc(i);
    s:= format('%5d ', [i]);
    for i := 0 to rows.ColumnCount - 1 do
      s := s + Format('%30s', [Copy(rows.ColumnString(i), 1, 30)]) + pad ;
    writeln(s);
  end;
  writeln(ss);
  writeln(Format('%d row(s) returned.', [i]));
  writeln;
end;

begin
  // connection made using windows authentication to database "dev"
  // if using SQLServer authentication supply user and passsword too
  dbConnection := TOleDBMSSQLConnectionProperties.Create('localhost\sqlexpress', 'dev', '', '');
  writeln('default behavior ("exec test_resultsets"):');
  writeln;
  printResultSet(dbConnection.Execute('exec test_resultsets ?', [0]));
  writeln('NOCOUNT=ON ("exec test_resultsets 1"):');
  writeln;
  printResultSet(dbConnection.Execute('exec test_resultsets ?', [1]));
  readln;
end.

It looks like mORMot doesn't handle DONE_IN_PROC messages sent by SQL Server correctly (it should ignore them and wait for real result if there is any). You can check documentation here: http://msdn.microsoft.com/en-us/library/ms189837.aspx

Hope this post helps to clarify things a bit.

Regards,
dc

Yes, thank you your patch fixes both issues (rowid and type), however there is one more problem; when I ty to execute something like this:

DB.Delete(TSQLMyClass, 'PLU < ?', [20000]);

i get error "OLEDB Error 80040E14 -  (line 1): Incorrect syntax near ':'." and query in ExecuteDirect method is: "delete from dbo.Assortment where PLU < :(20000):"

dc

At the select level it is adding RowId column to query. Here is SQLTableSimpleFields from debugger (table name was changed to Assortment, but it's irrelevant):

(
  ('PLU,Name,ArtNr,PurchDate,ExpiryDate,Price', 'Assortment.PLU,Assortment.Name,Assortment.ArtNr,Assortment.PurchDate,Assortment.ExpiryDate,Assortment.Price'),
  ('RowID,PLU,Name,ArtNr,PurchDate,ExpiryDate,Price', 'Assortment.RowID,Assortment.PLU,Assortment.Name,Assortment.ArtNr,Assortment.PurchDate,Assortment.ExpiryDate,Assortment.Price')
)

One more thing: there are proper values in Decoder.FieldValues, however Types has only ftUnknown values (then exception "ExecuteFromJSON: Invalid Types[%d]=%d" is raised.