[freetds] dbrpcparam with SQLDECIMAL output parameter

[Marc Abramowitz]

I have some code that calls dbrpcparam [1] with type = SQLDECIMAL and DBRPCRETURN. It is attempting to get back a DECIMAL(6, 5) from a stored proc.

What I am seeing is that the data returned has a scale of 0 ? i.e.: the value I get back looks like this:

data[0] = 38
data[1] = 0

So it appears that I'm getting a DECIMAL(38, 0) and thus losing everything after the decimal point.

I found an old message on the list about this [2], which suggested that one should send a DBDECIMAL structure with the desired precision and scale (code was added by Freddy for this at that time). I tried that and thus far have still not gotten it to send back the expected value. I am wondering if I have something wrong still ? I am unclear on what I should be sending for maxlen and datalen for instance. I've tried a number of combinations of 0, -1, and sizeof(DBDECIMAL) and so far they all have returned the same DECIMAL(38, 0) or I have gotten back an error about using an invalid length.

Currently, I have something like this:

DBDECIMAL db_decimal
db_decimal.precision = 18
db_decimal.scale = 5

dbrpcparam(dbproc, "@odecimal", DBRPCRETURN, SQLDECIMAL, sizeof(DBDECIMAL), 0, (BYTE *) &db_decimal) // SQLDECIMAL = 106; sizeof(DBDECIMAL) = 35

Am I doing this wrong? Anyone have a working example of returning a SQLDECIMAL?

Thanks,
Marc

[1] http://freetds.schemamania.org/reference/a00285.html#ga8
[2] http://lists.ibiblio.org/pipermail/freetds/2009q3/025076.html

[Marc Abramowitz]

Post by Marc Abramowitz
I have some code that calls dbrpcparam [1] with type = SQLDECIMAL and
DBRPCRETURN. It is attempting to get back a DECIMAL(6, 5) from a stored
proc.
What I am seeing is that the data returned has a scale of 0 ? i.e.: the
value
data[0] = 38
data[1] = 0
...
Am I doing this wrong? Anyone have a working example of returning a
SQLDECIMAL?

I got this working (it took a while to figure out) and I wanted to post for
anyone who is searching and finds this later.

Here's what I do:

// stored proc that takes a decimal(6, 5) as input and returns it as an
// output parameter
rc = dbrpcinit(dbproc, "pymssqlTestDecimal", 0);

// Convert string with decimal to a DBDECIMAL struct
DBTYPEINFO type_info;
type_info.precision = 18;
type_info.scale = 5;
DBDECIMAL idecimal;
dbconvert_ps(
/* dbproc */ dbproc,
/* srctype */ SQLCHAR,
/* src */ "5.12345",
/* srclen */ -1,
/* desttype */ SQLDECIMAL,
/* dest */ (void *)&idecimal,
/* destlen */ sizeof(idecimal),
/* typeinfo */ &type_info
);

// Bind @idecimal input param
rc = dbrpcparam(
/* dbproc */ dbproc,
/* paramname */ "@idecimal",
/* status */ 0,
/* type */ SQLDECIMAL,
/* maxlen */ -1,
/* datalen */ sizeof(idecimal),
/* value */ (void *)&idecimal
);

// Bind @odecimal output param
DBDECIMAL odecimal = { 0 };
odecimal.precision = 18;
odecimal.scale = 5;
rc = dbrpcparam(
/* dbproc */ dbproc,
/* paramname */ "@odecimal",
/* status */ DBRPCRETURN,
/* type */ SQLDECIMAL,
/* maxlen */ -1,
/* datalen */ sizeof(odecimal),
/* value */ (void *)&odecimal
);

rc = dbrpcsend(dbproc);
rc = dbsqlok(dbproc);
BYTE *data = dbretdata(dbproc, 1);

// Convert returned DBDECIMAL struct to a string for easy printing
char odecimal_as_cstr[8];
float odecimal_as_float;
dbconvert_ps(
/* dbproc */ dbproc,
/* srctype */ SQLDECIMAL,
/* src */ (void *)data,
/* srclen */ sizeof(odecimal),
/* desttype */ SQLCHAR,
/* dest */ (void *)odecimal_as_cstr,
/* destlen */ -1,
/* typeinfo */ &type_info
);
dbconvert_ps(
/* dbproc */ dbproc,
/* srctype */ SQLDECIMAL,
/* src */ (void *)data,
/* srclen */ sizeof(odecimal),
/* desttype */ SQLFLT4,
/* dest */ (void *)&odecimal_as_float,
/* destlen */ -1,
/* typeinfo */ &type_info
);
printf("string rep of odecimal = \"%s\"\n", odecimal_as_cstr);
printf("float rep of odecimal = %f\n", odecimal_as_float);