FreeTDS Development Group

[Marc Abramowitz <marca AT surveymonkey.com>]

On July 26, 2013, Marc Abramowitz wrote:

> 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
>
> ...
>
> 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);