CDBVariant type problem

_Flaviu

Hi all of you. I come here hoping to solve a problem that I have met on retrieving data from DB (SQL Server). I retrieve data in the following way:

pRs->GetFieldValue(i, *pDBVariant);

nothing special ... (pRS is CRecordset*, and pDBVariant is CDBVariant*). And this is method to format data:

CString CMyDoc::FormatData(CDBVariant* pDBVariant)
{
CString sRet;

do
{
	if(NULL == pDBVariant)
		break;
	switch(pDBVariant->m\_dwType)
	{
	case DBVT\_LONG:
		TRACE("DBVT\_LONG\\n");
		sRet.Format(\_T("%d"), pDBVariant->m\_lVal);
		break;
	case DBVT\_DOUBLE:
		TRACE("DBVT\_DOUBLE\\n");
		break;
	case DBVT\_SHORT:
		TRACE("DBVT\_SHORT\\n");
		break;
	case DBVT\_SINGLE:
		TRACE("DBVT\_SINGLE\\n");
		break;
	case DBVT\_STRING:
		TRACE("DBVT\_STRING\\n");
		break;
	case DBVT\_ASTRING:
		TRACE("DBVT\_ASTRING\\n");
		sRet = \*pDBVariant->m\_pstring;
		break;
	case DBVT\_DATE:
		TRACE("DBVT\_DATE\\n");
		break;
	case DBVT\_WSTRING:
		TRACE("DBVT\_WSTRING\\n");
		CStringW wstring = \*pDBVariant->m\_pstringW;
		sRet = CString(wstring);
		break;
	}
}
while(FALSE);

return sRet;

}

I have the following SQL:

SELECT nUser FROM my_table;

nUser has int SQL data type, and when I test this data with CMyDoc::FormatData, the returning data type are: DBVT_LONG Ok, let take another SQL:

SELECT dVAT FROM my_table;

dVAT has decimal (10.2) SQL data type, and when I test this data with CMyDoc::FormatData, the returning data type are: DBVT_ASTRING ... why ? I have tested another SQL:

SELECT sName FROM my_table;

sName has string SQL data type, and when I test this data with CMyDoc::FormatData, the returning data type are: DBVT_ASTRING, which are ok ... but, excepting int SQL data type, all other SQL data type are having DBVT_ASTRING when I test this with CMyDoc::FormatData ... why ? Can you help me ? Thank you.

Lost User

Have you checked the actual data returned in the variant to see whether it is a string or the value type you expect?

David Crow

It's unrelated to the problem but why the unnecessary do/while loop?

"One man's wage rise is another man's price increase." - Harold Wilson

"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons

"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles

_Flaviu

Good point, it's unnecessary !

_Flaviu

I re-written the FormatData method:

CString CMyDoc::FormatData(CDBVariant* pDBVariant)
{
CString sRet;

switch(pDBVariant->m\_dwType)
{
case DBVT\_LONG:
	TRACE("DBVT\_LONG\\n");
	sRet.Format(\_T("%d"), pDBVariant->m\_lVal);
	break;
case DBVT\_DOUBLE:
	TRACE("DBVT\_DOUBLE\\n");
	break;
case DBVT\_SHORT:
	TRACE("DBVT\_SHORT\\n");
	break;
case DBVT\_SINGLE:
	TRACE("DBVT\_SINGLE\\n");
	break;
case DBVT\_STRING:
	TRACE("DBVT\_STRING\\n");
	break;
case DBVT\_ASTRING:
	TRACE("DBVT\_ASTRING\\n");
	sRet = \*pDBVariant->m\_pstring;
	break;
case DBVT\_DATE:
	TRACE("DBVT\_DATE\\n");
	break;
case DBVT\_WSTRING:
	TRACE("DBVT\_WSTRING\\n");
	CStringW wstring = \*pDBVariant->m\_pstringW;
	sRet = CString(wstring);
	break;
}

return sRet;

}

I have the following SQL:

SELECT nUser FROM my_table

In DB, nUser has "int" type. The result in FormatData method: DBVT_LONG I have another SQL:

SELECT dValue FROM my_table

In DB, dValue has "decimal (10.2)" type. The result in FormatData method: DBVT_ASTRING !? Why ? I have another SQL:

SELECT sName FROM my_table

In DB, dValue has "varchar(1024)" type. The result in FormatData method: DBVT_ASTRING - correct. I noticed that any king of column type in DB, except "int" type, return DBVT_ASTRING in FormatData method ... why ? I have tried to get data with CRecordset as dynaset, snaphot and dynamic ... every one of them had the same result ...

Lost User

That is what you wrote in your original question, and removing the unnecessary do ... while() is not going to affect things. The issue is, what value type is SQL returning into your recordset? What you are looking at here is happening long after the data has been sent from the database.

leon de boer

What you have described sounds perfectly correct depending how you set the tables up. Your nUser will be (look at SQL spec integer value between the range 2^ -31 and 2^31 -1) which is 32 bits or 4 bytes and would be a long in C/C++. Your decimal (10.2) will be stored as an IEEE-754/-854-compliant bit string which will be 5-17 bytes. If you stored your decimal(10.2) as singles/doubles you would get issues when you added them etc the small fractions you can't see would roll do you understand lets add 3 digits with 3 decimal places Stored .... 10.2 format ------------------------- 1.344 .... 1.34 2.364 .... 2.36 1.134 .... 1.13 ---------------------- 4.842 .... 4.83 See the Problem 4.842 down to 2 digits is 4.84 and is not right answer they don't give same result and that is why SQL does not automatically covert numbers with precision. I am sure if you make a table of doubles, single or floats they will come back as that just don't expect that from decimal, numeric which will be strings

In vino veritas

Lost User

Hi,

Flaviu2 wrote:

dVAT has decimal (10.2) SQL data type, and when I test this data with CMyDoc::FormatData, the returning data type are: DBVT_ASTRING ... why ?

Because the best way to pass a floating point value between network devices is as TEXT. The SQL server has no idea what architecture your device is using and how it will interpret the floating point value. Best Wishes, -David Delaune

Victor Nijegorodov

Did you read the answers in [Returning CDBVariant type](http://forums.codeguru.com/showthread.php?560437-Returning-CDBVariant-type)?

_Flaviu

Yes, you are right ... I must change the data type in SQL in order to get right values in CDBVariant ... but I should ask this to my SQL colleagues ... :) Kindly thank you for your answer !!