We are converting a working application from the ZeosLib data access components to pgExpress with Devart drivers.
We have a problem with the Devart pgExpress drivers throwing an error on a call to TSQLQUery.ExecSQL(). This query evaluates perfectly in pgAdmin, and previously when using the ZeosLib data access components. All we are doing is setting the SQLQUery.SQL.Text property and calling ExecSQL(), and there are no event handlers or parameters set up on the SQLQuery object.
The error is:
- No value for parameter '[^[:digit:]]*'
The query text is as follows:
Code: Select all
set search_path = aull, breedplan, public;
set session authorization sk;
/* if the header record doesn't already exist for this calving year, create it */
insert into membership_years (soc_code, members_key, membership_year,
membership_rec_type, membership_type, membership_status,
date_assessed, membership_key)
select zt.soc_code, zt.members_key, 2011, 2,
cast('6' || cast('4' as varchar) as integer), 1,
local_timestamp_f('AULL'), cast(next_seq_no_f('AULL', 'MY', 'MEMBERSHIP_KEY') as integer) as membership_key
from ztmp.ztmp_251077_memberkeys zt
where zt.members_key not in
(select ztt.members_key from ztmp.ztmp_251077_memberkeys ztt
join membership_years my using (soc_code, members_key)
where ztt.soc_code = 'AULL'
and my.membership_rec_type = 2
and my.membership_type = cast('6' || cast('4' as varchar) as integer)
and my.membership_year = 2011);
/*===============================================================*/
/* save stuff away for the report */
/* note: this needs fixing because you could have multiple member link rows and this table should only
ever return ONE row */
create table ztmp.ztmp_251077_rpthdgs as
select me.soc_code,
me.member_ident,
me.members_key,
mp.member_ident as primary_herd,
mp.memb_name as primary_name,
me.stud_letters,
me.pref_language,
me.stud_prefix,
me.soc_me_cb_1 as invent_status,
me.soc_me_ck_4_flag as full_invent,
(select alt_member_ident from member_cross_refs where soc_code='AULL' and alt_type=3 and members_key=me.members_key limit 1) as nlis_pic,
me.memb_name,
me.address_1,
me.address_2,
me.address_3,
param_to_description_f(me.soc_code, 'STATE', me.state) as me_state,
me.postcode,
me.phone_priv,
me.phone_fax,
cast('2011' as varchar(4)) as calv_yr ,
cast(param_to_description_f(me.soc_code, 'INVENTORY_SEASON', '4') as varchar(16)) as calv_seas,
cm.company_name,
cm.address_1 as cm_address_1, cm.address_2 as cm_address_2,
cm.address_3 as cm_address_3, cm.postcode as cm_postcode,
param_to_description_f(cm.soc_code, 'STATE', cm.state) as cm_state,
param_to_description_f(cm.soc_code, 'COUNTRY', cm.country) as cm_country,
cm.phone_bus as cm_phone_bus,
cm.phone_bus_2 as cm_phone_bus_2,
cm.phone_fax as cm_phone_fax,
cm.email as cm_email ,
my.membership_key as invent_year_key,
case when 'N' = 'Y' then 'Y' else 'N' end as do_update_counts,
to_char(local_timestamp_f(me.soc_code) ,'FMMonth dd, yyyy') as issue_date,
case when 'AULL' = 'CANG' then me.soc_me_cb_2 else cast(null as int2) end as anim_rpt_sort_order
from ztmp.ztmp_251077_memberkeys zt
join members me on me.soc_code = zt.soc_code and me.members_key = zt.members_key
join companies cm on cm.soc_code = zt.soc_code
left join member_links ml on ml.soc_code = zt.soc_code and ml.secondary_members_key = me.members_key and ml.member_link_type = 2
and member_link_exists_f(ml.soc_code,ml.primary_members_key,ml.secondary_members_key,2,'20110831')
left join members mp on mp.soc_code = zt.soc_code and ml.primary_members_key = mp.members_key
join membership_years my on my.soc_code = zt.soc_code
and my.members_key = me.members_key
and my.membership_year = '2011'
and my.membership_rec_type = 2
and my.membership_type = cast('6' || cast('4' as varchar) as integer)
where zt.soc_code = 'AULL' ;
/*===============================================================*/
/* if we are re-calculating figures, delete the old ones from membership_inventory */
delete from membership_inventory
where soc_code = 'AULL'
and membership_key in
(select invent_year_key from
ztmp.ztmp_251077_rpthdgs hdg
where hdg.do_update_counts = 'Y');
/*===============================================================*/
/* if you are recalculating figures, gather up all currently owned animals for the herds */
/* if not updating, pass the empty string into members_inventory_load_f so that an empty keys table gets created */
select members_inventory_load_f(case when
exists(select 1
from ztmp.ztmp_251077_rpthdgs hdg
where hdg.do_update_counts = 'Y' group by 1) then 'ztmp_251077_memberkeys' else '' end, 'ztmp_251077_animalkeys', cast('20110831' as date))
;
/*===============================================================*/
/* to get in the inventory you must be
1. Female
2. Active
4. of correct age ie. older than the minimum date of birth entered by the user, and younger than that date - 16 years
*/
insert into membership_inventory
( soc_code,membership_key,animals_key, rego_status)
select mi.soc_code, hdg.invent_year_key, an.animals_key, an.rego_status
from ztmp.ztmp_251077_animalkeys mi
join animals an on an.soc_code = mi.soc_code and an.animals_key = mi.animals_key
join ztmp.ztmp_251077_rpthdgs hdg on hdg.soc_code = mi.soc_code and hdg.members_key = mi.members_key
where an.sex = 1
and an.anim_type 5
and mi.anim_status = 1
and held_status is null
and ((an.date_of_birth <= 20110831 and an.date_of_birth is not null)
or (an.calving_year <= extract(year from cast('20110831' as date)) and an.date_of_birth is null))
and (get_global_f(an.soc_code, 'inventory_registered_only') is distinct from 'true' or an.rego_status = 11)
and hdg.do_update_counts = 'Y'
order by hdg.member_ident,
an_format_ilr1_ident_f(an.soc_code, 'out', null, an.anim_ident),
anim_type,
param_to_abbrev_f(an.soc_code,'SEX',an.sex) desc,
coalesce(an.calving_year,extract('year' from an.date_of_birth)),
cast (nullif(substring(an.tattoo from '^(?:[^[:digit:]]*)([[:digit:]]*)'),'') as integer),
substring(an.tattoo from '^[^ ]*' );
/* analyze the report headings so it can join it properly */
select sys_analyse_table_f('ztmp.ztmp_251077_rpthdgs');
/*===============================================================*/
/* make this the rpt bit then */
create table ztmp.ztmp_251077_inventanims as
select hdg.members_key,
hdg.member_ident,
hdg.primary_herd,
hdg.primary_name,
hdg.stud_letters,
hdg.memb_name,
hdg.address_1,
hdg.address_2,
hdg.address_3,
hdg.me_state,
hdg.postcode,
hdg.phone_priv,
hdg.phone_fax,
hdg.calv_yr ,
hdg.calv_seas,
hdg.company_name,
hdg.cm_address_1,
hdg.cm_address_2,
hdg.cm_address_3,
hdg.cm_postcode,
hdg.cm_state,
hdg.cm_country,
hdg.cm_phone_bus,
hdg.cm_phone_bus_2,
hdg.cm_phone_fax,
hdg.cm_email ,
hdg.issue_date,
hdg.pref_language,
hdg.anim_rpt_sort_order,
hdg.stud_prefix,
hdg.nlis_pic,
hdg.invent_status,
cast(case when full_invent then '23.10' else '9.90' end as varchar(5)) as invent_fee,
cast(case when full_invent then '20.90' else '8.80' end as varchar(5)) as invent_fee_disc,
mi.*,
an_format_ident_f(an.soc_code,an.animals_key) as anim_ident,
an.tattoo,
an.herd_id,
an.anim_name,
an.calving_year,
an.inventory_season,
substr(get_global_f(an.soc_code, 'year_letters')
/*,cast(extract(year from an.date_of_birth) as integer) */
, cast('2011' as integer)
-get_global_int_f(an.soc_code, 'year_letter_base')+1,1) as anim_year_letter,
param_to_abbrev_f(an.soc_code,'SEX',an.sex) as sex_abbr,
param_to_abbrev_f(an.soc_code,'REGO_STATUS',an.rego_status) as reg_abbr,
an_format_ilr1_ident_f(an.soc_code, 'out', null, an.anim_ident) as sort_anim_ident,
cast(2011 as int) as inventory_year,
cast(param_to_description_f(an.soc_code, 'SEASON', '4') as varchar(16)) as invent_season_desc,
cast(sys_year_code_f('AULL', 2011) as varchar(1))as current_year_code,
cast(2011 as integer) as current_year,
an.anim_type as reg_order,
sire_tattoo,
sire_ident,
sire_ai,
sire_date
from ztmp.ztmp_251077_rpthdgs hdg
join membership_inventory mi on mi.soc_code = hdg.soc_code and mi.membership_key = hdg.invent_year_key
join animals an on an.soc_code = mi.soc_code and an.animals_key = mi.animals_key
left join (select sd.dam_key as animals_key, an1.tattoo as sire_tattoo, an1.anim_ident as sire_ident, case when sd.service_type = 2 then 'Y' else null end as sire_ai, sd.from_service_date as sire_date
from service_details sd
join animals an1 on an1.soc_code = sd.soc_code and an1.animals_key = sd.sire_key
join ztmp.ztmp_251077_rpthdgs hdg on hdg.soc_code = an1.soc_code
join membership_inventory mi on mi.soc_code = hdg.soc_code and mi.membership_key = hdg.invent_year_key
where sd.dam_key = mi.animals_key
and sd.from_service_date = (select from_service_date from service_details sd1
where sd1.soc_code= hdg.soc_code and sd1.dam_key = sd.dam_key
and coalesce(sd1.service_status,0) = 0
and sd1.from_service_date between cast(cast(to_number(hdg.calv_yr,'9999') - 1 as char(4)) || '-01-01' as date)
and cast(cast(to_number(hdg.calv_yr,'9999') - 1 as char(4)) || '-12-31' as date)
order by sd1.from_service_date asc limit 1))
sd on sd.animals_key = mi.animals_key ;
/* and t3941.adls_flag = 'N' and t3941.forms_flag = 'Y' */I should add that this is occuring in Delphi 7. The reason for the change to Devart is that we are preparing to migrate the application to a more current version of Delphi.
Thanks,
Alex.