Re: [SPAM] Re: [firebird-support] Firebird : find strign in all database

['Neville Richards' nevi...@meltonisl.com [firebird-support]]

What you’ve written is a valiant effort, but shows why I think it’s better to 
use external programming to generate the SQL needed.  Programming languages 
like c, c++, pascal etc., give far more fine control in doing such a task, have 
well-defined control statements, and are able to be debugged statement by 
statement to find errors which are so easy to make.  A typical error in putting 
together such code to create SQL where it will contain a concatenation of many 
parts, some literal and some variable, it the simple omission of space 
characters where they are needed.

I’m afraid that I have used the procedural bits of Firebird SQL very little, 
and always find it a stress when I have to.  It seems very clunky and difficult 
to manage to me, compared with programming languages which are designed to be 
procedural.

Neville

From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, January 11, 2017 9:02 AM
To: firebird-support@yahoogroups.com 
Subject: [SPAM] Re: [firebird-support] Firebird : find strign in all database

  

Hi thanks for your reply



i have this code but something gone wrong



SET TERM !;

EXECUTE BLOCK 

RETURNS (DANSTABLE CHAR(31), DANSCOLONNE CHAR(31) )

AS

DECLARE VARIABLE STMT VARCHAR(250); 

DECLARE VARIABLE CHAINE VARCHAR(50);

DECLARE VARIABLE RESULT SMALLINT;

BEGIN

CHAINE='ACHERCHER';

 FOR SELECT r.RDB$FIELD_NAME,

r.RDB$RELATION_NAME

FROM RDB$RELATION_FIELDS r LEFT JOIN RDB$FIELDS F ON r.RDB$FIELD_SOURCE = 
f.RDB$FIELD_NAME

WHERE r.RDB$SYSTEM_FLAG=0 AND f.RDB$FIELD_TYPE IN (37,14) AND 
f.RDB$FIELD_LENGTH>=9 

INTO :DANSCOLONNE,:DANSTABLE

DO BEGIN 

   STMT='SELECT 1 FROM '||:DANSTABLE||' WHERE '||:DANSCOLONNE||' = ?';

   EXECUTE STATEMENT (STMT) (CHAINE) INTO :RESULT;

   IF (RESULT IS NOT NULL) THEN SUSPEND;

 END  

END!



SET TERM ; !

Re: [firebird-support] Firebird : find strign in all database

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 11-1-2017 10:35, startx252...@yahoo.fr [firebird-support] wrote:
>
>
> i mean taht i have no result (it's empty) and i am sure my string existe
> in one table

That is probably because you are using

' WHERE '||:DANSCOLONNE||' = ?'

instead of

' WHERE '||:DANSCOLONNE||' = ''%'' || ? || ''%'''

or

' WHERE '||:DANSCOLONNE||' CONTAINING ?'

You might also want to account for case sensitivity, etc.

Also `EXECUTE STATEMENT (STMT) (CHAINE) INTO :RESULT;` won't work 
properly if there is more than one row, you might want to switch to 
using `for execute statement` (see 
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-forexec)

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Firebird : find strign in all database

[startx252...@yahoo.fr [firebird-support]]

i mean taht i have no result (it's empty) and i am sure my string existe in one 
table

Re: [firebird-support] Firebird : find strign in all database

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 11-1-2017 10:02, startx252...@yahoo.fr [firebird-support] wrote:
> i have this code but something gone wrong

Please be more specific than "something gone wrong"

Mark

-- 
Mark Rotteveel

Re: [firebird-support] Firebird : find strign in all database

[startx252...@yahoo.fr [firebird-support]]

Hi thanks for your reply
  
 i have this code but something gone wrong
  
 SET TERM !;
 EXECUTE BLOCK 
 RETURNS (DANSTABLE CHAR(31), DANSCOLONNE CHAR(31) )
 AS
 DECLARE VARIABLE STMT VARCHAR(250); 
 DECLARE VARIABLE CHAINE VARCHAR(50);
 DECLARE VARIABLE RESULT SMALLINT;
 BEGIN
  CHAINE='ACHERCHER';
  FOR SELECT r.RDB$FIELD_NAME,
 r.RDB$RELATION_NAME
 FROM RDB$RELATION_FIELDS r LEFT JOIN RDB$FIELDS F ON r.RDB$FIELD_SOURCE = 
f.RDB$FIELD_NAME
 WHERE r.RDB$SYSTEM_FLAG=0 AND f.RDB$FIELD_TYPE IN (37,14) AND 
f.RDB$FIELD_LENGTH>=9 
 INTO :DANSCOLONNE,:DANSTABLE
  DO BEGIN 
STMT='SELECT 1 FROM '||:DANSTABLE||' WHERE '||:DANSCOLONNE||' = ?';
EXECUTE STATEMENT (STMT) (CHAINE) INTO :RESULT;
IF (RESULT IS NOT NULL) THEN SUSPEND;
  END  
 END!
  
 SET TERM ; !
  

Re: [SPAM] [firebird-support] Firebird : find strign in all database

['Neville Richards' nevi...@meltonisl.com [firebird-support]]

I think you would probably have to provide some external programming to solve 
this.  I can't see a way of putting a SQL solution together.

Using external programming (any of the usual development systems) you can get a 
list of tables, together with a list of fields they contain, and the data types 
they have.  You can then programmatically construct SQL (for each table) 
something like:
select * from  where  etc   like ‘%%’

It might take a long time though.

Neville Richards

From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, January 10, 2017 5:19 PM
To: firebird-support@yahoogroups.com 
Subject: [SPAM] [firebird-support] Firebird : find strign in all database

  

Hi all,



I am looking for a procedure or a function to find a stirng in my database

I have a DB with 200 tables and want to find a specific string (record) in a my 
DB 

I don't know the name of table / field.




Thanks for any help

Re: [firebird-support] Firebird : find strign in all database

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
 
rather simple operation but can take very long time if database is big.
You can write stored procedure - or EXECUTE BLOCK
and inside iterate by system tables RDB$RELATIONS and RDB$RELATION_FIELDS
and do EXECUTE STATEMENT with where condition
and return back sum of fields '||' and table name where it find something 
interesting
 
regards,
Karol Bieniaszewski
 
 
W dniu 2017-01-10 18:19:34 użytkownik startx252...@yahoo.fr [firebird-support] 
 napisał:
 
Hi all,
 
I am looking for a procedure or a function to find a stirng in my database
I have a DB with 200 tables and want to find a specific string (record) in a my 
DB
I don't know the name of table / field.
 
 
Thanks for any help
 

[firebird-support] Firebird : find strign in all database

[startx252...@yahoo.fr [firebird-support]]

Hi all,
  
 I am looking for a procedure or a function to find a stirng in my database
 I have a DB with 200 tables and want to find a specific string (record) in a 
my DB 
 I don't know the name of table / field.
  
 
 Thanks for any help