//---------------------------------------------------------------------------
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;
}
Problem pulling text from MySQL database
Author
Hey gang, I'm at a bit of a loss as to why this function is not working. Here is the relevant code:
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?
Author
Okay, I got it. I guess you can't use 'select *' when pulling text entries from a database, you have to be explicit, e.g. 'select Body'.
I doubt that's actually the problem! Perhaps it was because you're not handling a null value?
Author
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.
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.
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.
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 (:
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 (:
Author
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:
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].
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].
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:
Also,
That's why it's always best to explicitly select the fields you want,
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.
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, lengths); // use value...}Also,
Quote:Original post by RonHilerThe 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.
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.
That's why it's always best to explicitly select the fields you want,
SELECT Timestamp, Title, Body FROM newsThe 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.
Author
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,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.SELECT Timestamp, Title, Body FROM news
Okay, sounds like good advice, I will do it that way, thanks for the tip.
Quote:I'll bet you (2:1 odds!) that this is the problem you hit (:
Darn i was wrong, i owe you 2 internets now!
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
