MFC and MySQL




















  1. #1


    MFC and MySQL

    Windows 10
    Visual Studio 2022 Community
    MySQL 8.0

    I have an MFC app that connects to the database fine and can run a simple query that then prints to a Control List.

    Code:

    TRY{
    		database.Open(NULL,false, false, ConnectionString);
    		CRecordset recset(&database);
    		
    		SqlString = L"SELECT firstname, lastname, middlename 
    					FROM names";
    
    		recset.Open(CRecordset::forwardOnly, SqlString, CRecordset::readOnly);
    
    		ListView_SetExtendedListViewStyle(m_ListControl, LVS_EX_GRIDLINES);
    
    		// Column width and heading
    		m_ListControl.InsertColumn(0, L"First Name", LVCFMT_LEFT, -1, 0);
    		m_ListControl.InsertColumn(1, L"Middle Name", LVCFMT_LEFT, -1, 1);
    		m_ListControl.InsertColumn(2, L"Last Name", LVCFMT_LEFT, -1, 1);
    ;
    		m_ListControl.SetColumnWidth(0, 45);
    		m_ListControl.SetColumnWidth(1, 45);
    		m_ListControl.SetColumnWidth(2, 45);
    
    		while (!recset.IsEOF()) {
    			// Copy each column into a CString variables
    			recset.GetFieldValue(L"firstname", strfirstname);
    			recset.GetFieldValue(L"middlename", strmiddlename);
    			recset.GetFieldValue(L"lastname", strlastname);
    
    			// Insert values into the list control
    			iRec = m_ListControl.InsertItem(0, strfirstname, 0);
    			m_ListControl.SetItemText(0, 1, strmiddlename);
    			m_ListControl.SetItemText(0, 2, strlastname);
    
    			// goto next record
    			recset.MoveNext();
    		}
    
    		database.Close();
    	}CATCH(CDBException, err) {
    		AfxMessageBox(L"Database Error: " + err->m_strError);
    	}
    	END_CATCH;

    Problem is when I try to run a query from two tables like this (which works in mysql command line):

    SqlString = L”SELECT n.firstname, n.lastname, n.middlename, a.address
    FROM names as n, address as a
    where n.ID = a.ID”;

    I get an assertion error when I try to copy the record into the CString variable with this code:
    recset.GetFieldValue(L”Address”, straddress);

    How do I get this to work using more than one table in the query?





  2. #2


    Re: MFC and MySQL

    Press the Retry button on the Assertion dialog to step in the code causing the failure and understand what was the reason…

    Victor Nijegorodov





  3. #3


    Re: MFC and MySQL

    Quote Originally Posted by zapper222
    View Post

    SqlString = L”SELECT n.firstname, n.lastname, n.middlename, a.address
    FROM names as n, address as a
    where n.ID = a.ID”;

    I get an assertion error when I try to copy the record into the CString variable with this code:
    recset.GetFieldValue(L”Address“, straddress);

    Your recordset does not include “Address” field. Try aliasing it:

    Code:

            SqlString = L"SELECT n.firstname, n.lastname, n.middlename, a.address AS Address 
                FROM names as n, address as a 
                where n.ID = a.ID";

    Best regards,
    Igor





  4. #4


    Re: MFC and MySQL

    Yeah that was it, a simple typo. Thanks Igor.
    The thing runs awfully slow though, 8 seconds for a query like that.
    One table has 224289 rows of data the other 145632 rows.
    I don’t think those are that big when talking databases.






  5. Re: MFC and MySQL

    Quote Originally Posted by zapper222
    View Post

    The thing runs awfully slow though, 8 seconds for a query like that.
    One table has 224289 rows of data the other 145632 rows.

    You need to make sure you’re blaming the real culprit. Cramming hundreds of thousands of rows into a plain list control seems not much reasonable to me altogether. List control gets extremely slow when stuffed like that, so querying database may have nothing to do with the slowness issue.

    Virtual List Controls

    Best regards,
    Igor






  6. Re: MFC and MySQL

    Quote Originally Posted by zapper222
    View Post

    The thing runs awfully slow though, 8 seconds for a query like that.
    One table has 224289 rows of data the other 145632 rows.

    I agree with Igor. The “classic” list control slows down with so many rows.
    Consider using the virtual list control.



















Posting Permissions

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












Click Here to Expand Forum to Full Width













.

Leave a Comment