 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Greg Gailer
Guest
|
 Posted: Thu Oct 26, 2006 8:37 am Post subject: SQL won't work |
 |
|
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au |
|
| Back to top |
|
 |
Dodgy
Guest
|
| Posted: Thu Oct 26, 2006 1:45 pm Post subject: Re: SQL won't work |
|
|
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
<g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
| Quote: | Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
|
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES |
|
| Back to top |
|
|
Greg Gailer
Guest
|
| Posted: Thu Oct 26, 2006 2:56 pm Post subject: Re: SQL won't work |
|
|
Dodgy wrote:
| Quote: | On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
Thanks for the reply, |
I tried what you suggested and it still doesn't work. It should, as far
as I know, send the query when I close and then open the query but it
doesn't seem to be doing it from the code. It does , however, do it from
the actual component, so I must be missing something in my code. I am
very confused!
Greg |
|
| Back to top |
|
|
pr
Guest
|
| Posted: Thu Oct 26, 2006 3:34 pm Post subject: Re: SQL won't work |
|
|
"Greg Gailer" <g_gailer (AT) yahoo (DOT) com.au> wrote in message
news:12k11jj3h2nvl34 (AT) corp (DOT) supernews.com...
| Quote: | Dodgy wrote:
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
Thanks for the reply,
I tried what you suggested and it still doesn't work. It should, as far as
I know, send the query when I close and then open the query but it doesn't
seem to be doing it from the code. It does , however, do it from the
actual component, so I must be missing something in my code. I am very
confused!
|
Try without the semicolon at the end.
PR |
|
| Back to top |
|
|
Dodgy
Guest
|
| Posted: Thu Oct 26, 2006 6:09 pm Post subject: Re: SQL won't work |
|
|
On Thu, 26 Oct 2006 19:56:48 +1000, Greg Gailer
<g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
| Quote: | Dodgy wrote:
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
Thanks for the reply,
I tried what you suggested and it still doesn't work. It should, as far
as I know, send the query when I close and then open the query but it
doesn't seem to be doing it from the code. It does , however, do it from
the actual component, so I must be missing something in my code. I am
very confused!
Greg
|
You don't say what query component you are using.
Oh hang on, I just saw an acronym I missed earlier.
You said "BDE"
I should have run away screaming at that point...
Dump it... Loose it... It never caused me anything but trouble.
Use standard TADOConnection and TADOQuery (Came in D5 and later I
believe).
TADOConnection will happily connect to the MySQL ODBC connector. (IIRC
Just don't give it an initial catalogue).
Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES |
|
| Back to top |
|
|
Dodgy
Guest
|
| Posted: Thu Oct 26, 2006 6:10 pm Post subject: Re: SQL won't work |
|
|
On Thu, 26 Oct 2006 12:34:08 +0200, "pr" <pr (AT) telkom (DOT) net> waffled on
about something:
| Quote: |
"Greg Gailer" <g_gailer (AT) yahoo (DOT) com.au> wrote in message
news:12k11jj3h2nvl34 (AT) corp (DOT) supernews.com...
Dodgy wrote:
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
Thanks for the reply,
I tried what you suggested and it still doesn't work. It should, as far as
I know, send the query when I close and then open the query but it doesn't
seem to be doing it from the code. It does , however, do it from the
actual component, so I must be missing something in my code. I am very
confused!
Try without the semicolon at the end.
PR
|
LOL!
I didn't see that even when I did my edit on his code... would that
really cause all this bother? I would have expected some kind of
exception to be thrown.
Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES |
|
| Back to top |
|
|
Dan
Guest
|
| Posted: Fri Oct 27, 2006 5:56 am Post subject: Re: SQL won't work |
|
|
I don't see anything wrong either. Here is a handy way to keep from
beating the wrong bush:
showmesssage(query.sql.text);
place this just before query.open to see what your code is actually
sending to the database engine. And, when it shows, be sure to read it
very carefully. We all tend to see what we want to see ;-)
Dan
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
<g_gailer (AT) yahoo (DOT) com.au> wrote:
| Quote: | Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au |
|
|
| Back to top |
|
|
Greg Gailer
Guest
|
| Posted: Fri Oct 27, 2006 8:06 am Post subject: Re: SQL won't work |
|
|
Thanks Dan, but I have done that already and can find no error in the
actual SQL statement. I have some MySQL programs to test the statements
and it works OK from them. The actual component must be working OK too
because it orders the data correctly according to the basic SQL
statement I have put into the component. I don't think that the code is
actually sending the SQL statement to the database but can't find
anything that would stop it. Its getting quite frustrating!! Everything
else is working except the query.
Greg
Dan wrote:
| Quote: | I don't see anything wrong either. Here is a handy way to keep from
beating the wrong bush:
showmesssage(query.sql.text);
place this just before query.open to see what your code is actually
sending to the database engine. And, when it shows, be sure to read it
very carefully. We all tend to see what we want to see ;-)
Dan
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> wrote:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
|
|
|
| Back to top |
|
|
Greg Gailer
Guest
|
| Posted: Fri Oct 27, 2006 8:13 am Post subject: Re: SQL won't work |
|
|
Dodgy wrote:
| Quote: | On Thu, 26 Oct 2006 19:56:48 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Dodgy wrote:
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
Thanks for the reply,
I tried what you suggested and it still doesn't work. It should, as far
as I know, send the query when I close and then open the query but it
doesn't seem to be doing it from the code. It does , however, do it from
the actual component, so I must be missing something in my code. I am
very confused!
Greg
You don't say what query component you are using.
Oh hang on, I just saw an acronym I missed earlier.
You said "BDE"
I should have run away screaming at that point...
Dump it... Loose it... It never caused me anything but trouble.
Use standard TADOConnection and TADOQuery (Came in D5 and later I
believe).
TADOConnection will happily connect to the MySQL ODBC connector. (IIRC
Just don't give it an initial catalogue).
Dodgy.
|
Well, that was a good thought. I changed the query as you said. I
dropped a TADOConnection, got it to connect, used a TADOQuery,
DatasetProvider, ClientDataset and then a DataSource on the form.
Connected them all together and checked that my basic SQL was working
again and then tried the code again. Still no success I'm afraid. Is
there anything else that can stop the query from accessing the database?
It seems like the basic component SQL statement is working but the code
isn't. There must be something disabling the query.
Greg |
|
| Back to top |
|
|
pr
Guest
|
| Posted: Fri Oct 27, 2006 5:56 pm Post subject: Re: SQL won't work |
|
|
"Greg Gailer" <g_gailer (AT) yahoo (DOT) com.au> wrote in message
news:12k2ttgge2lgl6b (AT) corp (DOT) supernews.com...
| Quote: | Thanks Dan, but I have done that already and can find no error in the
actual SQL statement. I have some MySQL programs to test the statements
and it works OK from them. The actual component must be working OK too
because it orders the data correctly according to the basic SQL statement
I have put into the component. I don't think that the code is actually
sending the SQL statement to the database but can't find anything that
would stop it. Its getting quite frustrating!! Everything else is working
except the query.
|
I have written a function by which I can save the actual query
text to a disk file, open it with notepad and then copy and
paste into some tool and execute it.
Query.sql.SavetoFile( filename ); should do it in your case.
In my case I use Firebird/IB_SQL as a tool, in your case you
may be able to use the Database Explorer.
Once I have the query text in the tool I can easily manipulate
it and see the result of the manipulation without having to go
through the compilation process and run the program.
This approach has saved me a massive amount of time and
frustration especially when queries are constructed dynamically
at run time.
PR |
|
| Back to top |
|
|
Greg Gailer
Guest
|
| Posted: Sat Oct 28, 2006 4:49 am Post subject: Re: SQL won't work |
|
|
pr wrote:
| Quote: | "Greg Gailer" <g_gailer (AT) yahoo (DOT) com.au> wrote in message
news:12k2ttgge2lgl6b (AT) corp (DOT) supernews.com...
Thanks Dan, but I have done that already and can find no error in the
actual SQL statement. I have some MySQL programs to test the statements
and it works OK from them. The actual component must be working OK too
because it orders the data correctly according to the basic SQL statement
I have put into the component. I don't think that the code is actually
sending the SQL statement to the database but can't find anything that
would stop it. Its getting quite frustrating!! Everything else is working
except the query.
I have written a function by which I can save the actual query
text to a disk file, open it with notepad and then copy and
paste into some tool and execute it.
Query.sql.SavetoFile( filename ); should do it in your case.
In my case I use Firebird/IB_SQL as a tool, in your case you
may be able to use the Database Explorer.
Once I have the query text in the tool I can easily manipulate
it and see the result of the manipulation without having to go
through the compilation process and run the program.
This approach has saved me a massive amount of time and
frustration especially when queries are constructed dynamically
at run time.
PR
Thanks, I will try what you've suggested, but I don't think the SQL |
statement is the problem. I have put a DataGrid on a form and changed
the SQL statement in the component and everything comes up as it should
in the DataGrid. I have done this numerous times with different SQL
statements with absolutely no problems, but when I execute them in code
the output doesn't change, almost as if the SQL statement was never
sent. Is there something else that I need to include in code to make the
SQL work? I have used local DBASE files before like this with no
problems but this MySQL query is causing lots of headaches.
Greg |
|
| Back to top |
|
|
Dan
Guest
|
| Posted: Sun Oct 29, 2006 10:21 pm Post subject: Re: SQL won't work |
|
|
<snip>
| Quote: | I have done this numerous times with different SQL
statements with absolutely no problems, but when I execute them in code
the output doesn't change, almost as if the SQL statement was never
sent. Is there something else that I need to include in code to make the
SQL work? I have used local DBASE files before like this with no
problems but this MySQL query is causing lots of headaches.
Greg
Maybe it is doing what you want, but you are not seeing the right |
data. I would review the transactions, commits, refresh, and things in
that area. The isolation level setting can make a big difference in
when changes become visible.
hth,
Dan |
|
| Back to top |
|
|
Dodgy
Guest
|
| Posted: Tue Oct 31, 2006 4:58 pm Post subject: Re: SQL won't work |
|
|
On Fri, 27 Oct 2006 13:13:50 +1000, Greg Gailer
<g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
| Quote: | Dodgy wrote:
On Thu, 26 Oct 2006 19:56:48 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Dodgy wrote:
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
Thanks for the reply,
I tried what you suggested and it still doesn't work. It should, as far
as I know, send the query when I close and then open the query but it
doesn't seem to be doing it from the code. It does , however, do it from
the actual component, so I must be missing something in my code. I am
very confused!
Greg
You don't say what query component you are using.
Oh hang on, I just saw an acronym I missed earlier.
You said "BDE"
I should have run away screaming at that point...
Dump it... Loose it... It never caused me anything but trouble.
Use standard TADOConnection and TADOQuery (Came in D5 and later I
believe).
TADOConnection will happily connect to the MySQL ODBC connector. (IIRC
Just don't give it an initial catalogue).
Dodgy.
Well, that was a good thought. I changed the query as you said. I
dropped a TADOConnection, got it to connect, used a TADOQuery,
DatasetProvider, ClientDataset and then a DataSource on the form.
Connected them all together and checked that my basic SQL was working
again and then tried the code again. Still no success I'm afraid. Is
there anything else that can stop the query from accessing the database?
It seems like the basic component SQL statement is working but the code
isn't. There must be something disabling the query.
Greg
|
Did you see pr's comment about you having a semi-colon on the end of
your sql query? I didn't even see it at first, so I even ended up with
it pasted into my code!
Try
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice');
Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES |
|
| Back to top |
|
|
Greg Gailer
Guest
|
| Posted: Thu Nov 02, 2006 5:07 pm Post subject: Re: SQL won't work |
|
|
Dodgy wrote:
| Quote: | On Fri, 27 Oct 2006 13:13:50 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Dodgy wrote:
On Thu, 26 Oct 2006 19:56:48 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Dodgy wrote:
On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
g_gailer (AT) yahoo (DOT) com.au> waffled on about something:
Greetings all,
I have a MySQL database running on a Linux server and wanting to access
it from Windows. I have the database connection all working correctly. I
am using the tables and the query from the BDE components and can edit
the database perfectly through the tables but am having no success in
the code for the query. The query component has a basic query that works
well in the component itself.
select * from Service
order by CustNum,Invoice;
In code, I execute another query to isolate just one person as follows:
with Query do begin
if Active then Close;
if Prepared then Unprepare;
SQL.Clear;
SQL.Add('select * from Service');
SQL.Add('where CustNum=9');
SQL.Add('order by Invoice;');
Prepare;
Open;
end;
I was hoping to get all the records for customer number 9 from this
query but I get the records of all customers ordered as before. This
seems to suggest that the query is not working at all. I have used this
sort of query before for local DBase files with no problems. What am I
doing wrong now? Can anyone please help?
Greg
g_gailer (AT) yahoo (DOT) com.au
I must confess to never having used the Tstring methods of the SQL
property, and as your query seems to be acting as if only the first
line is being executed, that's the first thing that comes to my
attention.
Have you tried...
with Query do begin
if Active then Close;
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice;');
Open;
end;
Dodgy.
Thanks for the reply,
I tried what you suggested and it still doesn't work. It should, as far
as I know, send the query when I close and then open the query but it
doesn't seem to be doing it from the code. It does , however, do it from
the actual component, so I must be missing something in my code. I am
very confused!
Greg
You don't say what query component you are using.
Oh hang on, I just saw an acronym I missed earlier.
You said "BDE"
I should have run away screaming at that point...
Dump it... Loose it... It never caused me anything but trouble.
Use standard TADOConnection and TADOQuery (Came in D5 and later I
believe).
TADOConnection will happily connect to the MySQL ODBC connector. (IIRC
Just don't give it an initial catalogue).
Dodgy.
Well, that was a good thought. I changed the query as you said. I
dropped a TADOConnection, got it to connect, used a TADOQuery,
DatasetProvider, ClientDataset and then a DataSource on the form.
Connected them all together and checked that my basic SQL was working
again and then tried the code again. Still no success I'm afraid. Is
there anything else that can stop the query from accessing the database?
It seems like the basic component SQL statement is working but the code
isn't. There must be something disabling the query.
Greg
Did you see pr's comment about you having a semi-colon on the end of
your sql query? I didn't even see it at first, so I even ended up with
it pasted into my code!
Try
SQL.text:='select * from Service '
+'where CustNum=9 '
+'order by Invoice');
Dodgy.
|
I have tried it with and without the semicolon but neither works. I have
tried both SQL.Text and SQL.Add. I have tried BDE, dbExpress, dbGo
components and the trial version of mySQLDAC and they all do the same
thing. It looks to me now like nothing can access the database correctly
so I will try in Visual Basic and see how that goes. Thanks for your
help and your time.
Greg |
|
| Back to top |
|
|
Greg Gailer
Guest
|
| Posted: Thu Nov 02, 2006 5:10 pm Post subject: Re: SQL won't work |
|
|
Dan wrote:
| Quote: | snip
I have done this numerous times with different SQL
statements with absolutely no problems, but when I execute them in code
the output doesn't change, almost as if the SQL statement was never
sent. Is there something else that I need to include in code to make the
SQL work? I have used local DBASE files before like this with no
problems but this MySQL query is causing lots of headaches.
Greg
Maybe it is doing what you want, but you are not seeing the right
data. I would review the transactions, commits, refresh, and things in
that area. The isolation level setting can make a big difference in
when changes become visible.
hth,
Dan
|
Thanks Dan, I have double checked all those things and all seems to be
OK. I have tried the BDE, dbExpress, dbGo and even the trial version of
mySQLDAC and every one of them works the same (no access from code). I
have become so frustrated that I am now going to discard the project and
try it in Visual Basic. Thanks for all your help and time.
Greg |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
