dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: No data returned

  1. #1
    Join Date
    Aug 2002
    Posts
    685

    No data returned

    Hi,
    I've been on this couple of hours but can't figure out why am I not getting the data.
    I think I just need a fresh pair of eyes.

    Thank you.

    Code:
                ret = SQLPrepare( m_hstmt, qry, SQL_NTS );
                if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
                {
                    GetErrorMessage( errorMsg, 1, m_hstmt );
                    result = 1;
                }
    			else
    			{
                    ret = SQLNumResultCols( m_hstmt, &numCols );
                    if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
                    {
                        GetErrorMessage( errorMsg, 1, m_hstmt );
                        result = 1;
                    }
                    else
                    {
                        for( int i = 0; i < numCols; i++ )
                        {
                            columnNameLen = new SQLSMALLINT *[numCols];
                            columnDataType = new SQLSMALLINT *[numCols];
                            columnDataSize = new SQLULEN *[numCols];
                            colummnDataDigits = new SQLSMALLINT *[numCols];
                            columnDataNullable = new SQLSMALLINT *[numCols];
                            columnData = new SQLWCHAR *[numCols];
                            columnDataLen = new SQLLEN *[numCols];
                        }
                        for( int i = 0; i < numCols; i++ )
                        {
                            columnNameLen[i] = new SQLSMALLINT;
                            columnDataType[i] = new SQLSMALLINT;
                            columnDataSize[i] = new SQLULEN;
                            colummnDataDigits[i] = new SQLSMALLINT;
                            columnDataNullable[i] = new SQLSMALLINT;
                            columnName[i] = new SQLWCHAR[256];
                            columnDataLen[i] = new SQLLEN;
                            ret = SQLDescribeCol( m_hstmt, i + 1, columnName[i], 256, columnNameLen[i], columnDataType[i], columnDataSize[i], colummnDataDigits[i], columnDataNullable[i] );
                            if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
                            {
                                GetErrorMessage( errorMsg, 1, m_hstmt );
                                result = 1;
                                break;
                            }
                            columnData[i] = new SQLWCHAR[(unsigned int) *columnDataSize[i] + 1];
                            memset( columnData[i], '\0', (unsigned int) *columnDataSize[i] + 1 );
                            switch( *columnDataType[i] )
                            {
                                case SQL_INTEGER:
                                    *columnDataType[i] = SQL_C_LONG;
                                    break;
                                case SQL_VARCHAR:
                                case SQL_CHAR:
                                    *columnDataType[i] = SQL_C_CHAR;
                                    break;
                                case SQL_WVARCHAR:
                                case SQL_WCHAR:
                                    *columnDataType[i] = SQL_C_WCHAR;
                                    break;
                            }
                            ret = SQLBindCol( m_hstmt, i + 1, *columnDataType[i], &columnData[i], *columnDataSize[i], columnDataLen[i] );
                            if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
                            {
                                GetErrorMessage( errorMsg, 1, m_hstmt );
                                result = 1;
                                break;
                            }
                        }
                        ret = SQLExecute( m_hstmt );
                        if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
                        {
                            GetErrorMessage( errorMsg, 1, m_hstmt );
                            result = 1;
                        }
                        for( ret = SQLFetch( m_hstmt ); ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) && ret != SQL_NO_DATA; ret = SQLFetch( m_hstmt ) )
                        {
                            str_to_uc_cpy( tableName, columnData[2] );
                            //if( *columnData[1] == 'C' )
                            str_to_uc_cpy( command, columnData[2] );
                        }
    Everything works as expected but the columnData are empty strings.

    Below is the query I'm trying to execute:

    Code:
                std::wstring sub_query1 = L"SET NOCOUNT ON; DECLARE @TargetDialogHandle UNIQUEIDENTIFIER; ";
                std::wstring sub_query2 = L"DECLARE @EventMessage XML; ";
                std::wstring sub_query3 = L"DECLARE @EventMessageTypeName sysname; ";
                std::wstring sub_query4 = L"WAITFOR( RECEIVE TOP(1) @TargetDialogHandle = conversation_handle, @EventMessage = CONVERT(XML, message_body), @EventMessageTypeName = message_type_name FROM dbo.EventNotificationQueue ), TIMEOUT 1000;";
                std::wstring sub_query5 = L"SELECT @EventMessageTypeName AS MessageTypeName, @EventMessage.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS TSQLCommand, @EventMessage.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)' ) as TableName";
                std::wstring query = sub_query1 + sub_query2 + sub_query3 + sub_query4 + sub_query5;
    Running it Management Studio everything works.

    Could someone please help?

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,805

    Re: No data returned

    Dealing with raw sql prepare statements where everything has to be allocated, column data types and count need to match up perfectly before everything works is so painful to have to deal with. Is there not a cross-platform ORM that you can use that will do the basic retrieval for you?

  3. #3
    Join Date
    Aug 2002
    Posts
    685

    Re: No data returned

    Hi,
    Quote Originally Posted by Arjay View Post
    Dealing with raw sql prepare statements where everything has to be allocated, column data types and count need to match up perfectly before everything works is so painful to have to deal with. Is there not a cross-platform ORM that you can use that will do the basic retrieval for you?
    ODBC API is the only one I know that works on all 3 major platforms: Windows, *nix and Mac.

    But I think I figured it out - the SQLDescribeCol() for the second column in the recordset returns 0 for the column size. Which is of course perfectly normal considering that this column should return a CREATE/ALTER/DROP command used, which is of course very different in size. And looking in the documentation here for the 7th parameter is perfectly normal.

    That same page does not recommend to call this function after the call to SQLExecute(), but I can try to do so and see what will happen.

    Thank you.

  4. #4
    Join Date
    Aug 2002
    Posts
    685

    Re: No data returned

    Hi,
    I started using SQLGetData() and looks like it works OK.

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)