Sign in to follow this  
RonHiler

Problem pulling text from MySQL database

Recommended Posts

RonHiler    214
Hey gang, I'm at a bit of a loss as to why this function is not working. Here is the relevant code:
//---------------------------------------------------------------------------
bool DatabaseClass::GetNewsItem(NewsStruct *NewsEntry, unsigned int Index)
	{
	ostringstream QueryString;
	
	ConnectToDatabase();
	if (Connection == false)
	    {
            Error = mysql_error(mysql);
	    return false;
	    }
	
    QueryString << "select * from news limit " << Index << ",1";
    if (QueryDatabase(QueryString.str()) == false)
	return false;
    Row = mysql_fetch_row(Result);
    if (Row == NULL)
        {
        Error = mysql_error(mysql);
	mysql_free_result(Result);
        DisconnectFromDatabase();
        return false;
        }
    else
	{
	NewsEntry->Timestamp = Row[1];
	NewsEntry->Title = Row[2];
	NewsEntry->Body = Row[3];
	}

    mysql_free_result(Result);
	
    DisconnectFromDatabase();
    return true;
    }

//---------------------------------------------------------------------------
bool DatabaseClass::QueryDatabase(string Query)
    {
    int res;

    res = mysql_real_query(mysql, Query.c_str(), Query.length());
    if (res != 0)
        {
	Error = mysql_error(mysql);
        return false;
        }

    Result = mysql_use_result(mysql);
    if (Result == NULL)
        {
	Error = mysql_error(mysql);
        return false;
        }

    //warning, we do NOT free up Result here.  Do so in the calling routine!
    return true;
    }


The database has a table called 'news' with four columns (NewsIndex, Timestamp, Title, and Body), which are of values INT(primary key), DATETIME, TINYTEXT, and TEXT, respectively. There is one row in the database, which just contains some test data that I threw in. As you can see from the code, I'm trying to pull the last three columns of that row into my NewsEntry structure: { NewsEntry->Timestamp = Row[1]; NewsEntry->Title = Row[2]; NewsEntry->Body = Row[3]; } The program gets to the first line of that block and pulls timestamp just fine, but then it gets to the second line and throws an exception because Row[2] is a bad pointer. Am I doing something wrong? Is there somethng special I need to do to pull text columns out of a database?

Share this post


Link to post
Share on other sites
RonHiler    214
Quote:
Original post by Kylotan
I doubt that's actually the problem! Perhaps it was because you're not handling a null value?


There were no NULL values in the database, if that's what you are getting at. And I think I covered the error handling in the code (but feel free to check it if you are doubting, maybe I missed something).

I don't know why the 'select *' didn't work for the text values. It DID pull integer values just fine, but gave bad pointers for the text values. Certainly I'm not an expert with mySQL database commands, so perhaps I messed something else up. But here is what I discovered:

Given the code and database setup I showed in the OP, this query:

select * from news limit 0, 1

gave good data in Row[0] and Row[1] (the index and timestamp values), but bad pointers in Row[2] and Row[3] (title and body, both text fields). Trying to read either of these values caused an exception.

By contrast, this query

select Title from news limit 0,1

with NO other changes in the code or database, gave the title entry from the database just fine (in Row[0], naturally). And similarly for

select Body from news limit 0,1

So if you have another explanation, I'm all ears. To me, it seems pretty conclusive that 'select *' simply won't pull text rows, you have to explicitely grab them by name.

Share this post


Link to post
Share on other sites
ApochPiQ    23005
Erm, if MySQL didn't allow directly pulling strings out of the database, there would be hell to pay [wink] So I'm going to have to agree with Kylotan that this is probably not your actual problem.

When you say Row[2] is a bad pointer, what kind of value is in there? NULL? 0xffffffff? Some other combination of hex/gibberish/etc.? Consider using mysql_num_fields() to determine if you're reading too many fields in the returned row. Also, note that if you don't copy the string values from the row, when you call mysql_free_result() you can no longer safely access those pointers. I don't know the definition of your NewsEntry class/struct, so I can't say for sure, if you're just storing char* fields in NewsEntry, I'd suggest upgrading to std::strings.

Share this post


Link to post
Share on other sites
Atrix256    539
I agree with Kylotan, select * with text entries should be just fine (:

What are the columns of the table you are querying?

I'll bet you the problem is just that you weren't getting the columns you thought you were.

For instance, lets say my table has the following columns:

MyNumberA, MyTimeStampA, MyTextA, MyNumberB, MyTextB

if you do a select like this:

Select MyTextA,MyTextB,MyNumberA from MyTable

then...

Row[0] == MyTextA
Row[1] == MyTextB
Row[2] == MyNumberA

and if you follow that in your code it works fine.

if you change it to this:

select * from MyTable

and still assume that Row [0], [1] and [2] are the same things...

Row[0] will **APPEAR** to be a bad pointer when in fact, it's not a pointer at all, just a number. In this case, Row[0] is the column "MyNumberA".

I'll bet you (2:1 odds!) that this is the problem you hit (:

Share this post


Link to post
Share on other sites
RonHiler    214
Alright, I believe you guys, but then I have no idea what is going wrong with the 'select *' code.

This is NewsStruct, since someone asked:

struct NewsStruct
{
string Title;
string Timestamp;
string Body;
};

very simple.

The database, as I've already said, is set up like such:

Quote:

The database has a table called 'news' with four columns (NewsIndex, Timestamp, Title, and Body), which are of values INT(primary key), DATETIME, TINYTEXT, and TEXT, respectively. There is one row in the database, which just contains some test data that I threw in.


So, given the code that I posted in the OP, why is this crashing?:

else
{
NewsEntry->Timestamp = Row[1];
NewsEntry->Title = Row[2];
NewsEntry->Body = Row[3];
}

at the second line of the block (NewsEntry->Title = Row[2];) when I do a

"select * from news limit 0, 1"

Am I accessing the Row[2] improperly?

[And sorry ApochPiQ, I don't have the program right here in front of me or I'd tell you what was in Row[2]. I want to say it contained 0x7fffffff, but don't quote me on that].

Share this post


Link to post
Share on other sites
Codeka    1239
The strings returned from mysql_fetch_row are not NULL terminated, they are counted. That means you can't just say "std::string str = row[0]" or whatever and expect it to work in all situations. You need to use mysql_fetch_lengths to get the length of the string and copy them yourself. For example:

int num_fields = mysql_num_fields(Result);

MYSQL_ROW row = mysql_fetch_row(Result);
unsigned long *lengths = mysql_fetch_lengths(Result);
for(i = 0; i < num_fields; i++)
{
std::string value = std::string(row[i], lengths[i]);

// use value...
}

Also,
Quote:
Original post by RonHiler
The database has a table called 'news' with four columns (NewsIndex, Timestamp, Title, and Body), which are of values INT(primary key), DATETIME, TINYTEXT, and TEXT, respectively. There is one row in the database, which just contains some test data that I threw in.
The reason SELECT * is a bad idea is because you don't always know the order that it's going to return the fields in. I believe MySQL will return fields in the order they're defined in the database (that is, the order you specify in your CREATE TABLE statement) but if you go and change the order of fields (or add a new field in the middle, for example) that's going to screw up all code that references that table.

That's why it's always best to explicitly select the fields you want,
SELECT Timestamp, Title, Body FROM news
This way, if you ever reorder the fields in your table, you don't have to go searching for every place in your code that references that table.

The other benefit of doing this is you reduce the network traffic required to return the result: MySQL doesn't need to send you the NewsIndex field if you don't ask for it. It's probably not a big deal for a small integer column, but if you had a larger column, it could make a big difference.

Share this post


Link to post
Share on other sites
RonHiler    214
Quote:
Original post by Codeka
The strings returned from mysql_fetch_row are not NULL terminated, they are counted. That means you can't just say "std::string str = row[0]" or whatever and expect it to work in all situations.

Ahhhh, I didn't know that. Thanks Codeka, that's probably what the issue was. I'll give that a try.

Quote:
That's why it's always best to explicitly select the fields you want,
SELECT Timestamp, Title, Body FROM news
This way, if you ever reorder the fields in your table, you don't have to go searching for every place in your code that references that table.

Okay, sounds like good advice, I will do it that way, thanks for the tip.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this