|
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 |