[firebird-support] Wrting my first UDF for Firebird v1.5
Greetings All, I had to create a UDF using Delphi that can be imported into Firebird. Currently using v1.5 but am studying up on how to move to the latest version of Firebird. Using this article as my guide I created my first UDF: http://www.firebirdsql.org/en/writing-udfs-for-interbase/ The UDF will take a SQL statement with parameters and replace the parameters with the actual value, and return a SQL statement that can then be used with EXECUTE STATEMENT. The UDF works great. My concern and question is since I'm using v1.5 of Firebird and I did include IB_Util in my uses clause do I need to use "function ib_util_malloc(l: integer): pointer; cdecl; external 'ib_util.dll';"? Or does anyone see anything wrong with my UDF that I should be made aware of? Does ib_util.dll need to be in the windows\system directory on the office server? Here is my UDF: DPR Source: library Softtech; uses SysUtils, Classes, SQLFunctions in 'SQLFunctions.pas'; {$R *.RES} exports SQLCondParamRepl; begin end. Unit Source: {- Project Name: Project Dir : C:\Delphi 5\DLLs\Softtech UDF\ Created : 17-Dec-2012 Unit Name : SQLFunctions Purpose : Author : Michael G. Tuttle History : -} unit SQLFunctions; interface //* IB_Util.pas must be in the library path uses Sysutils, IB_Util; function SQLCondParamRepl(SQLCommandText: PChar; var AcctID, CaseID, DebtNo: Integer): PChar; cdecl; export; implementation { Use this in the script editor of Database Workbench to install this function into Firebird: declare external function f_SQLCondParamRepl cString(32760), Integer, Integer, Integer returns cstring(32760) free_it entry_point 'SQLCondParamRepl' module_name 'softtech'; Test it with this: SELECT F_SQLCONDPARAMREPL('SELECT D.LIST_DATE FROM DEBT D WHERE D.ACCT_ID = :Param1 AND D.CASE_ID = :Param2 AND D.DEBT_NO = :Param3', :V_ACCT_ID, :V_CASE_ID, :V_DEBT_NO) from RDB$Database Or from within a stored procedure: RESULT = F_SQLCONDPARAMREPL('SELECT D.LIST_DATE FROM DEBT D WHERE D.ACCT_ID = :Param1 AND D.CASE_ID = :Param2 AND D.DEBT_NO = :Param3', :V_ACCT_ID, :V_CASE_ID, :V_DEBT_NO); EXECUTE STATEMENT} function SQLCondParamRepl(SQLCommandText: PChar; var AcctID, CaseID, DebtNo: Integer): PChar; cdecl; export; var CT: String; begin CT := String(SQLCommandtext); CT := StringReplace(CT, ':Param1', IntToStr(AcctID), [rfReplaceAll, rfIgnoreCase]); CT := StringReplace(CT, ':Param2', IntToStr(CaseID), [rfReplaceAll, rfIgnoreCase]); if DebtNo > 0 then CT := StringReplace(CT, ':Param3', IntToStr(DebtNo), [rfReplaceAll, rfIgnoreCase]); Result := PChar(CT); end; end.
[firebird-support] Help with query optimization
I've got a query that I threw together to serve as a search in a web app. My first cut of it used "containing" operators for the user's search terms and was taking 15-20 seconds to run, which didn't surprise me. I figured I would have to do something smarter than "containing". As I began to play around with optimization, I came to find that the "containing" operators weren't my problem. My problem was my "distinct" and my joins. I prowled through the mailing list last Friday looking for leads, tried a lot of things, but never could get just the joins, without search criteria, running in much under about 14 seconds. select distinct s.StudentSeq, s.LastName, s.FirstName, s.BirthDate, s.MiddleName, p.StudentID from student s join PrfStuID p on s.StudentSeq=p.StudentSeq join AliasStu a on s.StudentSeq=a.StudentSeq join SchlHist h on s.StudentSeq=h.StudentSeq join Facility f on h.FacilityID=f.FacilityID join District d on f.DistrictCode=d.DistrictCode This database is old and hoary. I'll include the DDL, at least for the important fields and indexes. There are no primary keys on any tables, although every table has a single unique index. The plan looks like this: PLAN SORT (JOIN (F NATURAL, D INDEX (DISTRICT_), H INDEX (SCHLHIST_FACILITYID), S INDEX (STUDENT_), P INDEX (PRFSTUID_), A INDEX (ALIASSTU_STUDENTSEQINDEX))) Any ideas or leads appreciated. Thanks, Kevin DDL - CREATE TABLE STUDENT( STUDENTSEQ integer, FAMILYSEQ integer, CURRENTFAMILYSEQ integer, LASTNAME varchar(50) COLLATE EN_US, FIRSTNAME varchar(15) COLLATE EN_US, MIDDLENAME varchar(15) COLLATE EN_US, SEX varchar(1) COLLATE EN_US, BIRTHDATE date, ...); CREATE UNIQUE INDEX STUDENT_ ON STUDENT (STUDENTSEQ); CREATE INDEX STUDENT_FAMILYINDEX ON STUDENT (FAMILYSEQ); CREATE INDEX STUDENT_OPT_BIRTHDATE ON STUDENT (BIRTHDATE); CREATE TABLE PRFSTUID( STUDENTSEQ integer, STUDENTID varchar(20) COLLATE EN_US, SORTORDER smallint, ORIGINCODE smallint); CREATE UNIQUE INDEX PRFSTUID_ ON PRFSTUID (STUDENTSEQ); CREATE INDEX PRFSTUID_STUDENTIDINDEX ON PRFSTUID (STUDENTID); CREATE TABLE ALIASSTU( STUDENTID varchar(20) COLLATE EN_US, ORIGINCODE smallint, STUDENTSEQ integer, ...); CREATE UNIQUE INDEX ALIASSTU_ ON ALIASSTU (STUDENTID,ORIGINCODE); CREATE INDEX ALIASSTU_ORIGINCODEINDEX ON ALIASSTU (STUDENTSEQ,ORIGINCODE); CREATE INDEX ALIASSTU_STUDENTSEQINDEX ON ALIASSTU (STUDENTSEQ); CREATE TABLE SCHLHIST( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, FACILITYID varchar(6) COLLATE EN_US, ...); CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ); CREATE INDEX SCHLHIST_COESEQINDEX ON SCHLHIST (COESEQ); CREATE INDEX SCHLHIST_FACILITYID ON SCHLHIST (FACILITYID); CREATE INDEX SCHLHIST_MOSTRECENTINDEX ON SCHLHIST (STUDENTSEQ,RESDATE,FUNDINGDATE,GENDATE); CREATE TABLE FACILITY( FACILITYID varchar(6) COLLATE EN_US, DISPLAYCODE varchar(6) COLLATE EN_US, FACILITYNAME varchar(55) COLLATE EN_US, DISTRICTCODE integer, ...); CREATE UNIQUE INDEX FACILITY_ ON FACILITY (FACILITYID); CREATE INDEX FACILITY_DISPLAYCODEINDEX ON FACILITY (DISPLAYCODE); CREATE INDEX FACILITY_DISTRICTCODE ON FACILITY (DISTRICTCODE); CREATE INDEX FACILITY_DISTRICTINDEX ON FACILITY (DISTRICTCODE,FACILITYNAME); CREATE INDEX FACILITY_NAMEINDEX ON FACILITY (FACILITYNAME); CREATE TABLE DISTRICT( DISTRICTCODE integer, DISTRICTNAME varchar(50) COLLATE EN_US, REGIONCODE smallint, ...); CREATE UNIQUE INDEX DISTRICT_ ON DISTRICT (DISTRICTCODE); CREATE INDEX DISTRICT_LOCALNAMEINDEX ON DISTRICT (LOCALFLAG,DISTRICTNAME); CREATE INDEX DISTRICT_NAMEINDEX ON DISTRICT (DISTRICTNAME); [Non-text portions of this message have been removed]
[firebird-support] cpnael plus firebird
guys i need some help here... i have a cpanel web server, and a costumer asked me if i can host his site with a firebird databse what i need to do on my server to make it work? install client? install server? install some kind of driver? ...or the person can put it on a remote server and the site can access it remotely? sorry for bad english..i'm brazilian...thanks -- *Hélio Gomes Pereira da Silva Filho* *DGMnet Telemática Ltda* - Hospedagem de E-mails Corporativos e Websites, autenticação ADSL e Suporte Técnico especializado. Mobile: +55 (64) 9946 1309, (11) 9 7332 5592 Home: +55 (64) 3631 1013, (11) 5052 6494 Work: +55 (64) 3632 9700 [Non-text portions of this message have been removed]
RE: [firebird-support] Help with query optimization
Hi Kevin! >I've got a query that I threw together to serve as a search in a web app. >My first cut of it used "containing" operators for the user's search terms and >was >taking 15-20 seconds to run, which didn't surprise me. I figured I would have >to do >something smarter than "containing". As I began to play around with >optimization, I >came to find that the "containing" operators weren't my problem. My problem >was my >"distinct" and my joins. > >I prowled through the mailing list last Friday looking for leads, tried a lot >of >things, but never could get just the joins, without search criteria, running >in much >under about 14 seconds. > >select distinct s.StudentSeq, s.LastName, s.FirstName, >s.BirthDate, s.MiddleName, p.StudentID >from student s >join PrfStuID p on s.StudentSeq=p.StudentSeq >join AliasStu a on s.StudentSeq=a.StudentSeq >join SchlHist h on s.StudentSeq=h.StudentSeq >join Facility f on h.FacilityID=f.FacilityID >join District d on f.DistrictCode=d.DistrictCode > >This database is old and hoary. I'll include the DDL, at least for the >important >fields and indexes. There are no primary keys on any tables, although every >table has >a single unique index. The plan looks like this: > >PLAN SORT (JOIN (F NATURAL, D INDEX (DISTRICT_), H INDEX >(SCHLHIST_FACILITYID), >S INDEX (STUDENT_), P INDEX (PRFSTUID_), A INDEX (ALIASSTU_STUDENTSEQINDEX))) The first impression is that this is a fair query with a decent plan, or at least as decent as it gets without any WHERE clause. However, many web apps desire to show only a fraction of the records on screen, and that can be done without distinct (so that you do not have to select all records): select s.StudentSeq, s.LastName, s.FirstName, s.BirthDate, s.MiddleName, p.StudentID from student s join PrfStuID p on s.StudentSeq=p.StudentSeq where exists(select * from AliasStu a join SchlHist h on a.StudentSeq=h.StudentSeq join Facility f on h.FacilityID=f.FacilityID join District d on f.DistrictCode=d.DistrictCode where s.StudentSeq=a.StudentSeq) ROWS 1 TO 20 (you may want to add ORDER BY and possibly use FIRST rather than ROWS if you use an old Firebird version, of course 1 TO 20 was randomly chosen, use whatever fits your need). However, if you want all rows returned, then I don't think my suggestion will help you anything. HTH, Set