Unexpected behavior when bult-in sql functioned invoked

[zbychfish]

Output from LOGINPROPERTY builtin function is not correctly encoded

conn_params=['','sa','xxxx','192.168.0.20','1433','master']
conn=TinyTds::Client.new username: conn_params[1], password: conn_params[2],	host: conn_params[3], port: conn_params[4], database: conn_params[5], azure:false
sql="SELECT LOGINPROPERTY('sa', 'IsLocked') as param)"
puts conn.encoding
puts result=conn.execute(sql).each

returns:

UTF-8
{"param"=>"\u0000\u0000\u0000\u0000"}
[Finished in 1.236s]

It looks that something wrong with output encoding.

I found temporary solution but the reason of this behavior will be useful.
I have converted value to int internally on sql server:
SELECT CONVERT(int,(SELECT LOGINPROPERTY('sa', 'IsLocked') as param))
in this case returned value from tiny_tds is correctly displayed

[metaskills]

Indeed! It seems other functions work. I wonder why this one does not.

v = @client.execute("SELECT LOGINPROPERTY('sa', 'IsLocked') as v").first['v']
=> "\u0000\u0000\u0000\u0000"

If I add a debug statement to the result file:

--- a/ext/tiny_tds/result.c
+++ b/ext/tiny_tds/result.c
@@ -205,6 +205,7 @@ static VALUE rb_tinytds_result_fetch_row(VALUE self, ID timezone, int symbolize_
     DBINT data_len = dbdatlen(rwrap->client, col);
     int null_val = ((data == NULL) && (data_len == 0));
     if (!null_val) {
+      rb_warn("coltype %i", coltype);
       switch(coltype) {
         case SYBINT1:
           val = INT2FIX(*(DBTINYINT *)data);

I get the following information:

@client.execute("SELECT LOGINPROPERTY('sa', 'IsLocked') as v").first['v']
(pry):1: warning: coltype 98
=> "\u0000\u0000\u0000\u0000"

And I do not see that in the https://github.com/FreeTDS/freetds/blob/master/include/sybdb.h#L153 file

[metaskills]

I filed this issue with FreeTDS FreeTDS/freetds#86

[metaskills]

Made this PR for us: #321

[metaskills]

Odd that Ubuntu fails this new test but not locally on OS X or Appveyor.

Expected: 0
Actual: 140518445023232

[metaskills]

Ah! I think it is a 4-byte integer. Testing that now...

[freddy77]

The data returned by dbdata is dependent on the type of SYBVARIANT (98). Basically every cell of the table can have a different type. I think currently there's no way to get the type of the SYBVARIANT cells in our dblib.
Microsoft dblib support up to 4.2 version of the protocol which does not support SYBVARIANT so SYBVARIANT is returned as VARCHAR or TEXT (not sure).

[metaskills]

Thanks @freddy77 for dropping in. Have you seen other places where SYBVARIANT col types are returned? This is my first time seeing it.

I am going to move our SYBVARIANT case to the bottom of rb_tinytds_result_fetch_row and just check the length of the data. If == 4 then we will assume a DBINT. If not, we will just pass it thru as a binary string as is.

[metaskills]

This is now in master and will be upcoming v1.0.6 release.