No data returned

[OneEyeMan]

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.

            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:

            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?

[Arjay]

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?

[OneEyeMan]

Hi,

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.

[OneEyeMan]

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

Thank you.