Cast unknown data type columns as text when generating a multi column…

… filter from user input. See http://www.heidisql.com/forum.php?t=22562
HeidiSQL · Nov 1, 2016 · 7bd29f0 · 7bd29f0
1 parent aa373aa
commit 7bd29f0
Show file tree

Hide file tree

Showing 2 changed files with 15 additions and 1 deletion.
diff --git a/source/dbconnection.pas b/source/dbconnection.pas
@@ -106,6 +106,7 @@   TTableColumn = class(TObject)
       destructor Destroy; override;
       function SQLCode(OverrideCollation: String=''): String;
       function ValueList: TStringList;
+      function CastAsText: String;
       property Status: TEditingStatus read FStatus write SetStatus;
       property Connection: TDBConnection read FConnection;
   end;
@@ -7136,6 +7137,19 @@ function TTableColumn.ValueList: TStringList;
 end;
 
 
+function TTableColumn.CastAsText: String;
+begin
+  // Cast data types which are incompatible to string functions to text columns
+  Result := FConnection.QuoteIdent(Name);
+  if DataType.Index = dtUnknown then
+    case FConnection.Parameters.NetTypeGroup of
+      ngMySQL: Result := 'CAST('+Result+' AS CHAR)';
+      ngMSSQL: Result := 'CAST('+Result+' AS NVARCHAR('+IntToStr(SIZE_MB)+'))';
+      ngPgSQL: Result := Result + '::text';
+    end;
+end;
+
+
 
 { *** TTableKey }
 

diff --git a/source/main.pas b/source/main.pas
@@ -8363,7 +8363,7 @@ procedure TMainForm.editFilterSearchChange(Sender: TObject);
     Conditions := TStringList.Create;
     for i:=0 to SelectedTableColumns.Count-1 do begin
       // The normal case: do a LIKE comparison
-      Condition := Conn.QuoteIdent(SelectedTableColumns[i].Name) + ' LIKE ''%' + Conn.EscapeString(ed.Text, True, False)+'%''';
+      Condition := SelectedTableColumns[i].CastAsText + ' LIKE ''%' + Conn.EscapeString(ed.Text, True, False)+'%''';
       if not SelectedTableColumns[i].DataType.ValueMustMatch.IsEmpty then begin
         // Use an exact comparison for some PostgreSQL data types to overcome SQL errors, e.g. UUID, INT etc.
         // Also, prevent other errors by matching the value against a certain regular expression.