Re: [lazarus] SQL Statement
There is no problem with the error message. Did a quick test and it returns Cannot open a non-select statement - so all is fine. Regards, - Graeme - -- There's no place like 127.0.0.1 _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
RE: [lazarus] SQL Statement
Thank you for your time and directions.
Guidelines provided by you are very important to me and will be followed.
I totally agree with you and I believe I respect members, I first dig into
information resources on the net before bother list members, check list
archive, but sometimes do not compose query effectively. Please give people
time to learn and improve themselves, don't shoot :-).
Cause now I still don't know if it's a bug or not.
No, it is not a bug. Non-Select statements need .ExecSQL method, not .open. I
had a couple of replies on this, tested and confirmed.
Regards,
Panagiotis
-Original Message-
From: Joost van der Sluis [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 16, 2006 1:03 PM
To: lazarus@miraclec.com
Subject: Re: [lazarus] SQL Statement
What I missed in this question, and the one before, is the error-message you've
got. It should be: 'Cannot open a non-select statement'
If you had asked the question: What do I do wrong when I got: 'Cannot open a
non-select statement', everyone here with some sql-knowledge would have known
the answer. Then no other information was even needed, and we didn't have to
dig into your code, looking at the quotes, wondering what they are and such.
A quote from the link i've send:
-
Q:
My {program, configuration, SQL statement} doesn't work
A:
This is not a question, and I'm not interested in playing Twenty Questions to
pry your actual question out of you — I have better things to do. On seeing
something like this, my reaction is normally of one of the following:
* do you have anything else to add to that?
* oh, that's too bad, I hope you get it fixed.
* and this has exactly what to do with me?
-
Next time, describe accurately what happens. Cause now I still don't know if
it's a bug or not. Can you tell me if the program gave the error i told about
above? If not, it's a bug. If it does, I need to clarify the error-message a
bit.
On Wed, 2006-03-15 at 22:32 +, Panagiotis Sidiropoulos wrote:
It is not my intention being irritating, on the contrary. Composing
this post I thought that subject was describing the problem, focusing
on the SQL statement syntax and not to the code. I apologise. Your
answer gives me the impression that you are tired reading this kind of
not well described posts from me. I do not feel this is true, I only
had another such comment once at the past in a case not more
information was available than provided.
What I can promise is to try harder and read url with directions
suggested. I also ask you to be patient in such cases, you see it is
an international meeting point at which people with different cultures
meet communicating in English, not mother language for many of us.
So, please let me provide full code. While executing, code exit at
.open point. Trying a test SELECT statement it is OK. If you can think
anythink to direct me where to search it would be most helpful to me.
// update ID data
function mgspsUpdateIDData: integer;
var
nLicensesLeft: integer;
cCustID: string;
begin
// update
Result := MGSPS_ERROR_UPDATE_FAILED;
nLicensesLeft :=
SQLQuery.FieldByName( 'ids-licenses-left' ).AsInteger - 1;
cCustID := SQLQuery.FieldByName( 'ids-internet-id' ).AsString;
SQLQuery.Close;
SQLQuery.SQL.Clear;
SQLQuery.SQL.Add( 'update `ids-aa`' );
SQLQuery.SQL.Add( ' set `ids-licenses-left`=1' );
SQLQuery.SQL.Add( ' where
`ids-internet-id`=aa-123456789012345678' );
SQLQuery.Open;
Result := MGSPS_ALL_OK;
end;
An why all these quotes arount the field names? Does that even
compile?
These quotes are necessary maybe because of field names form, dash
character is used separating parts of names making these more
readable. I tried it without quotes and had dificulties. This way
works fine, I have Select statements executed normally but not this
Update which I think is in a general accepted form.
As I wrote on my complementary reply, this is running on Linux using
SQLDB components and MySQL database server.
Panagiotis
Στις 15-03-2006, ημέρα Τετ, και ώρα 20:27 +0100, ο/η Joost van der
Sluis
έγραψε:
This is gettig irritating.
'statement is not accepted'
Is that the error message? 'statement not accepted' You can't expect
us to keep answering this kind of questions.
An why all these quotes arount the field names? Does that even
compile?
And read this, before you sent another question:
www.catb.org/~esr/faqs/smart-questions.html
On Wed, 2006-03-15 at 21:14 +, Panagiotis Sidiropoulos wrote:
I use following code to update a table. Connection is succesfuly
carried out and before this, Select statement executed succesfuly.
Problem is that this statement is not accepted. I checked this
same statement through an MySQL client and it was executed
properly. Can anyone please advise
RE: [lazarus] SQL Statement
Cause now I still don't know if it's a bug or not. No, it is not a bug. Non-Select statements need .ExecSQL method, not .open. I had a couple of replies on this, tested and confirmed. I'm still curious if you've got the error message 'Cannot open a non- select statement' or not. (when you're using open) Joost _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
RE: [lazarus] SQL Statement
I'm still curious if you've got the error message 'Cannot open a non- select statement' or not. (when you're using open) Νο, I did not get such message. Just a while ago, Graeme replied reporting this message Cannot open a non-select statement which made me wonder why I did not, so, checked again my application and relized that my function is called from inside a try..end structure which is not ready to handle exceptions. This simple thing started all this conversation, blame me. Now I'm seeking to find how to handle exceptions my own. Did you realy think that I could bother list members with such a question if getting this clear message from my application? :-) Panagiotis -Original Message- From: Joost van der Sluis [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 12:33 PM To: lazarus@miraclec.com Subject: RE: [lazarus] SQL Statement Cause now I still don't know if it's a bug or not. No, it is not a bug. Non-Select statements need .ExecSQL method, not .open. I had a couple of replies on this, tested and confirmed. I'm still curious if you've got the error message 'Cannot open a non- select statement' or not. (when you're using open) Joost _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
What I missed in this question, and the one before, is the error-message
you've got. It should be: 'Cannot open a non-select statement'
If you had asked the question: What do I do wrong when I got: 'Cannot
open a non-select statement', everyone here with some sql-knowledge
would have known the answer. Then no other information was even needed,
and we didn't have to dig into your code, looking at the quotes,
wondering what they are and such.
A quote from the link i've send:
-
Q:
My {program, configuration, SQL statement} doesn't work
A:
This is not a question, and I'm not interested in playing Twenty
Questions to pry your actual question out of you — I have better things
to do. On seeing something like this, my reaction is normally of one of
the following:
* do you have anything else to add to that?
* oh, that's too bad, I hope you get it fixed.
* and this has exactly what to do with me?
-
Next time, describe accurately what happens. Cause now I still don't
know if it's a bug or not. Can you tell me if the program gave the error
i told about above? If not, it's a bug. If it does, I need to clarify
the error-message a bit.
On Wed, 2006-03-15 at 22:32 +, Panagiotis Sidiropoulos wrote:
It is not my intention being irritating, on the contrary. Composing this
post I thought that subject was describing the problem, focusing on the
SQL statement syntax and not to the code. I apologise. Your answer gives
me the impression that you are tired reading this kind of not well
described posts from me. I do not feel this is true, I only had another
such comment once at the past in a case not more information was
available than provided.
What I can promise is to try harder and read url with directions
suggested. I also ask you to be patient in such cases, you see it is an
international meeting point at which people with different cultures meet
communicating in English, not mother language for many of us.
So, please let me provide full code. While executing, code exit at .open
point. Trying a test SELECT statement it is OK. If you can think
anythink to direct me where to search it would be most helpful to me.
// update ID data
function mgspsUpdateIDData: integer;
var
nLicensesLeft: integer;
cCustID: string;
begin
// update
Result := MGSPS_ERROR_UPDATE_FAILED;
nLicensesLeft :=
SQLQuery.FieldByName( 'ids-licenses-left' ).AsInteger - 1;
cCustID := SQLQuery.FieldByName( 'ids-internet-id' ).AsString;
SQLQuery.Close;
SQLQuery.SQL.Clear;
SQLQuery.SQL.Add( 'update `ids-aa`' );
SQLQuery.SQL.Add( ' set `ids-licenses-left`=1' );
SQLQuery.SQL.Add( ' where
`ids-internet-id`=aa-123456789012345678' );
SQLQuery.Open;
Result := MGSPS_ALL_OK;
end;
An why all these quotes arount the field names? Does that even compile?
These quotes are necessary maybe because of field names form, dash
character is used separating parts of names making these more readable.
I tried it without quotes and had dificulties. This way works fine, I
have Select statements executed normally but not this Update which I
think is in a general accepted form.
As I wrote on my complementary reply, this is running on Linux using
SQLDB components and MySQL database server.
Panagiotis
Στις 15-03-2006, ημέρα Τετ, και ώρα 20:27 +0100, ο/η Joost van der Sluis
έγραψε:
This is gettig irritating.
'statement is not accepted'
Is that the error message? 'statement not accepted' You can't expect us
to keep answering this kind of questions.
An why all these quotes arount the field names? Does that even compile?
And read this, before you sent another question:
www.catb.org/~esr/faqs/smart-questions.html
On Wed, 2006-03-15 at 21:14 +, Panagiotis Sidiropoulos wrote:
I use following code to update a table. Connection is succesfuly carried
out and before this, Select statement executed succesfuly. Problem is
that this statement is not accepted. I checked this same statement
through an MySQL client and it was executed properly. Can anyone please
advise?
SQLQuery.Close;
SQLQuery.SQL.Clear;
SQLQuery.SQL.Add( 'update `ids-aa`' );
SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' );
SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' );
showmessage( SQLQuery.SQL.Text );
SQLQuery.Open;
I also tested this statement a one line setting this to .text property.
Panagiotis
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at
Re: [lazarus] SQL Statement
SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); All I can think of, is to make sure you leave spaces between the different line, so when that get put together, they still make sence. If I split a Sql statement into seperate lines, I normall start ever line with a space. eg: SQLQuery.SQL.Add( 'where ... becomes ... SQLQuery.SQL.Add( ' where SQLQuery.Open; Not sure what components you use, so this may differ from what I use. The statement you are trying to run is a Update statement which doesn't return a value, so normally needs to be executed instead of Opened... eg: SQLQuery.Execute; Select statements would use the SQLQuery.Open; As I said, this could be different for certain components. Regards, - Graeme - _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
This is gettig irritating. 'statement is not accepted' Is that the error message? 'statement not accepted' You can't expect us to keep answering this kind of questions. An why all these quotes arount the field names? Does that even compile? And read this, before you sent another question: www.catb.org/~esr/faqs/smart-questions.html On Wed, 2006-03-15 at 21:14 +, Panagiotis Sidiropoulos wrote: I use following code to update a table. Connection is succesfuly carried out and before this, Select statement executed succesfuly. Problem is that this statement is not accepted. I checked this same statement through an MySQL client and it was executed properly. Can anyone please advise? SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); showmessage( SQLQuery.SQL.Text ); SQLQuery.Open; I also tested this statement a one line setting this to .text property. Panagiotis _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives -- Met vriendelijke groeten, Joost van der Sluis CNOC Informatiesystemen en Netwerken http://www.cnoc.nl _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
Additionaly to my original post, this query is executed under Linux using sqldb components TSQLQuery, TSQLtransaction and TMySQL41Connection. If any additional information is required please just task. Στις 15-03-2006, ημέρα Τετ, και ώρα 21:14 +, ο/η Panagiotis Sidiropoulos έγραψε: I use following code to update a table. Connection is succesfuly carried out and before this, Select statement executed succesfuly. Problem is that this statement is not accepted. I checked this same statement through an MySQL client and it was executed properly. Can anyone please advise? SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); showmessage( SQLQuery.SQL.Text ); SQLQuery.Open; I also tested this statement a one line setting this to .text property. Panagiotis _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
On Wednesday 15 March 2006 13:36, Panagiotis Sidiropoulos wrote: Additionaly to my original post, this query is executed under Linux using sqldb components TSQLQuery, TSQLtransaction and TMySQL41Connection. If any additional information is required please just task. Στις 15-03-2006, ημέρα Τετ, και ώρα 21:14 +, ο/η Panagiotis Sidiropoulos έγραψε: I use following code to update a table. Connection is succesfuly carried out and before this, Select statement executed succesfuly. Problem is that this statement is not accepted. I checked this same statement through an MySQL client and it was executed properly. Can anyone please advise? SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); showmessage( SQLQuery.SQL.Text ); SQLQuery.Open; I also tested this statement a one line setting this to .text property. Panagiotis First is 'aa' a variable that you are attempting to pass? Also I believe you do not need all the quotes. sqlquery.text ='update ids-aa set ids-licenses-left = 1 where ids-internet-id =aa - 123456789012345678'. Of couse I don't know what 'aa' is all about. John _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
It is not my intention being irritating, on the contrary. Composing this post I thought that subject was describing the problem, focusing on the SQL statement syntax and not to the code. I apologise. Your answer gives me the impression that you are tired reading this kind of not well described posts from me. I do not feel this is true, I only had another such comment once at the past in a case not more information was available than provided. What I can promise is to try harder and read url with directions suggested. I also ask you to be patient in such cases, you see it is an international meeting point at which people with different cultures meet communicating in English, not mother language for many of us. So, please let me provide full code. While executing, code exit at .open point. Trying a test SELECT statement it is OK. If you can think anythink to direct me where to search it would be most helpful to me. // update ID data function mgspsUpdateIDData: integer; var nLicensesLeft: integer; cCustID: string; begin // update Result := MGSPS_ERROR_UPDATE_FAILED; nLicensesLeft := SQLQuery.FieldByName( 'ids-licenses-left' ).AsInteger - 1; cCustID := SQLQuery.FieldByName( 'ids-internet-id' ).AsString; SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( ' set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( ' where `ids-internet-id`=aa-123456789012345678' ); SQLQuery.Open; Result := MGSPS_ALL_OK; end; An why all these quotes arount the field names? Does that even compile? These quotes are necessary maybe because of field names form, dash character is used separating parts of names making these more readable. I tried it without quotes and had dificulties. This way works fine, I have Select statements executed normally but not this Update which I think is in a general accepted form. As I wrote on my complementary reply, this is running on Linux using SQLDB components and MySQL database server. Panagiotis Στις 15-03-2006, ημέρα Τετ, και ώρα 20:27 +0100, ο/η Joost van der Sluis έγραψε: This is gettig irritating. 'statement is not accepted' Is that the error message? 'statement not accepted' You can't expect us to keep answering this kind of questions. An why all these quotes arount the field names? Does that even compile? And read this, before you sent another question: www.catb.org/~esr/faqs/smart-questions.html On Wed, 2006-03-15 at 21:14 +, Panagiotis Sidiropoulos wrote: I use following code to update a table. Connection is succesfuly carried out and before this, Select statement executed succesfuly. Problem is that this statement is not accepted. I checked this same statement through an MySQL client and it was executed properly. Can anyone please advise? SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); showmessage( SQLQuery.SQL.Text ); SQLQuery.Open; I also tested this statement a one line setting this to .text property. Panagiotis _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); ids-aa, ids-licenses-left, ids-internet-id are field names into ids-aa table. aa-123456789012345678 is content of record in ids-internet-id filed which should be apdated. Thank you for your time. Panagiotis Στις 15-03-2006, ημέρα Τετ, και ώρα 11:55 -0800, ο/η johnf έγραψε: On Wednesday 15 March 2006 13:36, Panagiotis Sidiropoulos wrote: Additionaly to my original post, this query is executed under Linux using sqldb components TSQLQuery, TSQLtransaction and TMySQL41Connection. If any additional information is required please just task. Στις 15-03-2006, ημέρα Τετ, και ώρα 21:14 +, ο/η Panagiotis Sidiropoulos έγραψε: I use following code to update a table. Connection is succesfuly carried out and before this, Select statement executed succesfuly. Problem is that this statement is not accepted. I checked this same statement through an MySQL client and it was executed properly. Can anyone please advise? SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); showmessage( SQLQuery.SQL.Text ); SQLQuery.Open; I also tested this statement a one line setting this to .text property. Panagiotis First is 'aa' a variable that you are attempting to pass? Also I believe you do not need all the quotes. sqlquery.text ='update ids-aa set ids-licenses-left = 1 where ids-internet-id =aa - 123456789012345678'. Of couse I don't know what 'aa' is all about. John _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
Yes, you are right, .Open method is not appropriate for UPDATE statement, .ExecSQL is the one that does what I expect to. Thank you for your help, I realy appreciate it. Panagiotis Στις 15-03-2006, ημέρα Τετ, και ώρα 21:29 +0200, ο/η Graeme Geldenhuys έγραψε: SQLQuery.Close; SQLQuery.SQL.Clear; SQLQuery.SQL.Add( 'update `ids-aa`' ); SQLQuery.SQL.Add( 'set `ids-licenses-left`=1' ); SQLQuery.SQL.Add( 'where `ids-internet-id`=aa-123456789012345678' ); All I can think of, is to make sure you leave spaces between the different line, so when that get put together, they still make sence. If I split a Sql statement into seperate lines, I normall start ever line with a space. eg: SQLQuery.SQL.Add( 'where ... becomes ... SQLQuery.SQL.Add( ' where SQLQuery.Open; Not sure what components you use, so this may differ from what I use. The statement you are trying to run is a Update statement which doesn't return a value, so normally needs to be executed instead of Opened... eg: SQLQuery.Execute; Select statements would use the SQLQuery.Open; As I said, this could be different for certain components. Regards, - Graeme - _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
On 3/15/06, Panagiotis Sidiropoulos [EMAIL PROTECTED] wrote: SQLQuery.Open; Well, in Delphi the error would be obivious. 'Open' is for select statement, all other statements must be 'Execute'. So try something like: SQLQuery.Execute; (This is from memory, the actual procedure/function could be different.) Regards. -- Alexandre Leclerc _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQL Statement
Graeme also suggested similar approach, tested by me and confirmed. It is maybe elementary knowledge but not for unexperienced SQL users like me. Thank you for your time. Panagiotis Στις 15-03-2006, ημέρα Τετ, και ώρα 15:51 -0500, ο/η Alexandre Leclerc έγραψε: On 3/15/06, Panagiotis Sidiropoulos [EMAIL PROTECTED] wrote: SQLQuery.Open; Well, in Delphi the error would be obivious. 'Open' is for select statement, all other statements must be 'Execute'. So try something like: SQLQuery.Execute; (This is from memory, the actual procedure/function could be different.) Regards. -- Alexandre Leclerc _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
