How to fetch data from mysql_querry

Question

1.00/5 (1 vote)

See more:

hello i am try to get a group of data from mysql with c++ language
i did some research but i only got the info how to get one data
for an example

from the table of 'mytable'
there is 2 colum

one 1st column is name
2nd column is age

in the name column i got
'jessica' 'jessie' and 'jessy

age was 10, 12, 18

how do i store all of the data to a list of string
when i am calling SELECT * from mytable

What I have tried:

char* retrieveData(char* sqlStatement) {
		mysql_query(_mysqlCon, sqlStatement);
		_mysqlResult = mysql_store_result(_mysqlCon);
		if((_mysqlRows = mysql_fetch_row(_mysqlResult)) == NULL) return "No student's data was found";
		else while (1) {
			return _mysqlRows[0];
		}
		mysql_free_result(_mysqlResult);
		mysql_close(_mysqlCon);
	}

this is what i have done.
but i just only got jessica as a result

when i change to _mysqlRow[1];
i got 10

but when i try to fetch _mysqlRow[2] to _mysqlRow[5]
the result was null..

but when i try this

__declspec(dllexport) int dataRow(char* sqlStatement) {
		mysql_query(_mysqlCon, sqlStatement);
		_mysqlResult = mysql_store_result(_mysqlCon);
		return mysql_num_rows(_mysqlResult);
		mysql_free_result(_mysqlResult);
		mysql_close(_mysqlCon);
	}

it actually tell me that the row of data was 6..

Posted 23-Aug-17 14:24pm

newbie1992

Updated 23-Aug-17 23:20pm

Add a Solution

Comments

Mohibur Rashid 23-Aug-17 21:58pm

What would happened if you call mysql_fetch_row again before you free and close?
What would eventually happen if you keep doing this?

Say, you have 10 rows. And you did that 11 times, what will be in _mysqlRows?
Please investigate

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Accepted Answer · 2017-08-23T21:12:00

Solution 1

Read the documentation of the used functions and have a look at some example code. Both can be found in the web.

To iterate over the results use something like this (untested):

MYSQL_RES *result = mysql_store_result(mysqlCon);
if (result != NULL) 
{
    int numFields = mysql_num_fields(result);
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result))) 
    { 
        // Row
        for (int i = 0; i < numFields; i++) 
        { 
            // Access field with row[i]
        }
    } 
    mysql_free_result(result);
}
else
{
    // Handle no matching data found
}
mysql_close(mysqlCon);

Some notes:

Don't return from a function without cleanup. In your code the result may be not freed and the connection may be not closed.

You should not use names and defines that begin with an underscore. These are reserved for usage by the compiler implementation.

Posted 23-Aug-17 21:12pm

Jochen Arndt

Comments

newbie1992 24-Aug-17 3:59am

ok sir.. noted with thanks..
btw. sir.. actually i just found what is the problem of my code..
i was doing dll file with c++
it is use to retrieve data from database and return the value back to c#
this is my code:

from c++

__declspec(dllexport) char* retrieveData(char* sqlStatement, int noOfstatement) {
mysql_query(mysqlCon, sqlStatement);
mysqlResult = mysql_store_result(mysqlCon);
if (!(mysqlResult == NULL)) {
while ((mysqlRows = mysql_fetch_row(mysqlResult))) {
printf("%s\t%s\n", mysqlRows[0], mysqlRows[1]);
}
mysql_free_result(mysqlResult);
}
mysql_close(mysqlCon);
}

in c#

[DllImport("C:\\Users\\user\\Desktop\\bookbarConnector\\Debug\\bookbarConnector.dll")]
public static extern intptr retrieveData([MarshalAs(UnmanagedType.LPStr)]string sqlStatement, int noOfstatement);

openConnection();
retrieveData(Marshal.PtrToStingAnsi("select name, ic from studentdatabase where name LIKE 'j%'"), 2);

from the code above i only able to print out one row of data
but after i change the return type of data to void
it finally give 4 rows of data

newbie1992 24-Aug-17 4:01am

in this case, is there any to store the data i got from the query?

my idea is try to push all of the element i got from the querry to a list of char
then slowly pass the string to c#.
is this a good practice? or is there any better recommendation?

Jochen Arndt 24-Aug-17 4:44am

Why do you want to call a C++ DLL instead of using the C# MySQL connector?

It is making all much more complicated. You would have to return a string array which requires proper allocation in the C++ DLL. Doing so will also lose the ability to return non-string data (e.g. dates and floating point values) which are prone to conversion errors.

To avoid the above you would have to return an array of variant data. That would be the "professional" method but is a quite complex task.

newbie1992 24-Aug-17 9:34am

i create this dll file
so that my c# will focus on the User Interface only
and the dll file will act as the engine for this program
is this a bad idea sir?

Jochen Arndt 24-Aug-17 9:53am

It is a bad idea because you are making it complicated and complex. Moving code to a DLL will not make it vanish. It will be still part of your application and most likely not used by any other application.

Just put the database operations into an own C# class.

newbie1992 24-Aug-17 18:57pm

Ok sir.. thanks for the advice