RE: [firebird-support] sp case statement
I forgot to mention I'm using firebird 1.5.5
1) The integer was a typo.
2) The stored procedure in your reply, it's looping through every record
correct?
I guess my question wasn't very clear. What I'm trying to do is have
conditional logic in the where clause.
pseudo code:
Select * from table T
Where
Case :input_code
when 'A' then
T.somefield = 'A'
when 'B' then
t.somefield = 'B'
else /* else we want both types */
t.somefield = 'A' or t.somefield = 'B'
end
or
select * from table T
if :input_code = 'A' then
where t.somefield = 'A'
else if :input_code = 'B' then
where t.somefield = 'B'
else
where t.somefield = 'A' or t.somefield = 'B'
This is just a simplified example. There are multiple input parameters and the
where clause will get more complex. Do I have to live with having the stored
procedure check every record in my example?
Daniel
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Alan J Davies
Sent: Wednesday, October 31, 2012 10:19 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] sp case statement
No.
You have some errors in the logic and data types.
You can try this. You will get one record returned for every record in your
table.
CREATE OR ALTER PROCEDURE GET_DATA (
Input_code char(1) )
RETURNS (
Output_code char(1) ) /* you have an integer which will fail */
as
begin
for select
case
when :input_code='A'
then 'A'
when :input_code='B'
then 'B'
else 'C'
end
from table
into :output_code
do suspend;
end
Alan J Davies
Aldis
On 31/10/2012 21:38, Matchey,Brian wrote:
>
>
> Is there a way to achieve the logic I'm trying to accomplish with the
> CASE statement in the WHERE clause below?
>
> CREATE OR ALTER PROCEDURE GET_DATA (
> Input_code char(1) )
> RETURNS (
> Output_code integer )
> as
> begin
> FOR SELECT T.Output_code
> FROM Table T
> WHERE T.Input_code =
> case :Input_code
when 'A' then 'A'
> when 'B' then 'B'
> else (('A') or ('B'))
> end
> INTO :Output_code
> DO
> SUSPEND;
> end;
>
> Vertafore
> Unleash your potential
>
> Daniel Raith
> Software Engineer II
> 26550 W. Mondovi St, Eleva, WI 54738
> T 800 433 2550 Ext 2917 | F 805 553 9308 | dra...@vertafore.com
> >
> vertafore.com
>
> [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
[firebird-support] Create index while database is in use?
Is it safe? I want to create an index on a table but the database (fb1.5) has ~20 active users. Database usage is mostly reads, especially the table I want to index. I just don't want to take a chance creating an index with active connections could corrupt the database. Daniel
[firebird-support] for select stored procedure question
I want to write a stored procedure that updates two columns (all records) in a table to initial caps or camel case. For example: "testing a test" -> "Testing A Test" I wrote a test procedure that accepts an input varchar, changes the string to initial caps and returns the output string. This works great. But now I want to create another stored procedure that contains a FOR SELECT on the table I want to update. However reading this page: http://ibexpert.net/ibe/index.php?n=Doc.FORSELECTINTODO mentions this caution statement: Caution! If the value of a PSQL variable that is used in the SELECT statement changes during execution of the loop, the statement may (but will not always) be re-evaluated for the remaining rows. To me, this means I should not create this procedure: create procedure InitialCapsMyTable as begin for select a, b from MyTable into :aa, :bb do begin :aa = initial_caps_proc( :aa ); :bb = initial_caps_proc( :bb ); end end Questions 1) Am I correct that the caution mentioned above applies my example? 2) Is there a better way to accomplish changing a column (all records) to initial caps? I'm using FB1.5, Daniel
RE: [firebird-support] for select stored procedure question
I tried using my stored procedure in an update statement like so: update MyTable set ColumnA = initial_caps_proc( ColumnA ) but I get an error saying "function unknown. @1" Daniel From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Woody Sent: Thursday, October 10, 2013 10:43 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] for select stored procedure question From: Raith,Daniel<mailto:dra...@vertafore.com> Sent: Thursday, October 10, 2013 10:33 AM To: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> Subject: [firebird-support] for select stored procedure question I want to write a stored procedure that updates two columns (all records) in a table to initial caps or camel case. For example: "testing a test" -> "Testing A Test" Why not just run a query to update the values if you only need to do it once? Update MyTable set column_A = initial_caps_proc(column_A), column_B = initial_caps_proc(column_B) if you really need a stored procedure that just returns the values and doesn't update them, then simply use this in the procedure: for select initial_caps_proc(column_A), initial_caps_proc(column_B) from myTable into :aa, :bb do suspend; Woody (TMW) [Non-text portions of this message have been removed]
RE: [firebird-support] for select stored procedure question
It's an internal procedure. I fixed my message file version issue and the error simply says: SQL error code = -804 Function unknown initial_caps_proc However this works: select * from initial_caps_proc( 'testing my test' ) From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Woody Sent: Thursday, October 10, 2013 10:59 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] for select stored procedure question Is initial_caps_proc an external function or an internal procedure? If it's an external function you need to make sure you declared it to the database and the DLL is in the UDF directory. It also looks like you might have a mismatched help file for FB since it's showing you an @1 where something else should be. Woody (TMW) ------ From: "Raith,Daniel" Sent: Thursday, October 10, 2013 10:52 AM To: Subject: RE: [firebird-support] for select stored procedure question > I tried using my stored procedure in an update statement like so: > > update MyTable > set ColumnA = initial_caps_proc( ColumnA ) > > but I get an error saying "function unknown. @1" > > Daniel > > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] On Behalf Of Woody > Sent: Thursday, October 10, 2013 10:43 AM > To: firebird-support@yahoogroups.com > Subject: Re: [firebird-support] for select stored procedure question > > > > From: Raith,Daniel<mailto:dra...@vertafore.com> > Sent: Thursday, October 10, 2013 10:33 AM > To: > firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> > Subject: [firebird-support] for select stored procedure question > > I want to write a stored procedure that updates two columns (all records) > in a table to initial caps or camel case. > > For example: "testing a test" -> "Testing A Test" > > > > Why not just run a query to update the values if you only need to do it > once? > > Update MyTable set column_A = initial_caps_proc(column_A), column_B = > initial_caps_proc(column_B) > > > if you really need a stored procedure that just returns the values and > doesn't update them, then simply use this in the procedure: > > for select initial_caps_proc(column_A), initial_caps_proc(column_B) from > myTable > into :aa, :bb do > suspend; > > > Woody (TMW) > > > > > [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 > > > [Non-text portions of this message have been removed]
RE: [firebird-support] for select stored procedure question
That works, but only if my stored proc contains the suspend. SET TERM ^ ; CREATE OR ALTER PROCEDURE INITIAL_CAPS_PROC ( input_string varchar(100)) returns ( output_string varchar(100)) as declare variable input_length integer; declare variable idx integer; begin output_string = ''; input_length = char_length(input_string); if (input_length > 0) then begin idx = 1; while (idx <= input_length) do begin if (idx = 1) then output_string = upper( substring( input_string from 1 for 1 ) ); else begin if ( substring( input_string from idx-1 for 1 ) = ' ' ) then output_string = output_string || upper( substring( input_string from idx for 1 ) ); else output_string = output_string || lower( substring( input_string from idx for 1 ) ); end idx = idx + 1; end end suspend; end^ SET TERM ; ^ From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Woody Sent: Thursday, October 10, 2013 1:27 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] for select stored procedure question As long as the initial_caps_proc doesn't have a suspend statement, it will basically be seen as a function call. To use it to update records one time: Update MyTable set Column_A = (select OutVar from initial_caps_proc(Column_A)), Column_B = (select OutVar from initial_caps_proc(Column_B)); I haven't tested this but it should work if memory serves me correctly. :) Woody (TMW)
RE: [firebird-support] for select stored procedure question
I guess I was actually on FB2.5. After switching back to FB1.5, I think I'm out of luck because the SUBSTRING internal function requires integer literals. So this statement isn't possible: substring( input_string from idx-1 for 1 ) Since most of our servers are running 1.5 and I can't control updates, and I can't use UDF's for distribution reasons, I think I'll have to do this outside of Firebird. Unless I write a substring_proc, hmmm. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Raith,Daniel Sent: Thursday, October 10, 2013 2:38 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] for select stored procedure question That works, but only if my stored proc contains the suspend. SET TERM ^ ; CREATE OR ALTER PROCEDURE INITIAL_CAPS_PROC ( input_string varchar(100)) returns ( output_string varchar(100)) as declare variable input_length integer; declare variable idx integer; begin output_string = ''; input_length = char_length(input_string); if (input_length > 0) then begin idx = 1; while (idx <= input_length) do begin if (idx = 1) then output_string = upper( substring( input_string from 1 for 1 ) ); else begin if ( substring( input_string from idx-1 for 1 ) = ' ' ) then output_string = output_string || upper( substring( input_string from idx for 1 ) ); else output_string = output_string || lower( substring( input_string from idx for 1 ) ); end idx = idx + 1; end end suspend; end^ SET TERM ; ^ From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Woody Sent: Thursday, October 10, 2013 1:27 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] for select stored procedure question As long as the initial_caps_proc doesn't have a suspend statement, it will basically be seen as a function call. To use it to update records one time: Update MyTable set Column_A = (select OutVar from initial_caps_proc(Column_A)), Column_B = (select OutVar from initial_caps_proc(Column_B)); I haven't tested this but it should work if memory serves me correctly. :) Woody (TMW) [Non-text portions of this message have been removed]
[firebird-support] Unable to start firebird service (1.5.6)
When I try to start the firebird service on this new server (windows server 2012) I get an error: Error 1053: The service did not respond to the start or control request in a timely fashion. The firebird.log file has some of these in it: INET/inet_error: connect errno = 10061 Any Thoughts? Daniel
