Watch, Follow, &
Connect with Us

Welcome, Guest
Guest Settings
Help

Thread: Multiple fields in query don't work in C++ Builder 6


This question is not answered. Helpful answers available: 1. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Sep 7, 2017 9:42 AM Last Post By: Jeff Overcash (... Threads: [ Previous | Next ]
Georgios Altint...

Posts: 2
Registered: 3/1/10
Multiple fields in query don't work in C++ Builder 6  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 6, 2017 1:59 AM
Hello,

I am using C++ Builder 6 and I want to query more than one field/column in my query to a table of my MySQL database.

Let's say, I have a table named "users" and my table has the fields "id", "name", "username" and "password". Please, check the following examples:

query = "SELECT name FROM users;"; // WORKS
query = "SELECT name, username FROM users;"; // WORKS ONLY FOR THE 1ST FIELD "name"
query = "SELECT * FROM users;"; // DOESN'T WORK: GIVES ME EAccessViolation
query = "SELECT name FROM users UNION SELECT username FROM users;"; // WORKS BUT IT ISN'T A SOLUTION

So far, the rest of my code is almost the same I found in this [guide|http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Tutorial:_Connecting_to_a_SQLite_Database_from_a_VCL_Application].

Could I query more than 1 field at the same time?

FULL CODE:

String query;

outputMemo->ClearSelection();

// PROBLEMATIC QUERY !!!
query = "SELECT * FROM users;";

try {
SQLQuery1->SQL->Text = query;
SQLQuery1->Active = true;
}
catch (Exception& E) {
outputMemo->Text = "Exception raised with message" + E.Message;
}

// Show the results of the query in a TMemo control.

TStringList *list;
TField *currentField;
String currentLine;

if (!SQLQuery1->IsEmpty()) {
SQLQuery1->First();
list = new TStringList;
__try {
SQLQuery1->GetFieldNames(list);

while (!SQLQuery1->Eof) {
currentLine = "";
for (int i=0; i<list->Count; i++) {
currentField = SQLQuery1->FieldByName(list->Strings[i]);
currentLine = currentLine + " " + currentField->AsString;
}

outputMemo->Lines->Add( currentLine.c_str() );
SQLQuery1->Next();
}
}
catch (Exception& E) {
outputMemo->Text = "Exception raised with message" + E.Message;
}

list->Free();
}

Thank you in advance for your help and your time.

Edited by: Georgios Altintzis on Sep 6, 2017 6:55 AM

Jeff Overcash (...

Posts: 1,398
Registered: 9/23/99
Re: Multiple fields in query don't work in C++ Builder 6 [Edit]
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 6, 2017 6:00 PM   in response to: Georgios Altint... in response to: Georgios Altint...
Georgios Altintzis wrote:
Hello,

I am using C++ Builder 6 and I want to query more than one field/column in my query to a table of my MySQL database.

Let's say, I have a table named "users" and my table has the fields "id", "name", "username" and "password". Please, check the following examples:

query = "SELECT name FROM users;"; // WORKS
query = "SELECT name, username FROM users;"; // WORKS ONLY FOR THE 1ST FIELD "name"
query = "SELECT * FROM users;"; // DOESN'T WORK: GIVES ME EAccessViolation
query = "SELECT name FROM users UNION SELECT username FROM users;"; // WORKS BUT IT ISN'T A SOLUTION

So far, the rest of my code is almost the same I found in this [guide|http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Tutorial:_Connecting_to_a_SQLite_Database_from_a_VCL_Application].

Could I query more than 1 field at the same time?

FULL CODE:

String query;

outputMemo->ClearSelection();

// PROBLEMATIC QUERY !!!
query = "SELECT * FROM users;";

try {
SQLQuery1->SQL->Text = query;
SQLQuery1->Active = true;
}
catch (Exception& E) {
outputMemo->Text = "Exception raised with message" + E.Message;
}

// Show the results of the query in a TMemo control.

TStringList *list;
TField *currentField;
String currentLine;

if (!SQLQuery1->IsEmpty()) {
SQLQuery1->First();
list = new TStringList;
__try {
SQLQuery1->GetFieldNames(list);

while (!SQLQuery1->Eof) {
currentLine = "";
for (int i=0; i<list->Count; i++) {
currentField = SQLQuery1->FieldByName(list->Strings[i]);
currentLine = currentLine + " " + currentField->AsString;
}

outputMemo->Lines->Add( currentLine.c_str() );
SQLQuery1->Next();
}
}
catch (Exception& E) {
outputMemo->Text = "Exception raised with message" + E.Message;
}

list->Free();
}

Thank you in advance for your help and your time.

Edited by: Georgios Altintzis on Sep 6, 2017 6:55 AM


This all looks like you have a persisted field on your query for name. If you
persist fields at design time you can't access any fields other than the
persisted one.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Georgios Altint...

Posts: 2
Registered: 3/1/10
Re: Multiple fields in query don't work in C++ Builder 6 [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 6:27 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thanks for the direction. Do you propose to change any properties of my query somewhere in the code or in the Object Inspector?
Jeff Overcash (...

Posts: 1,398
Registered: 9/23/99
Re: Multiple fields in query don't work in C++ Builder 6 [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 9:42 AM   in response to: Georgios Altint... in response to: Georgios Altint...
Georgios Altintzis wrote:
Thanks for the direction. Do you propose to change any properties of my query somewhere in the code or in the Object Inspector?

If you are changing the query's SQL and the columns are not matching the
persisted fields then do not persist the fields (remove them through the OI).
Persisted fields require that changed SQL have at least the field names that
were persisted or less you will get a column not found type error. If you add
additional columns to your SQL you will not be able to access them.

Newer versions have a property (FieldOptions) to allow for adding additional
fields to the persisted if missing, but BCB6 does not have that feature.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02