View Issue Details

IDProjectCategoryView StatusLast Update
0037646FPCDatabasepublic2020-09-03 20:24
ReporterLuiz Americo Assigned ToMichael Van Canneyt  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Platformi386OSWindows 
Product Version3.3.1 
Fixed in Version3.3.1 
Summary0037646: [patch] Implement handling Firebird SQL_NULL data type
DescriptionFirebird introduced, in version 2.5, the SQL_NULL data type, when a param is checked if is NULL.

Its useful to create conditional sql expressions (see complete explanation of usage in 2.5 language update reference: https://firebirdsql.org/refdocs/langrefupd25-sqlnull.html and main language reference: https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-datatypes-special-sqlnull)

The patch just detects the SQL_NULL type and avoid allocating SQLData memory and throwing an error, since the other requirements (setting sqlind to 0 or -1 and SQLData to nil) are already done elsewhere.

I added a test. Enabled for Sqlite and Firebird but should work with other databases. I just did not have other databases to test.
TagsNo tags attached.
Fixed in Revision46756
FPCOldBugId
FPCTarget3.2.2
Attached Files

Activities

Luiz Americo

2020-08-27 04:10

developer  

ibconnection-null-type.diff (2,506 bytes)   
diff --git packages/fcl-db/src/sqldb/interbase/ibconnection.pp packages/fcl-db/src/sqldb/interbase/ibconnection.pp
index 7926f2b4c..6996fd467 100644
--- packages/fcl-db/src/sqldb/interbase/ibconnection.pp
+++ packages/fcl-db/src/sqldb/interbase/ibconnection.pp
@@ -164,6 +164,7 @@ uses
 const
   SQL_BOOLEAN_INTERBASE = 590;
   SQL_BOOLEAN_FIREBIRD = 32764;
+  SQL_NULL = 32767;
   INVALID_DATA = -1;
 
 procedure TIBConnection.CheckError(ProcName : string; Status : PISC_STATUS);
@@ -834,7 +835,7 @@ begin
         begin
         if ((SQLType and not 1) = SQL_VARYING) then
           SQLData := AllocMem(in_SQLDA^.SQLVar[x].SQLLen+2)
-        else
+        else if SQLType <> SQL_NULL then
           SQLData := AllocMem(in_SQLDA^.SQLVar[x].SQLLen);
         // Always force the creation of slqind for parameters. It could be
         // that a database trigger takes care of inserting null values, so
@@ -1213,7 +1214,8 @@ begin
         SQL_BOOLEAN_FIREBIRD:
           PByte(VSQLVar^.SQLData)^ := Byte(AParam.AsBoolean);
       else
-        DatabaseErrorFmt(SUnsupportedParameter,[FieldTypeNames[AParam.DataType]],self);
+        if (VSQLVar^.sqltype <> SQL_NULL) then
+          DatabaseErrorFmt(SUnsupportedParameter,[FieldTypeNames[AParam.DataType]],self);
       end {case}
       end;
     end;
diff --git packages/fcl-db/tests/testsqldb.pas packages/fcl-db/tests/testsqldb.pas
index ec2651294..b803bce4a 100644
--- packages/fcl-db/tests/testsqldb.pas
+++ packages/fcl-db/tests/testsqldb.pas
@@ -62,6 +62,7 @@ type
     procedure TestMacros;
     Procedure TestPrepareCount;
     Procedure TestPrepareCount2;
+    Procedure TestNullTypeParam;
   end;
 
   { TTestTSQLConnection }
@@ -839,6 +840,30 @@ begin
   end;
 end;
 
+procedure TTestTSQLQuery.TestNullTypeParam;
+begin
+  if not (SQLServerType in [ssSQLite, ssFirebird]) then
+    Ignore(STestNotApplicable);
+  CreateAndFillIDField;
+  try
+    With SQLDBConnector.Query do
+      begin
+      UsePrimaryKeyAsKey:=False; // Disable server index defs etc
+      SQL.Text:='Select ID from FPDEV2 where (:ID IS NULL or ID = :ID)';
+      Open;
+      AssertEquals('Correct record count param NULL',10,RecordCount);
+      Close;
+      ParamByname('ID').AsInteger:=1;
+      Open;
+      AssertEquals('Correct record count param 1',1,RecordCount);
+      AssertEquals('Correct field value: ',1,Fields[0].AsInteger);
+      Close;
+      end;
+  finally
+    SQLDBConnector.Connection.OnLog:=Nil;
+  end;
+end;
+
 
 { TTestTSQLConnection }
 
ibconnection-null-type.diff (2,506 bytes)   

Michael Van Canneyt

2020-09-03 11:41

administrator   ~0125341

Checked and applied, thank you very much for this patch!

Luiz Americo

2020-09-03 20:24

developer   ~0125346

Thanks for looking at it

Issue History

Date Modified Username Field Change
2020-08-27 04:10 Luiz Americo New Issue
2020-08-27 04:10 Luiz Americo File Added: ibconnection-null-type.diff
2020-08-27 09:06 Michael Van Canneyt Assigned To => Michael Van Canneyt
2020-08-27 09:06 Michael Van Canneyt Status new => assigned
2020-09-03 11:41 Michael Van Canneyt Status assigned => resolved
2020-09-03 11:41 Michael Van Canneyt Resolution open => fixed
2020-09-03 11:41 Michael Van Canneyt Fixed in Version => 3.3.1
2020-09-03 11:41 Michael Van Canneyt Fixed in Revision => 46756
2020-09-03 11:41 Michael Van Canneyt FPCTarget => 3.2.2
2020-09-03 11:41 Michael Van Canneyt Note Added: 0125341
2020-09-03 20:24 Luiz Americo Note Added: 0125346
2020-09-03 20:24 Luiz Americo Status resolved => closed