Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

derived CRecordset field binding problem?

Posted on 2008-10-30
6
Medium Priority
?
1,134 Views
Last Modified: 2013-11-20
I have a view called 'view_worklist'  defined in MS SQL server that produces output columns of the following types:

int, uniqueidentifier, nvarchar(255), nvarchar(max), datetime, xml

In Visual C++ 6.0 class wizard, I created CWorklistRS derived from CRecordset.  I chose to bind all the columns and the wizard did its thing.  I then changed the member type from CString to CByteArray for each of the uniqueidentifier columns and replaced the RFX_Text macro with RFX_Binary for the same.

I then implemented GetDefaultSQL to return "select * from view_worklist where id=1"

Connection to the server works properly. I create an instance of CWorklistRS as follows:

            CWorklistRS rsWorklist(pDB);
            if( rsWorklist.Open( CRecordset::snapshot ))

however I end up getting an exception in:

LRESULT AFXAPI AfxCallWndProc(CWnd* pWnd, HWND hWnd, UINT nMsg,

The exception reads "Restricted data type attribute violation"
State:07006,Native:0,Origin:[Microsoft][SQL Native Client]
m_nRetCode = -1

Any ideas what I'm doing wrong? Is it perhaps because the data source is a view and not a table? If so how do I handle that?
0
Comment
Question by:PMH4514
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 1000 total points
ID: 22848255
>>>> Is it perhaps because the data source is a view and not a table?
No, table or view is the same for ODBC.

I assume it is because of the nvarchar columns which were UNICODE text type. Is your app a also UNICODE?

I also wonder about nvarchar(max), datetime and xml. Do you really need these *strange* datatypes which I never heard of that they were directly supported by MFC recordsets.  Why not making simple fixed sized text and number fields especially if you have a view which might change the data type of the original fields.  
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 22848522
The ClassWizard will work to create a CRecordset-derived object from a database view as well as a table.

Howevver, there are several non-standard items in your view:
uniqueidentifier
nvarchar(max)
xml
I don't know which of them is causing the problem. Try removing these and adding them back one at a time (creating a new CRecordset-derived object each time).

0
 

Author Comment

by:PMH4514
ID: 22850272
Hi Guys -
Yes I the app is compiled UNICODE.

Yes, regarding uniqueidentifier, nvarchar(max), xml  - I am brainstorming reworking those.. They are appropriate data-types within the grand scheme of the database and web application front-end.. Well, uniqueidentifier is as all records using them need to be unique across "space and time".. nvarchar(max) is actually a typo, it's specified as nvarchar(2000), sorry about that.. and XML - how else would I store a large chunk of XML code Tthe text field type if I recall doesn't concatenate well in TSQL code with the + operator which is something a stored procedure is doing.

Anyway,  The C++ application in question is just a small DLL that is running as an agent more or less and is the only MFC piece of the larger architecture and it only looks at this single view, so I'd hate to use less appropriate data-types in the database schema merely to support such a small piece of the overall picture..


itsmeandnobodyelse wrote:
>>especially if you have a view which might change the data type of the original fields.  

Interesting.. It did not occur to me to have the view change the data type of the original fields.. That would be implemented with CAST or CONVERT within the SQL code that built the view? That's good food for though that might solve all of this..  thanks.

That said, I did go and remove  the "strange" type references, XML and uniqueidentifier, so now the CRecordset derived class is only for integer, nvarchar(2000), nvarchar(255) and datetime and I received the same exception "Restricted data type attribute violation" when trying to open it.

So then I removed the datetime type, and still have the same problem.

Any thoughts?

I'm going to try as well to cast everything, in the view, to strings to see if I can make it play nice with MFC.

0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:PMH4514
ID: 22850353
EDIT - actually, I take that last comment back.. Once I removed the dataetime type, while I did still receive an exception, it was not the restricted data type exception, rather, "Invalid character value for cast specification"

Perhaps it has a problem with null fields?
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 22850790
>>>> Once I removed the dataetime type,
Yes datetime unfortunately is still something special as it varies between databases. Here you convert it to a - say 19 chars - string field in your view and it should work.

>>>> "Invalid character value for cast specification"
It was 10 years ago and SQL Server firstly comes with nvarchar instead of varchar2. I mean to remember we had similar problems. Look at your RecordExchange member function what macros it used for the nvarchar2. If it is RfxText it is probably wrong cause as far as I know RfxText is for ANSI strings only. I don't know exactly whether VC6 was providing a Rfx macro for UNICODE. If not, you would need to write it yourself, probably. To these times I had my own ODBC recordset class and it wasn't so much difficult to support UNICODE strings.  
0
 

Author Comment

by:PMH4514
ID: 22851053
itsmeandnobodyelse - I am going to accept your original response as the solution, specifically:

" Why not making simple fixed sized text and number fields especially if you have a view which might change the data type of the original fields.  "

I was not aware that I could CONVERT in the query that assembles the view. I did so, making everything nvarchar types. I made the necessary changes in my MFC code and now it all works perfectly.

thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: SunnyDark
This article's goal is to present you with an easy to use XML wrapper for C++ and also present some interesting techniques that you might use with MS C++. The reason I built this class is to ease the pain of using XML files with C++, since there is…
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question