Re: [firebird-support] EXECUTE STATEMENT ON EXTERNAL 'ODBC://odbc_datasource_name'
Questions on Firebird 3.0 - as long as it isn't final - should be directed to the firebird-devel mailinglist. Note that the document you link to is about the provider architecture, there is no ODBC provider included in Firebird 3.0, so this doesn't work out of the box. Mark On 30-8-2014 12:25, bert_herngr...@yahoo.com [firebird-support] wrote: Should the Execute statement work on an ODBC datasource already with FB 3.0? EXECUTE BLOCK AS BEGIN EXECUTE STATEMENT 'SELECT * FROM TEST_TABLE' ON EXTERNAL 'ODBC://TEST'; END I receive the following error message: Execute statement error at attach :335544721 : Unable to complete network request to host ODBC. 335544704 : Failed to locate host machine. 335544706 : The specified name was not found in the hosts file or Domain Name Services. Data source : Firebird::ODBC://TEST. It seems prefix ODBC is not recognized and interpreted as hostname. I read about this feature in the presentation 'Firebird 3: provider - based architecture, plugins and OO approach to API' from Alex Peshkov (http://www.firebirdsql.org/file/community/ppts/fbcon11/Firebird3Plug ins2011.pdf http://www.firebirdsql.org/file/community/ppts/fbcon11/Firebird3Plugins2011.pdf). In the release notes (http://web.firebirdsql.org/download/prerelease/rlsnotes/Firebird-3.0.0_Alpha1-ReleaseNotes.pdf) I read: The ability to access foreign database engines using providers should not be overlooked, either. It might seem strange to consider this, given the number of tools available for this sort of task. Think about the ability to access other Firebird databases using EXECUTE STATEMENT, that became available in Firebird 2.5. With a provider to ODBC or other common tool to access various data sources it is within reach to use EXECUTE STATEMENT to get direct access from procedures and triggers, to data from any database having a driver for the chosen access tool. It is even possible to have a provider to access some particular typ e of foreign database engine if there is some reason to want to avoid the ODBC layer. Or did I misinterpret 'is within reach'? -- Mark Rotteveel
Re: [firebird-support] EXECUTE STATEMENT problem understanding
On 28-7-2013 20:49, Alan J Davies wrote: Hi again all, thanks to Mark and Matkus I have successfully converted a number of SPs using this technique, but cannot get this particular version to work. I have tried every combination I can think of and looked and re-looked at the example code and my own code. The error I get every time (regardless of where the parameters are placed) is: The insert and delete options work perfectly Overflow occurred during data type conversion. conversion error from string CT . At procedure 'Q_UPD_TOOL_GAUGE_PPAP_PARTNO' line: 31, col: 9. What has the value CT , what is its datatype and what is the datatype of the column being inserted or updated? At first glance I'd say you try to insert a string into a numeric column. Mark -- Mark Rotteveel
Re: [firebird-support] EXECUTE STATEMENT problem understanding
Hi again all, thanks to Mark and Matkus I have successfully converted a number of SPs using this technique, but cannot get this particular version to work. I have tried every combination I can think of and looked and re-looked at the example code and my own code. The error I get every time (regardless of where the parameters are placed) is: The insert and delete options work perfectly Overflow occurred during data type conversion. conversion error from string CT . At procedure 'Q_UPD_TOOL_GAUGE_PPAP_PARTNO' line: 31, col: 9. If I step through the SP all the parameters display the correct data. create or alter procedure q_upd_tool_gauge_ppap_partno ( acno account_no, partno part_no, pjs_no decimals_0, new_pjs_no decimals_0, tgp char(1), actiontype integer) as declare variable table_to_use varchar(30); declare variable stmnt varchar(500); begin if (TGP='T') then table_to_use='q_tool_partno'; else if (TGP='G') then table_to_use='q_gauge_partno'; else if (TGP='P') then table_to_use='q_ppap_partno'; if (actiontype=1) then /* Update */ begin /* the actual operation update q_gauge_partno set pjs_no=:new_pjs_no where acno=:acno and partno=:partno and pjs_no=:pjs_no; end of actual operation */ stmnt= ' update '|| table_to_use || ' set pjs_no=:new_pjs_no where acno=:acno and partno=:partno and pjs_no=:pjs_no'; execute statement (stmnt) (acno:=acno,partno:=partno,pjs_no:=pjs_no,new_pjs_no:=new_pjs_no); end else if (ActionType=2) then /* Insert */ begin stmnt= ' insert into '|| table_to_use || ' (acno,partno,pjs_no) values (:acno,:partno,:pjs_no)'; execute statement (stmnt) (pjs_no:=pjs_no,acno:=acno,partno:=partno); end else if (ActionType=3) then /* Delete */ begin stmnt= ' delete from '|| table_to_use || ' where acno=:acno and partno=:partno and pjs_no=:pjs_no'; execute statement (stmnt) (acno:=acno,partno:=partno,pjs_no:=pjs_no); end when SQLCode -803 Do Exception insertException;/* Already On File */ when SQLCode -530 Do Exception deleteException;/* Deliveries On Order File */ end Alan J Davies Aldis +44 (0) 1926 842069 +44 (0) 7885 372793 On 26/07/2013 13:47, Mark Rotteveel wrote: On Fri, 26 Jul 2013 13:14:08 +0100, Alan J Davies alan.dav...@aldis-systems.co.uk mailto:Alan.Davies%40aldis-systems.co.uk wrote: Ok, Mark Markus, thanks again for both your help. I've tried this construct but get an error -206 column unknown pjs_no_in which is passed in as an input parameter declare variable table_to_use varchar(30); declare variable stmnt varchar(500); begin table_to_use='q_tool'; begin EXECUTE STATEMENT 'update '|| table_to_use || ' set tgp_no=:tgp_no,del_date=:del_date where (pjs_no=:pjs_no_in)'; Column does not belong to referenced table. Dynamic SQL Error. SQL error code = -206. Column unknown. PJS_NO_IN. You are not passing any parameters into the EXECUTE STATEMENT, so it does not know about pjs_no_in, del_date or tgp_no. The query executed cannot access parameters or variables declared in the stored procedure, you need to pass them explicitly. See http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-params See also my first mail for an example on how you pass in parameters. Mark
Re: [firebird-support] EXECUTE STATEMENT problem understanding
On Fri, 26 Jul 2013 09:50:43 +0100, Alan J Davies alan.dav...@aldis-systems.co.uk wrote: Hi all, I have a number of sps similar to this. Basically, I update, insert or delete according to the parameter actiontype Then according to the parameter tgp I use the q_tool table, the q_gauge table or the q_ppap table. Everything else is identical and basically I want one set of actions with the table name as a parameter (if I explain that correctly) along these lines just for the first option: create or alter procedure q_upd_tool_gauge_ppap_test ( pjs_no integer, tgp_no integer, del_date date, tgp char(1), actiontype integer) as declare variable s varchar(100); begin s='update q_tool'; if (TGP='T') then begin if (:actiontype=1) then /* Update */ begin execute statement (:s) /* q_tool */ ( tgp_no:=:tgp_no,del_date:=:del_date); where pjs_no=:pjs_no); end The statement to be passed to EXECUTE STATEMENT needs to be the entire statement (optionally with parametrized *values*). You are currently trying to pass a statement 'update q_tool', which is not valid (it is missing a 'SET'-clause, and you are trying to add a `WHERE`-clause to EXECUTE STATEMENT itself, which is not possible. What you need to do is roughly: s = 'update q_tool set tgp_no = :tgp_no, del_date = :del_date where pjs_no = :pjs_no' EXECUTE STATEMENT (s) (tgp_no := tgp_no, del_date := del_date, pjs_no := pjs_no); Mark
Re: [firebird-support] EXECUTE STATEMENT problem understanding
Thanks Mark, it appears that what I want to do is not possible. i.e. have the table name as a replaceable parameter. Using your example I would still have 3 separate statements (as now) but in a different format in the SP. What I really would like to be able to do is (paraphrase): declare table_to_use varchar(20); if my_input_parameter='T' then table_to_use='q_tool' else if my_input_parameter='G' then table_to_use='q_gauge' else if my_input_parameter='P' then table_to_use='q_ppap' end; and then this code only once in the SP. s = 'update :table_to_use set tgp_no = :tgp_no, del_date = :del_date where pjs_no = :pjs_no' EXECUTE STATEMENT (s) (tgp_no := tgp_no, del_date := del_date, pjs_no := pjs_no); Regards Alan Alan J Davies Aldis On 26/07/2013 10:07, Mark Rotteveel wrote: What you need to do is roughly: s = 'update q_tool set tgp_no = :tgp_no, del_date = :del_date where pjs_no = :pjs_no' EXECUTE STATEMENT (s) (tgp_no := tgp_no, del_date := del_date, pjs_no := pjs_no); Mark
Re: [firebird-support] EXECUTE STATEMENT problem understanding
On Fri, Jul 26, 2013 at 12:26 PM, Alan J Davies alan.dav...@aldis-systems.co.uk wrote: ** Thanks Mark, it appears that what I want to do is not possible. i.e. have the table name as a replaceable parameter. Using your example I would still have 3 separate statements (as now) but in a different format in the SP. What I really would like to be able to do is (paraphrase): declare table_to_use varchar(20); if my_input_parameter='T' then table_to_use='q_tool' else if my_input_parameter='G' then table_to_use='q_gauge' else if my_input_parameter='P' then table_to_use='q_ppap' end; and then this code only once in the SP. s = 'update :table_to_use set tgp_no = :tgp_no, del_date = :del_date where pjs_no = :pjs_no' EXECUTE STATEMENT (s) (tgp_no := tgp_no, del_date := del_date, pjs_no := pjs_no); Regards Alan Your s is just a string. You don't have to use parameters you can concatenate it like this: s = 'update ' || table_to_use || ' set.'; HTH, Markus [Non-text portions of this message have been removed]
Re: [firebird-support] EXECUTE STATEMENT problem understanding
On Fri, 26 Jul 2013 11:26:31 +0100, Alan J Davies alan.dav...@aldis-systems.co.uk wrote: Thanks Mark, it appears that what I want to do is not possible. i.e. have the table name as a replaceable parameter. Using your example I would still have 3 separate statements (as now) but in a different format in the SP. What I really would like to be able to do is (paraphrase): declare table_to_use varchar(20); if my_input_parameter='T' then table_to_use='q_tool' else if my_input_parameter='G' then table_to_use='q_gauge' else if my_input_parameter='P' then table_to_use='q_ppap' end; and then this code only once in the SP. s = 'update :table_to_use set tgp_no = :tgp_no, del_date = :del_date where pjs_no = :pjs_no' EXECUTE STATEMENT (s) (tgp_no := tgp_no, del_date := del_date, pjs_no := pjs_no); You can't pass the table name as parameter to EXECUTE STATEMENT, but nothing stops you from dynamically building the statement itself. Just be sure that the tablename is not userinput, otherwise you open yourself to SQL injection. Eg s = UPDATE || table_to_use || SET ... -- etc Mark
Re: [firebird-support] EXECUTE STATEMENT problem understanding
Ok, Mark Markus, thanks again for both your help. I've tried this construct but get an error -206 column unknown pjs_no_in which is passed in as an input parameter declare variable table_to_use varchar(30); declare variable stmnt varchar(500); begin table_to_use='q_tool'; begin EXECUTE STATEMENT 'update '|| table_to_use || ' set tgp_no=:tgp_no,del_date=:del_date where (pjs_no=:pjs_no_in)'; Column does not belong to referenced table. Dynamic SQL Error. SQL error code = -206. Column unknown. PJS_NO_IN. Alan J Davies Aldis On 26/07/2013 11:36, Markus Ostenried wrote: On Fri, Jul 26, 2013 at 12:26 PM, Alan J Davies alan.dav...@aldis-systems.co.uk mailto:Alan.Davies%40aldis-systems.co.uk wrote: ** Thanks Mark, it appears that what I want to do is not possible. i.e. have the table name as a replaceable parameter. Using your example I would still have 3 separate statements (as now) but in a different format in the SP. What I really would like to be able to do is (paraphrase): declare table_to_use varchar(20); if my_input_parameter='T' then table_to_use='q_tool' else if my_input_parameter='G' then table_to_use='q_gauge' else if my_input_parameter='P' then table_to_use='q_ppap' end; and then this code only once in the SP. s = 'update :table_to_use set tgp_no = :tgp_no, del_date = :del_date where pjs_no = :pjs_no' EXECUTE STATEMENT (s) (tgp_no := tgp_no, del_date := del_date, pjs_no := pjs_no); Regards Alan Your s is just a string. You don't have to use parameters you can concatenate it like this: s = 'update ' || table_to_use || ' set.'; HTH, Markus [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] EXECUTE STATEMENT problem understanding
On Fri, 26 Jul 2013 13:14:08 +0100, Alan J Davies alan.dav...@aldis-systems.co.uk wrote: Ok, Mark Markus, thanks again for both your help. I've tried this construct but get an error -206 column unknown pjs_no_in which is passed in as an input parameter declare variable table_to_use varchar(30); declare variable stmnt varchar(500); begin table_to_use='q_tool'; begin EXECUTE STATEMENT 'update '|| table_to_use || ' set tgp_no=:tgp_no,del_date=:del_date where (pjs_no=:pjs_no_in)'; Column does not belong to referenced table. Dynamic SQL Error. SQL error code = -206. Column unknown. PJS_NO_IN. You are not passing any parameters into the EXECUTE STATEMENT, so it does not know about pjs_no_in, del_date or tgp_no. The query executed cannot access parameters or variables declared in the stored procedure, you need to pass them explicitly. See http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-params See also my first mail for an example on how you pass in parameters. Mark
Re: [firebird-support] EXECUTE STATEMENT problem understanding
Thanks again Mark - it took a while to work out what was going on (doh!) but now it works very well and I can expand it to do what I want. Once again, thank you. Alan Just for interest (for anyone else) table_to_use='q_tool'; stmnt= 'update '|| table_to_use || ' set tgp_no=:tgp_no,del_date=:del_date where pjs_no=:pjs_no_in'; EXECUTE STATEMENT (stmnt) (tgp_no:=tgp_no,del_date:=del_date,pjs_no_in:=pjs_no_in); Alan J Davies Aldis
Re: [firebird-support] execute statement
On 9-8-2012 22:03, Sergio wrote: Hello! I'm trying to do a trigger to maintain a history table. I'm using (for the first time!) execute statement. What I want to do is very simple: if a field change I save the old value in the history When I execute the trigger I get an error: Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 1, column 1. if. I'm sure I'm using execute statement in the wrong way !!! this is the trigger: EXECUTE STATEMENT is for executing queries only, your code is not just a query as it contains an IF statement. I think for your purposes you need to use EXECUTE BLOCK http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-execblock.html Mark -- Mark Rotteveel
Re: [firebird-support] execute statement
Em 9/8/2012 17:03, Sergio escreveu: Hello! I'm trying to do a trigger to maintain a history table. I'm using (for the first time!) execute statement. What I want to do is very simple: if a field change I save the old value in the history When I execute the trigger I get an error: Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 1, column 1. if. I'm sure I'm using execute statement in the wrong way !!! this is the trigger: CREATE OR ALTER trigger tlm_maestro_au0 for tlm_maestro active after update position 0 AS declare variable loc_nuevo_id id; declare variable loc_ejecutar descripcion_larga; begin loc_nuevo_id = gen_id(gen_tlm_maestro_hist,1); insert into tlm_maestro_hist (id,modificado) values (:loc_nuevo_id, current_timestamp); for select 'if (new.' || trim(rdb$field_name) || ' is distinct from old.' || trim(rdb$field_name) || ') then update tlm_maestro_hist set ' || trim(rdb$field_name) || ' = old.' || trim(rdb$field_name) || ' where id = :loc_nuevo_id;' from rdb$relation_fields where rdb$relation_name = 'TLM_MAESTRO' into :loc_ejecutar do begin execute statement loc_ejecutar; end end There is no IF fucntion in FB, did you meant IIF ??? see you !
Re: [firebird-support] execute statement
On 9-8-2012 22:18, Alexandre Benson Smith wrote: There is no IF fucntion in FB, did you meant IIF ??? IF does exist in PSQL. Mark -- Mark Rotteveel
Re: [firebird-support] execute statement
Em 9/8/2012 17:20, Mark Rotteveel escreveu: On 9-8-2012 22:18, Alexandre Benson Smith wrote: There is no IF fucntion in FB, did you meant IIF ??? IF does exist in PSQL. Mark Yes, I know... But at first glance I thought he was trying to use IIF since it's inside a SELECT statement. After you mentioned EXECUTE BLOCK I re-read the post and saw what he really wants to do...
Re: [firebird-support] Execute Statement problem with timestamp
At 12:18 PM 24/11/2011, you wrote:
I've a little problem with a sql statement:
s_stmmain = 'insert into ' || :s_stmtable || '(kd_id, fnr, fbeginn, fende)
values ('
|| :t_timestamp || ',' || cast(:i_fnr as varchar(4)) || ','
|| '' || ',' || '' || ')';
execute statement s_stmmain;
t_timestamp is a timestamp value and has a blank character. Now, I have test
it with quotest , but it does not work.
Two problems here:
1. Don't use the colon prefix when attempting to concatenate a variable in an
exe-string. The colon is to be used when the variable is passed in a direct
SQL statement ONLY.
2. When passing an apostrophe as a literal you have to escape it with
another apostrophe. That is, two single quote characters.
Try this:
s_stmmain = 'insert into ' || s_stmtable || ' (kd_id, fnr, fbeginn, fende)
values (''' --- 3 apostrophes
|| t_timestamp
|| ''', ' --- 3 apostrophes + comma + 1 apostrophe
|| cast(i_fnr as varchar(4))
|| ','
|| ''
|| ', '
|| '' || ')';
./heLen
