Connecting Tech Pros Worldwide Help | Site Map

dbconvert() doing incorrect conversions

Suvodip Mukherjee
Guest
 
Posts: n/a
#1: Jul 20 '05
Hi,

I am facing a problem using the DBLIBRARY API dbconvert().
When dbconvert() is used to convert a MS SQL Server MONEY data type to
DECIMAL and then to STRING, the scale is getting lost.
eg. 41.98 returns 41.97999
123456789123456.98 returns 123456789123456.97000

The code snippet in VC++ is given as follows:

// Application to get the erratic behaviour of dbconvert when this
// dblibrary api is used to convert a MS SQL Server Money data type
// to a Decimal of scale 5 and then to a String.
// This two step conversion is done to preserve the scale position of
// the Money type which is 4. A direct conversion from Money to
// String as:
// dbconvert(dbproc, IP_SQLMONEY, pSrc, sizeof(DBMONEY), IP_SQLCHAR,
tmpstr, -1);
// returns the value in a scale of 2.
// Sample data which gives the error:
// 41.98 returns 41.97999
// 123456789123456.98 returns 123456789123456.97000

#include <stdio.h>
#include <windows.h>
#define DBNTWIN32
#include "sqlfront.h"
#include "sqldb.h"
#include "tchar.h"

#define SYBMONEY (BYTE)0x3C
#define SYBDECIMAL (BYTE)0x6A
#define SYBCHAR (BYTE)0x2F

// forward declarations
int err_handler(PDBPROCESS, INT, INT, INT, LPCSTR, LPCSTR);
int msg_handler(PDBPROCESS, DBINT, INT, INT, LPCSTR, LPCSTR, LPCSTR,
DBUSMALLINT);
int load();

DBPROCESS * dbproc;

// main
int main(int argc, char* argv[])
{

if (load() == 0)
{
printf("Load error\n");
}

dbexit();

return 0;
}

// error handler
int err_handler (PDBPROCESS dbproc, INT severity, INT dberr, INT
oserr,
LPCSTR dberrstr, LPCSTR oserrstr)
{
printf ("DB-Library Error %i: %s\n", dberr, dberrstr);
if (oserr != DBNOERR)
{
printf ("Operating System Error %i: %s\n", oserr, oserrstr);
} return (INT_CANCEL);
}

// msg handler
int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate, INT
severity,
LPCSTR msgtext, LPCSTR server, LPCSTR procedure, DBUSMALLINT line)
{
printf ("SQL Server Message %ld: %s\n", msgno, msgtext);
return (0);
}

int load()
{
LOGINREC * login;
DBCHAR price[64];

DBDECIMAL decTemp;

login = dblogin();
DBSETLUSER (login, "sa");
DBSETLPWD (login, "");

// get a DBPROCESS structure for communication with SQL Server.
// SUVODIP is the server name, change accordingly
if ((dbproc = dbopen (login, "SUVODIP")) == (DBPROCESS*) NULL)
{
printf("error opening connection\n");
return 0;
}

// Read the query into the command buffer.
// suvodip is the name of the database
// Table_1344_src is the table name with a Money type field
dbcmd(dbproc, "SELECT Price FROM suvodip..Table_1344_src");

// Send the query to SQL Server.
dbsqlexec(dbproc);

// Get ready to process the results of the query.
dbresults(dbproc);

// Stores the return Value from dbconvert;
// -1 indicates error, otherwise the size in bytes
int rtval;

// Process each row.
while (dbnextrow(dbproc) != NO_MORE_ROWS)
{
decTemp.precision = (char)38;
decTemp.scale = (char)5;

// Converts a Money data type to Decimal
rtval = dbconvert(dbproc, SYBMONEY, (dbdata(dbproc,1)),
(DBINT)-1, SYBDECIMAL,
(unsigned char*)&decTemp, sizeof(DBDECIMAL));//(DBINT)-1);

// Converts the Decimal data type to String
if(rtval != -1)
rtval = dbconvert(dbproc, SYBDECIMAL, (unsigned
char*)&decTemp, (DBINT)-1, SYBCHAR,
(unsigned char*)price, (DBINT)-1);

if(rtval != -1)
{
printf ("Price: %s\n", price);
}
else
{
printf ("Error encountered. \n");
return 0;
}
}
return 1;
}

To run the application, this file needs to be built in a MSDev Studio
environment and a table needs to be created with a MONEY data type.
Also the necessary changes in the code for the Server Name, Database
Name, Table Name and the Column Name needs to be done.

Please help me out.
I am in real urgent need of a solution/workaround.

Thanks in advance,
Suvodip
Closed Thread