NO_DATA is returned while there is a data
Posted by: Igor Korot
Date: December 24, 2022 11:52PM

Hi, ALL,

Following code returns MYSQL_NO_DATA for the table where there is definitly a foreign key.

Could someone please explain what I am doing wrong?

Thank you.

    std::wstring cat, table = L"owners", schema = L"draft";
    int result = 0;
    std::wstring query2 = L"SELECT kcu.column_name, kcu.ordinal_position, kcu.referenced_table_schema, kcu.referenced_table_name, kcu.referenced_column_name, rc.update_rule, rc.delete_rule, rc.constraint_name FROM information_schema.key_column_usage kcu, information_schema.referential_constraints rc WHERE kcu.constraint_name = rc.constraint_name AND kcu.table_catalog = ? AND kcu.table_schema = ? AND kcu.table_name = ?;";
    char *col_name[256], ref_schema[256], ref_table[256], ref_column[256], constraint_name[256];
    char update_rule[20], delete_rule[20];
    int ordinal;
    std::map<unsigned long, std::vector<FKField *> > foreign_key;
    unsigned long str_length1, str_length2, str_length3;
    str_length1 = catalog.length() + 2;
    str_length2 = schemaName.length() + 2;
    str_length3 = tableName.length() + 2;
    char *cat = new char[str_length1], *schema = new char[str_length2], *table = new char[str_length3];
    memset( cat, '\0', str_length1);
    memset( schema, '\0', str_length2 );
    memset( table, '\0', str_length3 );
    strcpy( cat, m_pimpl->m_myconv.to_bytes( catalog.c_str() ).c_str() );
    strcpy( schema, m_pimpl->m_myconv.to_bytes( schemaName.c_str() ).c_str() );
    strcpy( table, m_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str() );
    MYSQL_BIND params[3];
    memset( params, 0, sizeof( params ) );
    auto res1 = mysql_stmt_init( m_db );
    if( !res1 )
    {
        std::wstring err = m_pimpl->m_myconv.from_bytes( mysql_stmt_error( res1 ) );
        errorMsg.push_back( err );
        result = 1;
    }
    else
    {
        if( mysql_stmt_prepare( res1, m_pimpl->m_myconv.to_bytes( query2.c_str() ).c_str(), query2.length() ) )
        {
            std::wstring err = m_pimpl->m_myconv.from_bytes( mysql_stmt_error( res1 ) );
            errorMsg.push_back( err );
            result = 1;
        }
        else
        {
            params[0].buffer_type = MYSQL_TYPE_STRING;
            params[0].buffer = (char *) cat;
            params[0].buffer_length = strlen( cat );
            params[0].is_null = 0;
            params[0].length = &str_length1;
            params[1].buffer_type = MYSQL_TYPE_STRING;
            params[1].buffer = (char *) schema;
            params[1].buffer_length = strlen( schema );
            params[1].is_null = 0;
            params[1].length = &str_length2;
            params[2].buffer_type = MYSQL_TYPE_STRING;
            params[2].buffer = (char *) table;
            params[2].buffer_length = strlen( table );
            params[2].is_null = 0;
            params[2].length = &str_length3;
            strcpy( cat, m_pimpl->m_myconv.to_bytes( catalog.c_str() ).c_str() );
            strcpy( schema, m_pimpl->m_myconv.to_bytes( schemaName.c_str() ).c_str() );
            strcpy( table, m_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str() );
            if( mysql_stmt_bind_param( res1, params ) )
            {
                std::wstring err = m_pimpl->m_myconv.from_bytes( mysql_stmt_error( res1 ) );
                errorMsg.push_back( err );
                result = 1;
            }
            else
            {
                if( mysql_stmt_execute( res1 ) )
                {
                    std::wstring err = m_pimpl->m_myconv.from_bytes( mysql_stmt_error( res1 ) );
                    errorMsg.push_back( err );
                    result = 1;
                }
                else
                {
                    MYSQL_BIND results1[8];
                    bool is_null[8], error[8];
                    unsigned long length[8];
                    memset( results1, 0, sizeof( results1 ) );
                    results1[0].buffer_type = MYSQL_TYPE_STRING;
                    results1[0].buffer = &col_name;
                    results1[0].buffer_length = 256;
                    results1[0].is_null = &is_null[0];
                    results1[0].length = &length[0];
                    results1[0].error = &error[0];
                    results1[1].buffer_type = MYSQL_TYPE_SHORT;
                    results1[1].buffer = (char *) &ordinal;
                    results1[1].is_null = &is_null[1];
                    results1[1].error = &error[1];
                    results1[1].length = &length[1];
                    results1[2].buffer_type = MYSQL_TYPE_STRING;
                    results1[2].buffer = &ref_schema;
                    results1[2].buffer_length = 256;
                    results1[2].is_null = &is_null[2];
                    results1[2].length = &length[2];
                    results1[2].error = &error[2];
                    results1[3].buffer_type = MYSQL_TYPE_STRING;
                    results1[3].buffer = &ref_table;
                    results1[3].buffer_length = 256;
                    results1[3].error = &error[3];
                    results1[3].length = &length[3];
                    results1[3].error = &error[3];
                    results1[4].buffer_type = MYSQL_TYPE_STRING;
                    results1[4].buffer = &ref_column;
                    results1[4].buffer_length = 256;
                    results1[4].is_null = &is_null[4];
                    results1[4].length = &length[4];
                    results1[4].error = &error[4];
                    results1[5].buffer_type = MYSQL_TYPE_STRING;
                    results1[5].buffer = &update_rule;
                    results1[5].buffer_length = 20;
                    results1[5].length = &length[5];
                    results1[5].is_null = &is_null[5];
                    results1[5].error = &error[5];
                    results1[6].buffer_type = MYSQL_TYPE_STRING;
                    results1[6].buffer_length = 20;
                    results1[6].buffer = &delete_rule;
                    results1[6].length = &length[6];
                    results1[6].is_null = &is_null[6];
                    results1[6].error = &error[6];
                    results1[7].buffer_type = MYSQL_TYPE_STRING;
                    results1[7].buffer = &constraint_name;
                    results1[7].buffer_length = 256;
                    results1[7].is_null = &is_null[7];
                    results1[7].length = &length[7];
                    results1[7].error = &error[7];
                    if( mysql_stmt_bind_result( res1, results1 ) )
                    {
                        std::wstring err = m_pimpl->m_myconv.from_bytes( mysql_stmt_error( res1 ) );
                        errorMsg.push_back( err );
                        result = 1;
                    }
                    else
                    {
                        while( true )
                        {
                            auto dataset = mysql_stmt_fetch( res1 );
                            if( dataset == 1 || dataset == MYSQL_NO_DATA )
                                break;
                            else
                            {
                                auto orig_col = m_pimpl->m_myconv.from_bytes( *col_name );
                                auto refCol = m_pimpl->m_myconv.from_bytes( ref_column );
                                auto refSchema = m_pimpl->m_myconv.from_bytes( ref_schema );
                                auto refTable = m_pimpl->m_myconv.from_bytes( ref_table );
                                std::map<int, std::vector<std::wstring> >origFields, refFields;
                                origFields[ordinal].push_back( orig_col );
                                refFields[ordinal].push_back( refCol );
//                                foreign_key[ordinal].push_back( new FKField( ordinal, constraint_name, schemaName, tableName, orig_col, refSchema refTable, refCol, origFields[ordinal], refFields[ordinal], 
                            }
                        }
                        if( mysql_stmt_close( res1 ) )
                        {
                            std::wstring err = m_pimpl->m_myconv.from_bytes( mysql_stmt_error( res1 ) );
                            errorMsg.push_back( err );
                            result = 1;
                        }
                    }
                }
            }
        }
    }

Options: ReplyQuote


Subject
Views
Written By
Posted
NO_DATA is returned while there is a data
345
December 24, 2022 11:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.