Re: [firebird-support] Need a little help with EXECUTE STATEMENT

[SoftTech]

Greetings Martijn and Mark,

You're exactly right that fixed the problem. 

Mark it also fixed this error
ISC ERROR CODE: 335544606
ISC ERROR MESSAGE:
expression evaluation not supported

Thanks to both of you for taking the time to respond,
Mike



  - Original Message - 
  From: Martijn Tonies 
  To: firebird-support@yahoogroups.com 
  Sent: Wednesday, January 09, 2013 8:14 AM
  Subject: {Disarmed} Re: [firebird-support] Need a little help with EXECUTE 
STATEMENT



  Hello Mike,

  > DECLARE VARIABLE tsEndDate TimeStamp;
  > begin
  > tsEndDate = :V_END_DATE || ' 23:59:59';
  > 
  > 
  > EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
  > FROM DEBT D
  > WHERE D.CREATE_DATE <= ' || tsEndDate
  > INTO :FIRST_CREATE_DATE;
  > 
  > both caused this error.
  > 
  > ISC ERROR CODE: 335544569
  > ISC ERROR MESSAGE:
  > Dynamic SQL Error
  > SQL error code = -104
  > Token unknown - line 7, char 141
  > 23
  > 
  > What am I doing wrong? Any work arounds?

  This ends up being (for V_END_DATE = 2013/1/09)

  WHERE D.CREATE_DATE <= 9/1/2013 23:59:59

  Do you see the problem? It becomes un-quoted...

  Try:

  WHERE D.CREATE_DATE <= ''' || tsEndDate || 
  INTO ...

  Where ''' = ' ' ' (with no spaces) and  = ' ' ' ' (with no spaces)

  With regards,

  Martijn Tonies
  Upscene Productions
  http://www.upscene.com

  Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
  Anywhere, MySQL, InterBase, NexusDB and Firebird!


  
  -- 
  This message has been scanned for viruses and 
  dangerous content by MailScanner, and is 
  believed to be clean. 

[Non-text portions of this message have been removed]

Re: [firebird-support] Exception handling in loop

[Thomas Steinmaurer]

> Since a block wasn't exited before the exception was handled, wouldn't
> any database statements in the block stand?
> Assume for a moment that it wasn't a constraint violation, but a table
> trigger throwing a custom exception, and the catch block handles it
> instead of handling constraint violations.
>
> FB exception syntax confuses me, as I'm used to other languages where
> the catch is outside the block.

E.g. like in Java? Right.

try {
   ...
} catch ... {
   ...
}


The catch (WHEN) block in Firebird PSQL needs to be at the last part of 
a BEGIN ... END block.

While you can't do something like that (i = 1 after the exception 
handler in the some begin/end block):

declare i integer;
begin
   i = 0;
   begin
 while (i <= 20) do
 begin
   i = i + 1;
   insert into t4(T4_ID) values (:i);
   when any do
   begin
   end
   i = 1;
 end
   end
end


It's fine to continue with something in context of the outer begin/end 
block, e.g.:

declare i integer;
begin
   i = 0;
   begin
 while (i <= 20) do
 begin
   i = i + 1;
   insert into t4(T4_ID) values (:i);
   when any do
   begin
   end
 end
 i = 1;
   end
end


Regards
Thomas

RE: [firebird-support] Unique key exceptions & triggers

[Rick Debay]

Thanks, I'm trying to follow Arthur Conan Doyle's advice "Once you
eliminate the impossible, whatever remains, no matter how improbable,
must be the truth" and turn improbabilities to impossibilities.

Rick DeBay

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas
Steinmaurer
Sent: Thursday, January 10, 2013 2:03 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Unique key exceptions & triggers

> If I insert duplicate rows, I assume the order of operations is:
>
> Before triggers
> Insert
> Exception
> After triggers <-- never executed

Correct.

This can be easily checked with the Trace API. The execution order of
the trace events are:

EXECUTE_STATEMENT_START (insert into)
EXECUTE_TRIGGER_START (before insert)
EXECUTE_TRIGGER_FINISH (before insert)
FAILED EXECUTE_STATEMENT_FINISH (insert into) ERROR AT jrd8_execute


Regards,
Thomas




++

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

RE: [firebird-support] Exception handling in loop

[Rick Debay]

Since a block wasn't exited before the exception was handled, wouldn't
any database statements in the block stand?
Assume for a moment that it wasn't a constraint violation, but a table
trigger throwing a custom exception, and the catch block handles it
instead of handling constraint violations.

FB exception syntax confuses me, as I'm used to other languages where
the catch is outside the block.

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas
Steinmaurer
Sent: Thursday, January 10, 2013 1:51 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Exception handling in loop

> I want to select a bunch of items and insert them somewhere else.  If 
> the insert fails because of a duplicate primary key or unique 
> constraint, I want to abandon that particular item and continue with 
> the rest.
> This code outlines my logic.  Is this correct for what I'm trying to 
> accomplish?
>
> BEGIN
>
>FOR SELECT DO BEGIN
>
>  BEGIN
>
>INSERT
>
>  END /* INSERT */
>
>  WHEN GDSCODE unique_key_violation DO
>  BEGIN
>/* NOP */
>  END
>
>END /* SELECT */
>
> END /* PROCEDURE */

I guess this should be:

BEGIN
   FOR SELECT DO
   BEGIN
 INSERT
 WHEN GDSCODE unique_key_violation DO
 BEGIN
   /* NOP */
 END
   END /* SELECT */
END /* PROCEDURE */



Regards,
Thomas














++

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

Re: [firebird-support] Unique key exceptions & triggers

[Thomas Steinmaurer]

> If I insert duplicate rows, I assume the order of operations is:
>
> Before triggers
> Insert
> Exception
> After triggers <-- never executed

Correct.

This can be easily checked with the Trace API. The execution order of 
the trace events are:

EXECUTE_STATEMENT_START (insert into)
EXECUTE_TRIGGER_START (before insert)
EXECUTE_TRIGGER_FINISH (before insert)
FAILED EXECUTE_STATEMENT_FINISH (insert into)
ERROR AT jrd8_execute


Regards,
Thomas

Re: [firebird-support] Exception handling in loop

[Thomas Steinmaurer]

> I want to select a bunch of items and insert them somewhere else.  If
> the insert fails because of a duplicate primary key or unique
> constraint, I want to abandon that particular item and continue with the
> rest.
> This code outlines my logic.  Is this correct for what I'm trying to
> accomplish?
>
> BEGIN
>
>FOR SELECT DO BEGIN
>
>  BEGIN
>
>INSERT
>
>  END /* INSERT */
>
>  WHEN GDSCODE unique_key_violation DO
>  BEGIN
>/* NOP */
>  END
>
>END /* SELECT */
>
> END /* PROCEDURE */

I guess this should be:

BEGIN
   FOR SELECT DO
   BEGIN
 INSERT
 WHEN GDSCODE unique_key_violation DO
 BEGIN
   /* NOP */
 END
   END /* SELECT */
END /* PROCEDURE */



Regards,
Thomas

Re: [firebird-support] Firebird trace config problem

[Thomas Steinmaurer]

Hello Karol,

> i try to use new trace facilities but without success
>
> only what i got is trace init and nothing more in log file
> 2013-01-10T08:57:52.9230 (3628:0090DA30) TRACE_INIT
>   SESSION_1 Firebird Audit > regards,
> Karol Bieniaszewski
>
> my config look like this
>
>
> 

How does your connect string look like?

I first would try a different database filter or as a start, no database 
filter at all.

Regards,
Thomas

[firebird-support] Unique key exceptions & triggers

[Rick Debay]

If I insert duplicate rows, I assume the order of operations is:

Before triggers
Insert
Exception
After triggers <-- never executed

[firebird-support] Exception handling in loop

[Rick Debay]

I want to select a bunch of items and insert them somewhere else.  If
the insert fails because of a duplicate primary key or unique
constraint, I want to abandon that particular item and continue with the
rest.
This code outlines my logic.  Is this correct for what I'm trying to
accomplish?

BEGIN

  FOR SELECT DO BEGIN

BEGIN

  INSERT

END /* INSERT */

WHEN GDSCODE unique_key_violation DO
BEGIN
  /* NOP */
END 

  END /* SELECT */

END /* PROCEDURE */

Re: [firebird-support] Re: Firebird & Windows 3.11

[Werner F. Bruhin]

On 10/01/2013 11:14, mariuz wrote:
>
>
>
> --- In firebird-support@yahoogroups.com 
> , "Andy Gable" wrote:
> >
> > Yes they would BUT I can only code in BASIC (QuickBasic for MS-DOS and
> > Visual Basic for windows)
>
Have you already written the POS?

If not or if it is early days you might want to consider:

http://sourceforge.net/directory/os:windows/freshness:recently-updated/?q=Point%20of%20sale
and join the developer team of one of them.

Use a dev tool which is multi platform, I use wxPython, there is also 
Dabo (a ui framework using wxPython) - http://dabodev.com/

Werner


[Non-text portions of this message have been removed]

[firebird-support] Re: Firebird & Windows 3.11

[mariuz]

--- In firebird-support@yahoogroups.com, "Andy Gable"  wrote:
>
> Yes they would BUT I can only code in BASIC (QuickBasic for MS-DOS and
> Visual Basic for windows)
> 
> -Original Message-
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Anderson Farias
> Sent: 08 January 2013 3:53 PM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Firebird & Windows 3.11
> 
> 
>   Wouldn't those machines run Linux fine ? I guess a lot better option than
> go with such a ancient OS.
> 
> --- Em ter, 8/1/13, Andy Gable  escreveu:
> I write EPoS Software for small shops who can not afford the new Windows 7
> machines
> 
> And some of the machines I get can handle windows 98 normally but I have had
> a batch that only handles windows 3.11


You can also try Gambas , seems to have Firebird support 
http://gambas.sourceforge.net/en/main.html

Another alternative is Lazarus 
http://www.lazarus.freepascal.org/

ps: When i saw this thread i thought that is 90's again and yahoo group digged 
a old thread from 2000

[firebird-support] Firebird trace config problem

[karolbieniaszewski]

Hi,

i try to use new trace facilities but without success

only what i got is trace init and nothing more in log file
2013-01-10T08:57:52.9230 (3628:0090DA30) TRACE_INIT
SESSION_1 Firebird Audit

regards,
Karol Bieniaszewski

my config look like this



# Do we trace database events or not
enabled true

# Operations log file name. For use by system audit trace only
log_filename "C:\\Program 
Files\\Firebird\\Firebird_2_5\\trace_tracker_log.log" 

# Maximum size of log file (megabytes). Used by system audit trace for 
# log's rotation : when current log file reached this limit it is 
renamed
# using current date and time and new log file is created. Value of 
zero 
# means that the log file size is unlimited and rotation will never 
happen.
max_log_size 0


# SQL query filters. 
#
# Only SQL statements falling under given regular expression are 
reported 
# in the log.
#include_filter 

# SQL statements falling under given regular expression are NOT 
reported 
# in the log.
#exclude_filter 


# Put attach/detach log records 
log_connections true

# Trace only given connection id. If zero - trace all connections 
connection_id 0

# Put transaction start/end records 
log_transactions true


# Put sql statement prepare records 
log_statement_prepare true

# Put sql statement free records 
log_statement_free true

# Put sql statement execution start records 
log_statement_start true

# Put sql statement execution finish\fetch to eof records 
log_statement_finish true

# Put record when stored procedure is start execution 
log_procedure_start true

# Put record when stored procedure is finish execution 
log_procedure_finish true

# Put trigger execute records 
log_trigger_start true

# Put trigger execute records 
log_trigger_finish true

# Put context variable change records (RDB$SET_CONTEXT)
log_context true


# Print access path (plan) with sql statement
print_plan true

# Print detailed performance info when applicable
print_perf true


# Put blr requests compile/execute records 
log_blr_requests true

# Print blr requests or not
print_blr true

# Put dyn requests execute records 
log_dyn_requests true

# Print dyn requests or not
print_dyn true


# Put xxx_finish record only if its timing exceeds this number of 
milliseconds
time_threshold 0

# Maximum length of SQL string logged 
# Beware when adjusting max_xxx parameters! Maximum length of log record
# for one event should never exceed 64K.
max_sql_length 3

# Maximum length of blr request logged 
max_blr_length 3

# Maximum length of dyn request logged 
max_dyn_length 3

# Maximum length of individual string argument we log 
max_arg_length 800

# Maximum number of query arguments to put in log 
max_arg_count 80