Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Firebird SQL Development
Reload this Page

Parameters in views

Discuss Parameters in views in the Firebird SQL Development forum on Dev Shed.
Parameters in views Firebird SQL Development forum discussing administration, Firebird SQL syntax, or other Firebird SQL-related topics. Firebird is the evolution of Borland's Interbase product.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old April 2nd, 2006, 02:06 PM
podi podi is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2006
Posts: 5 podi User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 7 m 39 sec
Reputation Power: 0
Parameters in views
Is there a possibility to send parameters to views? if so, please give me instructions about syntax, because i can not find any docs...
Reply With Quote
  #2  
Old April 3rd, 2006, 08:02 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
  
Join Date: Jun 2001
Location: Outside US
Posts: 7,906 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 3 Days 6 h 56 m 54 sec
Reputation Power: 279
Do you mean something like: select * from view where view_col = 'x'?
Other than this no parameters in views (and for good reason), use a stored procedure.
Reply With Quote
  #3  
Old April 3rd, 2006, 09:12 AM
podi podi is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2006
Posts: 5 podi User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 7 m 39 sec
Reputation Power: 0
Quote:
Originally Posted by pabloj
Do you mean something like: select * from view where view_col = 'x'?

Yes, exactly
Quote:
Other than this no parameters in views (and for good reason), use a stored procedure.

But stored procedure can not return data rows, can it?
Could you mention what is this good reason for lack of parameters in views? i'm curious...
I thought that the more code i keep in database the easier it is to work with it. Then i do not have to change code in application (sometimes in couple of places), only in database...but maybe I am wrong. If so, please tell me.
Anyway, thanks for responce
Reply With Quote
  #4  
Old April 3rd, 2006, 09:29 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
  
Join Date: Jun 2001
Location: Outside US
Posts: 7,906 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 3 Days 6 h 56 m 54 sec
Reputation Power: 279
Quote:
Originally Posted by podi
Yes, exactly

But stored procedure can not return data rows, can it?
Could you mention what is this good reason for lack of parameters in views? i'm curious...
I thought that the more code i keep in database the easier it is to work with it. Then i do not have to change code in application (sometimes in couple of places), only in database...but maybe I am wrong. If so, please tell me.
Anyway, thanks for responce

Well, I was unclear, you can:
1. create a view with a where clause (CREATE VIEW ... AS SELECT ... FROM ... WHERE) and you'll also be able to place a where in the select run on the view
2. It is good not to have an order by in the view, because specifying another specific order by in the select run on the view would mean double ordering
3.A stored proc can return a set of rows
Reply With Quote
  #5  
Old April 3rd, 2006, 09:58 AM
podi podi is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2006
Posts: 5 podi User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 7 m 39 sec
Reputation Power: 0
Quote:
Originally Posted by pabloj
3.A stored proc can return a set of rows


well..how?

Quote:
"Calling Stored Procedures
* Stored Procedures can perform an action and do not return any data"

this is from http://www.destructor.de/firebird/storedproc.htm
Reply With Quote
  #6  
Old April 3rd, 2006, 10:04 AM
podi podi is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2006
Posts: 5 podi User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 7 m 39 sec
Reputation Power: 0
okay, I know what You mean, cheers!
Reply With Quote
  #7  
Old April 3rd, 2006, 11:43 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
  
Join Date: Jun 2001
Location: Outside US
Posts: 7,906 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 3 Days 6 h 56 m 54 sec
Reputation Power: 279
An example is the ALL_LANGS procedure in the employee.fdb sample database
sql Code:
Original - sql Code 
  1. SET TERM ^ ;CREATE PROCEDURE ALL_LANGS
  2. RETURNS (
  3.     CODE Varchar(5),
  4.     GRADE Varchar(5),
  5.     COUNTRY Varchar(15),
  6.     LANG Varchar(15) )
  7. AS
  8. BEGIN
  9.         FOR SELECT job_code, job_grade, job_country FROM job 
  10.                 INTO :code, :grade, :country
  11.         DO
  12.         BEGIN
  13.             FOR SELECT languages FROM show_langs 
  14.                     (:code, :grade, :country) INTO :lang DO
  15.                 SUSPEND;
  16.             /* Put nice separators between rows */
  17.             code = '=====';
  18.             grade = '=====';
  19.             country = '===============';
  20.             lang = '==============';
  21.             SUSPEND;
  22.         END
  23.     END^
  24. SET TERM ; ^

You call it as select * from all_langs
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Parameters in views

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway. 
Stay green...Green IT