date:20121217

[firebird-support] Wrting my first UDF for Firebird v1.5

2012-12-17 Thread SoftTech

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

2012-12-17 Thread Kevin Donn

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

2012-12-17 Thread Helio Gomes Pereira da Silva Filho

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

2012-12-17 Thread Svein Erling Tysvær

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

[firebird-support] Wrting my first UDF for Firebird v1.5

[firebird-support] Help with query optimization

[firebird-support] cpnael plus firebird

RE: [firebird-support] Help with query optimization

4 matches

Site Navigation

Mail list logo

Footer information