Results from Stored Procedure (MS SQL) Returned as Base64?

cheemeng · 2014-08-22 09:50:16

Hi Arnaud,

I have a stored procedure which returns an XML fragment due to the need to encapsulate a 1-to-Many result as one of the fields:

SELECT	
  AccountCode, 
  CompanyName, 
  (
    SELECT Address1, Address2, PostCode
    FROM vwAddress vad
    WHERE vad.AccountID = va.AccountID
    FOR XML PATH ('Address'), ROOT('Addresses'), TYPE
  )
FROM VwAccount va
WHERE ClientID = @ClientID
FOR XML PATH ('Account')

And will generate something like

<Account><AccountCode>621</AccountCode><CompanyName>Acme Inc</CompanyName><Addresses><Address><Address1>21 Jump St</Address1><Address2 /><PostCode>214012</PostCode></Address></Addresses></Account>

Here is the code I use to test the execution of the Stored Proc:

var
  Props: TSQLDBConnectionProperties;
  Data: ISQLDBRows;
  XML: RawUTF8;
begin
  Props := TOleDBMSSQLConnectionProperties.Create(edtServer.Text, edtDatabase.Text, edtUsername.Text, edtPassword.Text);
  Data := Props.Execute('Exec GetAccount ?', ['2928-12-34']);

  while Data.Step() do
  begin
    XML := Data.ColumnString(0);
//    Base64MagicDecode(XML);  <--- this does not work either, 
    Memo1.Lines.Add(XML);
  end;
  Props.Free;
end;

I tried using Base64MagicDecode because the dump starts with

ï¿°RAdBAGMAYwBvAHUAbgB0AEQLQQBjAGMAbwB1AG

but it returns an empty string.

Did I miss something here?

Thank you!

Last edited by cheemeng (2014-08-22 09:51:14)

ab · 2014-08-22 10:44:34

What is the Data.ColumnType(0) value?
I suppose it is ftBlob in your case.

Just try Data.ColumnBlob(0) instead, then use RawUTF8ToString() to convert it to an XML string.
If RawUTF8ToString(Data.ColumnBlob(0)) is not correct, try

 RawUnicodeToString(Data.ColumnBlob(0))

: I suppose the content is returned as an UTF-16 blob.

Note that Data.ColumnString() returns a string, not a RawUTF8. To get a RawUTF8, uses Data.ColumnUTF8(0).

cheemeng · 2014-08-22 11:34:59

Thanks for the reply!

Ord(Data.ColumnType(0)) returns 7, which maps to ftBlob, I believe.

There is no RawUTF8ToString function, I tried UTF8ToString, it output some binary characters.

RawUnicodeToString works partially, but 20% of the text got converted into some weird Asian characters

ab · 2014-08-22 12:23:04

Could you not just modify your stored procedure to type-cast the returned column to be identified as an NVARCHAR / text, and not a XML column?
In our classes, this XML proprietary column type is mapped into ftBlob, which is not native.

cheemeng · 2014-08-22 12:26:06

In short, nope

It was already a miracle that the client agreed to let us take a peek at the source code for their stored proc :-/

ab · 2014-08-22 13:06:48

Could you try with our ODBC unit?

Otherwise, did you try what is written in http://stackoverflow.com/a/19140615/458259 ?
I mean, using TOleDBMSSQL2012ConnectionProperties class to use SQLNCLI11 provider, or/and DataTypeCompatibility=0 or DataTypeCompatibility=80?

cheemeng · 2014-08-22 14:17:54

Hi Arnaud,

Yes, I did try with the SQLNCLI11 provider. Also tried both of the DataTypeCompatibility values, neither work.

With ODBC, the statement executed, but .Step returns False?

ab · 2014-08-22 14:21:22

What is the column type in TOleDBStatement.BindColumns()?
I mean, what does it return within nfo^.wType?

cheemeng · 2014-08-22 14:47:09

It contains 128, which is DBTYPE_BYTES ... I guess that's why it is treated as BLOB?

ab · 2014-08-22 14:51:07

Indeed

And with the other compatibility setting?

cheemeng · 2014-08-22 15:07:11

Same result for both compatibility settings, and for both SQLNCLI11 and SQLNCLI10 drivers

I have a feeling I have to turn down this project...? Lol

cheemeng · 2014-08-22 15:12:49

Of course, if I do this

SELECT 
(SELECT	
  AccountCode, 
  CompanyName, 
  (
    SELECT Address1, Address2, PostCode
    FROM vwAddress vad
    WHERE vad.AccountID = va.AccountID
    FOR XML PATH ('Address'), ROOT('Addresses'), TYPE
  )
FROM VwAccount va
WHERE ClientID = @ClientID
FOR XML PATH ('Account')
)

then it works beautifully. But alas, I am not allowed to amend the code for their stored proc

ab · 2014-08-22 19:24:31

What we could do is to had a method to force a column data types before they are actually retrieved.
I hope that if we ask for the result to be returned as NVARCHAR(max), it will be returned as UTF-16 text, just as we expect...

May be you could use the debugger, stop in TOleDBStatement.BindColumns() when nfo^.wType=128, then force Col^.ColumnType to be ftUtf8 instead of ftBlob for this column, overriding the type computed by OleDBColumnToFieldType()...
You should also monitor nfo^.ulColumnSize to see what is the size of this column: is it in bytes, in widechars?
If this works, I will add a method, at TOleDBStatement level, to force a result column to have a given type...

cheemeng · 2014-08-23 12:43:59

Hi Arnaud,

As per my previous post, if I 'force' it as a column via sub-SELECT, then yes, it returns correctly as a string column using ColumnString or ColumnUTF8.

Otherwise, if I force the ColumnType to ftUTF8 in BindColumns, it will return the hex equivalent of the binary blob. The binary blob is not a real UTF-16, I suppose? Because if I force a conversion to UnicodeString using RawUnicodeToString(Data.ColumnBlob(0)), I get a mix of weird Asian characters especially where the angle brackets ("<" and ">") are.

ab · 2014-08-23 15:21:28

I found the following type:

  DBTYPE_XML = $0000008D; // introduced in SQL 2005

But it sounds like if the provider does not return this type, but DBTYPE_BYTES...
Weird... DataTypeCompatibility=80 was suppose to enable this type.

AFAIR the binary XML blob should start with a BOM, which may be ignored.
Could you post here the blob content in hexa?

Did you try this SQL-level trick detailed at http://stackoverflow.com/a/4894179/458259 ?
Take a look at the limitation detailed in http://www.sommarskog.se/share_data.html#OPENQUERY - but it may be a workaround...

cheemeng · 2014-08-26 16:30:58

Hi Arnaud,

Sorry for the late reply. There are no email notifications to the OP when a post is replied to

1. In any case, just to confirm whether my code is correct, I directly modified TOleDBMSSQL2012ConnectionProperties just to test things out:

procedure TOleDBMSSQL2012ConnectionProperties.SetInternalProperties;
begin
  if OSVersion>wVista then
    fProviderName := 'SQLNCLI11';
  fConnectionString := fConnectionString + 'DataTypeCompatibility=80;';
  inherited SetInternalProperties;
end;

This did not work. The column is still treated as an ftBlob. Tried the same with the 2008 driver. Client is using MSSQL 2008 by the way.

2. As for your second suggestion, I would like to avoid OpenQuery if possible (I'm worried about the part about data being passed twice... my resultset can sometimes be HUGE).

3. And here's a dump of what I got when I force the column type to ftUTF8 instead of ftBlob,

[{"XML_F52E2B61-18A1-11d1-B105-00805F49916B":"44074100630063006F0075006E00740044114F007000650072006100740069006F006E0061006C00530074006100740075007300440A53007500620043006C007500730074006500720001014101024103470681020103410310E40400000200415381038101"}]

while this is what I am supposed to get

<Account>
  <OperationalStatus>6</OperationalStatus>
  <SubCluster>AS</SubCluster>
</Account>

Thanks again!

ab · 2014-08-26 16:52:30

What is the full connection string generated?
Your "fConnectionString := fConnectionString + 'DataTypeCompatibility=80;'" sounds weird to me (I would have put a ';' before also).

The XML content is just not Unicode encoded as Hex.
Sounds like some raw content...

What is the hexadecimal content when it is returned as BLOB?

cheemeng · 2014-08-26 17:02:49

I think it might be your Base64 converted to hex?

BinToHex(Data.ColumnBlob(0));

returns the same data

'44074100630063006F0075006E00740044114F007000650072006100740069006F006E0061006C00530074006100740075007300440A53007500620043006C007500730074006500720001014101024103470681020103410310E40400000200415381038101'

As for fConnectionString, I just stepped in the debugger, it is an empty string. So it is the same as fConnectionString := 'DataCompatibility=80;';

The full connection string will end up being 'Provider=SQLNCLI11;Data Source=.\sqlexpress;Initial Catalog=dummy;User Id=;Password=;Integrated Security=SSPI;Persist Security Info=False;'

cheemeng · 2014-08-26 17:27:11

By the way, when I try to free my TOleDBMSSQL2012ConnectionProperties, I get this error at fProps.Free.

First chance exception at $75C5C42D. Exception class EOleDBException with message 'OLEDB Error 80040E05 - Object was open.'.

This only occurs if I execute a stored procedure. If I execute other SQL commands (SELECT, INSERT, UPDATE), I do not get this error. Here's some sample code:

    fProps := TOleDBMSSQLConnectionProperties.Create(fServer, fDatabase,
      fUser, fPassword);
    Data := fProps.Execute(fStatement, []);
    DataResult := Data.FetchAllAsJSON(True, nil, False, True);
    fProps.Free;

Happens when I use other versions of the MSSQL Connection Properties too.

Last edited by cheemeng (2014-08-26 17:27:57)

ab · 2014-08-26 17:46:05

cheemeng wrote:

The full connection string will end up being 'Provider=SQLNCLI11;Data Source=.\sqlexpress;Initial Catalog=dummy;User Id=;Password=;Integrated Security=SSPI;Persist Security Info=False;'

... so DataTypeCompatibility=80; disappears?

You have to write:

procedure TOleDBMSSQL2012ConnectionProperties.SetInternalProperties;
begin
  if OSVersion>wVista then
    fProviderName := 'SQLNCLI11';
  inherited SetInternalProperties;
  fConnectionString := fConnectionString+';DataTypeCompatibility=80';
end;

Your code

fProps := TOleDBMSSQLConnectionProperties.Create(fServer, fDatabase,
      fUser, fPassword);
    Data := fProps.Execute(fStatement, []);
    DataResult := Data.FetchAllAsJSON(True, nil, False, True);
    fProps.Free;

is a classic error.
You are mixing interfaces and manual class instances.
This is clearly stated in the doc.

Data is a ISQLRow, so will be released AFTER fProps.Free.
Your code

fProps := TOleDBMSSQLConnectionProperties.Create(fServer, fDatabase,
      fUser, fPassword);
   /// but you are creating fSTatement FROM fPRops, right?
    Data := fProps.Execute(fStatement, []);
    DataResult := Data.FetchAllAsJSON(True, nil, False, True);
    Data := nil; // release IRows
    fProps.Free;

cheemeng · 2014-08-26 17:59:27

Lol... sorry about the connection string part... was getting sleepy (it's almost 2am here).

Now DataCompatibility is in the connection string, but no changes. Forcing the column type to ftUTF8 returns the same hex dump, and BinToHex of the Column as blob returns the same dump.

As for the fProps.Free error, I had no idea that a TSQLDBStatement instance would prevent the Conn Properties from being freed... oops

Last edited by cheemeng (2014-08-26 17:59:43)

cheemeng · 2014-09-01 16:09:11

Arnaud, is there any other information that I could provide to help you look into this issue? Or should I just abandon this thread?

Thank you!

ab · 2014-09-01 17:57:56

Did you try to ask elsewhere, e.g. in StackOverflow or in MSDN forums/support?
I'm no MS SQL expert, and its raw XML format is something I never used (and don't need nor want to use)....

cheemeng · 2014-09-05 07:49:27

Thanks Arnaud. I'll update this thread if I managed to get any positive breakthrough.

ab · 2014-09-05 15:55:49

Yes.

Do not forget to let us now about your findings.

mORMot Open Source

#1 2014-08-22 09:50:16

Results from Stored Procedure (MS SQL) Returned as Base64?

#2 2014-08-22 10:44:34

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#3 2014-08-22 11:34:59

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#4 2014-08-22 12:23:04

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#5 2014-08-22 12:26:06

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#6 2014-08-22 13:06:48

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#7 2014-08-22 14:17:54

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#8 2014-08-22 14:21:22

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#9 2014-08-22 14:47:09

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#10 2014-08-22 14:51:07

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#11 2014-08-22 15:07:11

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#12 2014-08-22 15:12:49

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#13 2014-08-22 19:24:31

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#14 2014-08-23 12:43:59

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#15 2014-08-23 15:21:28

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#16 2014-08-26 16:30:58

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#17 2014-08-26 16:52:30

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#18 2014-08-26 17:02:49

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#19 2014-08-26 17:27:11

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#20 2014-08-26 17:46:05

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#21 2014-08-26 17:59:27

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#22 2014-09-01 16:09:11

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#23 2014-09-01 17:57:56

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#24 2014-09-05 07:49:27

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

#25 2014-09-05 15:55:49

Re: Results from Stored Procedure (MS SQL) Returned as Base64?

Board footer