--title: Firebird UDF / TPzConv - sample SQL
--author: Kazuhiro Yoshida <moriq@moriq.com>

set names SJIS_0208;
connect 'd:/usr/doc/hoe.gdb' user 'sysdba' password 'masterkey';

DECLARE EXTERNAL FUNCTION pzconv_new 
  RETURNS INTEGER BY VALUE 
  ENTRY_POINT 'fn_pzconv_new' MODULE_NAME 'udf_pzconv.dll';

-- drop external function pzconv_han2zen;
declare external function pzconv_han2zen 
  INTEGER,
  cstring(512),
  cstring(512)
  returns parameter 2
  entry_point 'fn_pzconv_han2zen' module_name 'udf_pzconv.dll';

DECLARE EXTERNAL FUNCTION pzconv_free 
  INTEGER
  RETURNS INTEGER BY VALUE 
  ENTRY_POINT 'fn_pzconv_free' MODULE_NAME 'udf_pzconv.dll';

-- drop procedure ZENKANA;
set term !! ;
create or alter procedure ZENKANA
returns (id integer, zen varchar(255))
as
DECLARE VARIABLE pzconv INTEGER;
DECLARE VARIABLE ret INTEGER;
begin
  pzconv = pzconv_new();
  if (pzconv > 0) then
  begin
--(:
    for select id,pzconv_han2zen(:pzconv, town_yomi) from t_zip
      into :id,:zen
    do
      suspend;
--or
--  UPDATE TABLE SET FIND = pzconv_han2zen(:pzconv, MOTONO_FIELD);
--:)
    ret = pzconv_free(pzconv);
  end
end!!
set term ; !!

select * from zenkana;