0

I am currently tracking down a rare problem in our SQL generator and was able to narrow down the source of the problem. I created a sample with three testcases to show you the exact behaviour: 

QUery.Close;
//Query.SQL.Text := Format('select Options, JobID, StatusMessage from ServerJob where JobID = %d', [ServerJobID]); // #1
//Query.SQL.Text := Format('select JobID, Options, StatusMessage from ServerJob where JobID = %d', [ServerJobID]); // #2
//Query.SQL.Text := Format('select JobID, StatusMessage, Options from ServerJob where JobID = %d', [ServerJobID]); // #3
Query.Open;

try
  JobID := Query.FieldByName('JobID').AsInteger; // #1 raises exception
                                                 // #2 ok
                                                 // #3 ok

  StatusMsg := Query.FieldByName('StatusMessage').AsString; // #1 never reaches this point
                                                            // #2 raises exception
                                                            // #3 ok
except
  on Exp: Exception do
  begin
    ExpMsg := Exp.Message; // #1 Fehler bei einem aus mehreren Schritten bestehenden Vorgang. Prüfen Sie die einzelnen Statuswerte.
                           // #2 Fehler bei einem aus mehreren Schritten bestehenden Vorgang. Prüfen Sie die einzelnen Statuswerte.
                           // #3 never reaches this point

    Query.Connection.Errors.Count; // #1 1
                                   // #2 1
                                   // #3 never reaches this point

    Query.Connection.Errors[0].Description; // #1 Expression illegal in evaluator
                                            // #2 Expression illegal in evaluator
                                            // #3 never reaches this point
    end;
  end;

The field "Options" is of type XML. So my observation is, that if I reference a field having xml as datatype, all subsequent fields cannot be referenced anymore using TDataset.FieldByName without raising an exception.

Is this some known bug??

P.S. This is the schema of the concerning table:

CREATE TABLE [dbo].[ServerJob](
    [JobID] [int] IDENTITY(1,1) NOT NULL,
    [PersonID] [int] NULL,
    [Name] [varchar](64) NOT NULL,
    [Description] [varchar](max) NULL,
    [StartTime] [datetime] NOT NULL,
    [Cpu] [decimal](5, 2) NULL,
    [Ram] [int] NULL,
    [Status] [varchar](32) NULL,
    [VitalSign] [datetime] NULL,
    [Options] [xml] NULL,
    [StatusMessage] [varchar](max) NULL,
 CONSTRAINT [PK_JobID] PRIMARY KEY CLUSTERED 
(
    [JobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I'm using Delphi 10 Seattle and SQL server 2014.

Johan
  • 74,508
  • 24
  • 191
  • 319
Silverdust
  • 1,503
  • 14
  • 26
  • Maybe try casting Options as a varchar? – John Easley May 01 '16 at 14:34
  • Your q seems to be lacking the basic information needed for readers to try to reproduce the problem. Can you show the DDL for your ServerJob table, view, stored proc or whatever it is? – MartynA May 01 '16 at 14:48
  • casting option as varchar by cast(option as varchar(max)) solves this particular problem but I need to understand whats going wrong here in order to gain a deeper understanding helping to fix such problems more efficiently in the future... – Silverdust May 01 '16 at 14:56
  • Have you tried googling your exception? There seem to be quite a few issues in this regard... – John Easley May 01 '16 at 14:59
  • I have, even though I could of course have overseen something. What I have read using google were plenty of questions concerning the meaning of the exception text. This is however not the problem I am describing here, as I know what the exception text is saying (actually that its required to iterate through the error list of the ado connection), what I am actually doing... – Silverdust May 01 '16 at 15:02
  • Its probably worth mentioning that the value of options is null. So any errors raising from invalid xml characters can be excluded – Silverdust May 01 '16 at 15:03
  • Good Question: its Delphi 10 Seattle, and Microsoft SQL Server 2014. – Silverdust May 01 '16 at 15:28
  • I'll check it out immediately and will report back :-) – Silverdust May 01 '16 at 15:40
  • I check both, changing the fieldname and the datatype. The problem definitely only appears if the datatype of the field is of type xml. – Silverdust May 01 '16 at 16:01
  • @Johan - this solved the problem - many thanks!! – Silverdust May 01 '16 at 16:50
  • I think you can vote to close your own question with the duplicate. And you're welcome. – Johan May 01 '16 at 16:50

0 Answers0