Re: [firebird-support] Re: expression evaluation not supported
Thank you very much Jonathan. I had managed to figure this out about 30 minutes after making the post. I must admit I was misled by the 'expression evaluation not supported.' message devoid of any detail. Apart from this my sojourn with Firebird is progressing well. Regards Jupiter On Sat, Aug 18, 2012 at 11:30 PM, realoldgeek2008 jonathan.fi...@rocketmail.com wrote: ** Hello Jupiter; Sorry you are forced to use something as great as Firebird :-). I assume LAST_MODIFIED_ON is a TIMESTAMP field in your table and is being updated via a trigger or some other mechanism as records are modified. Your DATEDIFF syntax is incorrect; try: ... WHERE LICENSE_PLATE_NO'No plate found' AND DATEDIFF(second,LAST_MODIFIED_ON,current_timestamp)120 or ... WHERE LICENSE_PLATE_NO'No plate found' AND DATEDIFF(minute,LAST_MODIFIED_ON,current_timestamp)2 Regards, Jonathan --- In firebird-support@yahoogroups.com, Jupiter Punungwe punungwe.tech@... wrote: I am running this query SELECT LICENSE_PLATE_NO, IMAGE_NAME FROM table WHERE (LICENSE_PLATE_NO'No plate found') AND (DATEDIFF(second FROM LAST_MODIFIED_ON TO current_time)120) the aim being to find all records modified in the last two minutes. I get the error message '[blah blah blah] expression evaluation not supported.' Does this mean I cannot put a function in a Firebird SQL statement? I can do this in MySQL and SQL Server without any problem. In this particulat project, I am forced to use Firebird for legacy reasons. Regards Jupiter [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Multiple rows in a EXECUTE PROCEDURE
RIGHT!!! Thank you very much. Walter. On Sat, Aug 18, 2012 at 10:00 PM, Paul Vinkenoog p...@vinkenoog.nl wrote: ** Hello Walter, With the following Stored Procedure: SET TERM ^ ; CREATE PROCEDURE TEST RETURNS( IDENTI TYPE OF COLUMN CLIENTS.CLI_IDENTI, NAME TYPE OF COLUMN CLIENTS.CLI_NAME) AS DECLARE VARIABLE MYCOMMAND VARCHAR(80) ; BEGIN MYCOMMAND = 'SELECT CLI_IDENTI, CLI_NAME FROM CLIENTS ORDER BY CLI_NAME' ; FOR EXECUTE STATEMENT :MYCOMMAND INTO :IDENTI, :NAME DO SUSPEND; END^ SET TERM ; ^ I get multiple rows when executing it with the Execute option of the SQL Manager. However just one row with ISQL or a program. EXECUTE PROCEDURE TEST; With ISQL or with a program the prior line show me just one row. Why that? And how can I get all the rows? Since this is a selectable stored procedure, you should retrieve its output (a dataset) like this: select identi, name from test HTH, Paul Vinkenoog [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] I need help-Syntax error - ;
Hi What is the problem with the following code? Thank you SET TERM ^ ; CREATE PROCEDURE SPTR AS DECLARE VARIABLE price INTEGER; DECLARE VARIABLE price1 INTEGER; DECLARE VARIABLE eid SMALLINT; DECLARE VARIABLE bsum INTEGER DEFAULT 0; DECLARE VARIABLE SCTN CURSOR FOR ( Select ID,DBR,CDR From TBL_Transactions ORDER BY SDate); BEGIN OPEN SCTN; FETCH SCTN INTO eid, price,price1; WHILE (1=1) DO BEGIN bsum = bsum + (price-price1); update TBL_TRANSACTIONS set CBALANCE = bsum where Id = eId; FETCH SCTN INTO eid, price,price1; END CLOSE SCTN; --DEALLOCATE SCTN; SELECT ID,DBR,CDR FROM TBL_TRANSACTIONS ORDER BY SDate; SUSPEND; END^ SET TERM ; ^
Re: [firebird-support] I need help-Syntax error - ;
Hi Farshid, What is the problem with the following code? Without examining it very closely, I see several problems: CREATE PROCEDURE SPTR You don't specify any output parameters. But the SUSPEND later on suggests that you want to return something to the caller. So you need a RETURNS clause. AS DECLARE VARIABLE price INTEGER; DECLARE VARIABLE price1 INTEGER; DECLARE VARIABLE eid SMALLINT; DECLARE VARIABLE bsum INTEGER DEFAULT 0; DECLARE VARIABLE SCTN CURSOR FOR ( Select ID,DBR,CDR From TBL_Transactions ORDER BY SDate); BEGIN OPEN SCTN; FETCH SCTN INTO eid, price,price1; WHILE (1=1) DO BEGIN bsum = bsum + (price-price1); update TBL_TRANSACTIONS set CBALANCE = bsum where Id = eId; FETCH SCTN INTO eid, price,price1; END The WHILE loop will never end, because there is no LEAVE statement and 1=1 will remain true forever. You probably want something like if (row_count = 0) then leave; after the fetch. BTW, what if the first fetch (before loop entry) returns no data? Your code doesn't seem prepared for that. Because of the endless loop, the code below will never be reached: CLOSE SCTN; --DEALLOCATE SCTN; SELECT ID,DBR,CDR FROM TBL_TRANSACTIONS ORDER BY SDate; You have to select those values INTO local variables and/or output parameters. SUSPEND; SUSPEND gives the caller the opportunity to fetch the current row of output parameters. But as said before, you haven't declared any. Not sure if this is all that's wrong, but it's a start! ;-) HTH, Paul Vinkenoog
