|
[ Pobierz całość w formacie PDF ]
.htm (78 of 86) [10/10/2000 1:14:05 AM] Ch 16 -- Advanced InterBase ConceptsMetheny Letter From Home 9 1Raitt Longing in their HeartsSade Love Deluxe 8 1Young Landing on Water 7 2Young Life 7 2When reviewing this data, it's hard not to feel that there is something missing from the Types field.After all, whatdoes the number 1 mean? What type is that?Once again, you can get the question answered by going to the well a second time and querying the Types table.However, it should come as no surprise to learn that there is a second solution:select Artist.Last, Album.Album, Album.Rating, Types.Typesfrom Album, Artist, Typeswhere artist.code = album.groupcode andAlbum.Album like "L%" andTypes.Code = Album.Typesorder by Artist.lastLAST ALBUM RATING TYPES============================== ========================= ====== ===============Adderley Live at Memory Lane JAZZBeatles Live at the BBC 6 ROCKBrowne Lawyers in Love 6 ROCKBrowne Lives in the Balance 6 ROCKCollins Living 6 FOLKDebussy La Mer 7 CLASSICALLiebman Lookout Farm 6 JAZZMcLaughlin Live at the Royal Festiva JAZZMetheny Letter From Home 9 JAZZSade Love Deluxe 8 JAZZYoung Landing on Water 7 ROCKYoung Life 7 ROCKHere you broadened the question by specifying that you want to bring in the Types table:from Album, Artist, Typesfile:///D|/DOWNLOAD/charlie_calvert's_borland_c++_builder_unleashed/ch16.htm (79 of 86) [10/10/2000 1:14:05 AM] Ch 16 -- Advanced InterBase ConceptsInclude one of its fields in the result set:select Artist.Last, Album.Album, Album.Rating, Types.TypesLink the Album table and Types table on the primary and foreign keys of the two tables:where.Types.Code = Album.TypesYou get all the things you need; the only problem is that you can't ask the user to open up WISQL--of allapplications!--just to get the answer to a simple question.You can take a number of courses at this point, but one of the best is to simply wrap your query in a stored procedure:CREATE PROCEDURE ALBUMSEARCH (ANALBUMNAME VARCHAR(75))RETURNS (ARTISTNAME VARCHAR(30),ALBUMNAME VARCHAR(30),RATINGVALUE VARCHAR(30),TYPENAME VARCHAR(30),MEDIUMNAME VARCHAR(30))ASbeginforselect Artist.Last, Album.Album, Album.Rating, Types.Types, Medium.Mediumfrom Album, Artist, Types, Mediumwhere artist.code = album.groupcodeand Album.Album like :AnAlbumNameand Types.Code = Album.Types and Medium.Code = Album.Mediumorder by Artist.Lastinto :ArtistName, :AlbumName, :RatingValue, :TypeName, MediumNamedo suspend;endYou can break this procedure down into several sections in order to make some sense of it.First, notice the header:CREATE PROCEDURE ALBUMSEARCH (ANALBUMNAME VARCHAR(75))This says that you are creating a stored procedure named AlbumSearch that takes a string as a parameter.Yousupply the name of the album you want to search in this string.The next part of the procedure declares what is returned to the user:file:///D|/DOWNLOAD/charlie_calvert's_borland_c++_builder_unleashed/ch16.htm (80 of 86) [10/10/2000 1:14:05 AM] Ch 16 -- Advanced InterBase ConceptsRETURNS (ARTISTNAME VARCHAR(30),ALBUMNAME VARCHAR(30),RATINGVALUE VARCHAR(30),TYPENAME VARCHAR(30),MEDIUMNAME VARCHAR(30))These rows set up what you want returned from the procedure.At the very bottom of the procedure, you state thesenames again, saying that you want the query to be returned in these variables:into :ArtistName, :AlbumName, :RatingValue, :TypeName, MediumNameThe query itself sits in between a begin.end pair, which nests around a for.do statement:asbegin for // Query goes heredo suspend;endIf you forget to wrap your query in this faintly ridiculous-looking syntactical sugar, InterBase complains about asingleton query not being able to return multiple rows.Now that you have your stored procedure all set up, the next thing to do is call it from BCB.The syntax for doing thiscould not be simpler:select * from AlbumSearch(:SearchValue);This simple SQL statement should reside inside the SQL property of a BCB TQuery component.You can then callthis procedure with code that looks like this:void TDMod::AlbumSearch(AnsiString SearchValue){AlbumSearchQuery->Close();AlbumSearchQuery->ParamByName("SearchValue")->Value = SearchValue;AlbumSearchQuery->Open();}That's all there is to it.Now you can hook up a TDataSource to the TQuery and a TDBGrid to theTDataSource, and after calling the AlbumSearch function, you see the results of your query inside a BCBapplication.You can access a number of interesting stored procedures in this manner from the menus of the BCB program.Some ofthe most interesting ones involve asking about the ratings you assign to albums.For instance, you can ask to see all thealbums that have a rating between five and seven or a rating higher than nine.This is such an important set of queriesthat I review them in the last section of this chapter, "Viewing a Range of Data."file:///D|/DOWNLOAD/charlie_calvert's_borland_c++_builder_unleashed/ch16.htm (81 of 86) [10/10/2000 1:14:05 AM] Ch 16 -- Advanced InterBase ConceptsAsking Questions That Do Not Return DatasetsIn the preceding section, you saw how to ask a question that returns a dataset.A different kind of stored procedure askshow to return a particular value such as a single number or string.For instance, you might want to ask the answer manhow many albums are in the database:select Count(*) from album;COUNT===========290To create a stored procedure that returns this kind of information, you should writeCREATE PROCEDURE ALBUMCOUNTRETURNS (NUM INTEGER)ASbeginforselect Count(*) from Albuminto :Numdo exit;endThis procedure doesn't take any parameters:CREATE PROCEDURE ALBUMCOUNTIt does, however, return a value:RETURNS (NUM INTEGER)Because you ask for a single answer and not a series of rows, you can use exit instead of suspend:for // Query goes heredo exit;Once you compose the query, you can use a stored procedure on the BCB end to get data from it.To set up the storedprocedure, all you have to do is drag it off the Component Palette, set its DataBaseName to the Music alias, anddrop down the list from its StoredProcName property so you can choose the appropriate stored procedure.The following code shows how to call the stored procedure from BCB:int TDMod::GetTotalAlbumCount(void){GetAlbumCount->Prepare();file:///D|/DOWNLOAD/charlie_calvert's_borland_c++_builder_unleashed/ch16.htm (82 of 86) [10/10/2000 1:14:05 AM] Ch 16 -- Advanced InterBase ConceptsGetAlbumCount->ExecProc();return GetAlbumCount->ParamByName("Num")->AsInteger;}This method returns an integer, which you can display to the user in any manner you think appropriate.Viewing a Range of DataTwo interesting stored procedures allow you to ask questions such as, "What albums have a particular rating?" and"What albums have a rating of nine or better?"CREATE PROCEDURE NINEORBETTERRETURNS (LAST VARCHAR(30),ALBUM VARCHAR(30),RATING INTEGER)ASbeginforselect Artist.Last, Album.Album, Album.Ratingfrom Album, Artistwhere Album.GroupCode = Artist.Codeand Album.Rating >= 9Order By Album [ Pobierz całość w formacie PDF ] |
Archiwum
|