Re: [firebird-support] Create view on external table

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Eric,

Yes, the optimizer can use an index with a WHERE on a VIEW, but only when the 
view is local, not when using EXECUTE STATEMENT on an external database.

With regards,

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



From: Eric Guéguiniat eric.gueguin...@gmail.com [firebird-support] 
Sent: Monday, January 6, 2020 4:52 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Create view on external table




Index are used with a view when the source is a table and not if it's a stored 
proc 
This view CUST_VIEW use index : SELECT * FROM CUSTOMER => SELECT * FROM 
CUST_VIEW WHERE IDCUSTOMER = 1 , INDEX on IDCUSTOMER is used
The view CUST_SPVIEW don't use index : select * FROM STORED_PROC_CUSTOMER =>  
SELECT * FROM CUST_SPVIEW WHERE IDCUSTOMER = 1 , No index

That why I need to select directly an external table in a view, instead a 
stored procedure

Thanks
Eric



Le lun. 6 janv. 2020 à 16:35, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support]  a écrit :


  06.01.2020 14:48, Eric Guéguiniat eric.gueguin...@gmail.com 
[firebird-support] wrote:
  > I can't use Stored procedure to do this, because index are not used with it

  Index cannot be used with ES either, so there is no difference and no need in 
a view.

  -- 
  WBR, SD.

Re: [firebird-support] Off-Topic: Firebird future

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

I wonder, Lucas, what will you be publishing then? :)



With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

-Original Message- 

Am 22.10.2019 um 15:24 schrieb Stefan Heymann li...@stefanheymann.de
[firebird-support]:
>>> If you want to have it for free, like in
>>> free beer, you can sit down, write it and publish it
>> This is the plan. ;-)
>> Gabor
>
> Sounds like a good plan :-))


Yes, I can confirm this, we tested that plan very thoroughly.

Worked like a charm.


Lucas




Posted by: Lucas Franzen 


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] Firebird vs. PostgreSQL

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Alexey,

>What about compilation of stored procedures - is it true that it is
>possible to create SP with errors, and they appear only when it will be
>invoked?

Yes, some errors are 'allowed', like a mismatch in parameter name in
declaration and code. You'll get an error when executing, eg:

' column "p_inventory_id2" does not exist '

A wrong variable name as the target for an assignment, however, isn't 
allowed.

>Also, about checking the parameters - is it true that you can create
>stored procedure which invokes other stored procedure with wrong list of
>parameters, and it will give error only at the execution time?

Yes, correct. Example:
CREATE OR REPLACE FUNCTION public.last_day(Timestamp)

Call v_d = last_day(); results in:
' function last_day() does not exist '

PostgreSQL supports multiple stored routine languages out of the box: 'sql'
and 'plpgsql', with different (weird) syntax.

And it supports parameter overloading > functions with the same name but
a different number of parameters.

Also, triggers are different > you need a stored function that returns 
'trigger',
and when creating a trigger, you assign it that function. Triggers don't 
have
code.


Hope this helps.


With regards,

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


On 07.11.2018 19:27, 'Martijn Tonies (Upscene Productions)'
m.ton...@upscene.com [firebird-support] wrote:
> Yes, it's true and it's rather annoying when, for example, developing an
> interactive database development tool;)







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links










++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Firebird vs. PostgreSQL

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Alexey,

>What about compilation of stored procedures - is it true that it is
>possible to create SP with errors, and they appear only when it will be
>invoked?

Unsure, will try tomorrow. Do note, that for example, Oracle allows you to
'save' or 'compile' invalid stored routines > they are stored in the 
database
anyway and you can ask for a list of errors. In Database Workbench, these
routines are shown in red as to signal they are currently invalid.

>Also, about checking the parameters - is it true that you can create
>stored procedure which invokes other stored procedure with wrong list of
>parameters, and it will give error only at the execution time?

I'll let you know.


With regards,

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

Regards,
Alexey



On 07.11.2018 19:27, 'Martijn Tonies (Upscene Productions)'
m.ton...@upscene.com [firebird-support] wrote:
> Yes, it's true and it's rather annoying when, for example, developing an
> interactive database development tool;)







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links










++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Firebird vs. PostgreSQL

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Yes, it's true and it's rather annoying when, for example, developing an 
interactive database development tool ;)


With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

-Original Message- 
From: Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
Sent: Wednesday, November 07, 2018 5:17 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird vs. PostgreSQL

07.11.2018 17:11, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
> Wow is this true?

https://www.tutorialspoint.com/postgresql/postgresql_transactions.htm

"Transactions can be started using BEGIN TRANSACTION or simply BEGIN 
command. Such
transactions usually persist until the next COMMIT or ROLLBACK command is 
encountered. But
a transaction will also ROLLBACK if the database is closed *or if an error 
occurs.*"


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links










++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Firebird and Windows 10 update

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

Could this be related to:
https://blog.mertech.com/windows-10-version-1803-breaks-some-shared-folder-applications

"This means that applications started from these environments can’t open a 
connection to a database server."


With regards,

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


On 15-5-2018 23:50, fo...@darsys.com.ar [firebird-support] wrote:
> Since few hours ago, in some customers after Windows 10 update, my app
> can not connect to server.
>
> Fb server works, and clients with previous Windows versions work.
>
>
> Error; 8004 - Unable to complete server request to host...
>
>
> Fb 2.5.6 .7 .8
>
> Tried with odbc 2.02, 2.03 2.05

Re: [firebird-support] Firebird 3 advanced plan output > how to use this with the API?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mark,

>>> m.ton...@upscene.com [firebird-support] wrote:
 I wonder, how can applications use this feature
 https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-planoutput.html
>>>
>>> What do you mean? How to parse it, or how to get it?
>>
>> How to get it, without using isql.
>
>Vlad already answered that:
>
> > Pass isc_info_sql_explain_plan (value 26) into isc_dsql_info()
>instead of isc_info_sql_get_plan (value 22)

I missed that, thanks.


With regards,

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

Re: [firebird-support] Firebird 3 advanced plan output > how to use this with the API?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

>On 23-2-2018 11:58, 'Martijn Tonies (Upscene Productions)'
>m.ton...@upscene.com [firebird-support] wrote:
>> I wonder, how can applications use this feature
>> https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-planoutput.html
>
>What do you mean? How to parse it, or how to get it?

How to get it, without using isql.


With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird. 

Re: [firebird-support] Firebird 3 advanced plan output > how to use this with the API?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mark,

How to get it, without using isql.


With regards,

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


-Original Message- 
From: Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
Sent: Friday, February 23, 2018 1:03 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird 3 advanced plan output > how to use 
this with the API?

On 23-2-2018 11:58, 'Martijn Tonies (Upscene Productions)'
m.ton...@upscene.com [firebird-support] wrote:
> I wonder, how can applications use this feature
> https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-planoutput.html

What do you mean? How to parse it, or how to get it?

Mark
-- 
Mark Rotteveel



Posted by: Mark Rotteveel <m...@lawinegevaar.nl>


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

[firebird-support] Firebird 3 advanced plan output > how to use this with the API?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

I wonder, how can applications use this feature
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-planoutput.html



With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

Re: [firebird-support] Insert error: count of column and variable list do not match

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

Remove the VALUES(( part

There’s no need for a VALUES keyword if you use a SELECT to insert rows.

With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

From: talorigo...@yahoo.co.uk [firebird-support] 
Sent: Friday, December 29, 2017 3:32 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Insert error: count of column and variable list do 
not match




I'm trying to insert data from a query into a table however I receive the 
following error message:



SQL Message : -104

Invalid token



Engine Code: 335544569

Engine Message :

Dynamic SQL Error

SQL error code = -104

Invalid command

count of column list and variable list do not match



The table is defined as:



CREATE GLOBAL TEMPORARY TABLE tmp_gdlmove

(

  verid BIGINT,

  level_num SMALLINT,

  parentid BIGINT,

  times_assigned SMALLINT,

  journey CHAR(1)

)

ON COMMIT DELETE ROWS;



and the query I'm trying is defined as:



INSERT INTO tmp_gdlmove (verid, level_num, parentid, times_assigned) 

VALUES((SELECT verid,  level_num, parentid, count(*) as times_assigned

   FROM (SELECT DIS   FROM ASSIGNMENT ass

INNER JOIN orgchart och on 
och.childid=ass.orgid

INNER JOIN organisation org on 
och.parentid=org.orgid

WHERE verid IN (3) AND level_num IN (1,2) and 
depth =1)

   GROUP BY verid, level_num, parentid))






The select portion of the query returns the same number of columns as I have 
listed in the insert statement so I'm unsure why I receive this message.

Re: [firebird-support] FB 3 add description to table/ column

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Michał,

Firebird 3 uses the following syntax:
COMMENT ON   IS 'value'
COMMENT ON COLUMN . IS 'value'


With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.


-Original Message- 

Code from fb 2.5  does not work:

update rdb$relation_fields set rdb$description = 'aaa'
where rdb$field_name = 'new_name" and rdb$relation_name  = 'table_name'

Update operation is not allowed for system table rdb$ralation_fields

FB 3.0.2.32692

-- 
pozdr,
Michał Kurczabiński

Re: [firebird-support] Re: Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

>28.03.2017 10:06, Martijn Tonies wrote:
>>
>> So the first 'fetch' operation on a position that's invalid returns
>> "rowcount=0",
>> any additional fetch (which includes a move) returns an exception?
>
>Yes. Moving cursor from "last" to EOF is perfectly valid, "no data" 
>condition is not an error (as per SQL spec). After that, you may fetch 
>prior and get the last record back. Moving cursor from EOF further can 
>be theoretically processed in two ways: (1) silent no-op (cursor remains 
>its position at EOF) and (2) raise an error. The former is somewhat 
>error prone (can lead to infinite loops, for example), perhaps this is 
>the reason why the engine raises an error instead. This is the logic 
>existing since InterBase for DSQL cursors, and it was applied to PSQL 
>cursors for consistency.

Right, thanks. Just wanted to get things straight here. 

Got this working in the debugger ;)


With regards,

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

Re: [firebird-support] Re: Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

So the first 'fetch' operation on a position that's invalid returns 
"rowcount=0",
any additional fetch (which includes a move) returns an exception?

That seems to be the case.


With regards,

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


-Original Message- 
From: Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]
27.03.2017 11:23, 'Martijn Tonies wrote:
>
> The FOR SELECT loop returns 1..10 as the values.
>
> The fetch/suspend lines return 1..10, with a row count of 1, and another
> ‘10’ with a row count of 0.
>
> If I add another FETCH, I get this error while executing the routine:
>>>attempt to fetch past the last record in a record stream At procedure
> 'DEBUG_CURSOR_TESTS' line: 42, col: 3
>
> And here’s my question: the last fetch has a row_count of 0, another
> fetch returns an error. However, if I make this cursor a scrollable
> cursor, and my first ‘fetch’ is a ‘fetch prior’, I immediately get this
> error:
>>>attempt to fetch before the first record in a record stream At
> procedure 'DEBUG_CURSOR_TESTS' line: 9, col: 3
>
> I get the ‘past last record’ error when I use:
> fetch absolute 11 from c into :p_out1;
>
> I wonder, should the first fetch next that returns no data, return
> row_count=0, or also the ‘fetch past record stream’ error?

AFAIU, this is expected. When the cursor moves past the last record, "no
data" condition is returned. In API, this means fetch() returning 100
(EOF marker). Inside PSQL, row_count is set to 0 that also indicates
EOF. However, moving past EOF is not supported and it throws an error.
It may look counter-intuitive to someone, but this is how our engine
always worked (internally or via API).

[firebird-support] Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

I’m unsure if this is an error or the intended behaviour.

Got a simply table “dummy_rows_10”, single column, integer values 1..10

Next, this procedure:

SET TERM ^^ ;
CREATE OR ALTER PROCEDURE DEBUG_CURSOR_TESTS returns (
  P_OUT1 TYPE OF COLUMN DUMMY_ROWS_10.V, 
  RC Integer)
AS
declare c  cursor for (select v from dummy_rows_10 order by 1);
begin
  for select v from dummy_rows_10 order by 1
  into p_out1
  do suspend;
  open c;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend; 
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend; 
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;

  /*fetch prior from c into :p_out1;
  suspend;  
 
  /*suspend;
  fetch prior from c into :p_out1;
  suspend;
  fetch last from c into :p_out1;
  suspend;
  fetch prior from c into :p_out1;
  suspend;  
  fetch prior from c into :p_out1;  
  /*fetch relative p_out1 - 1 from c into :p_out1;
  suspend; */  
end ^^
SET TERM ; ^^


The FOR SELECT loop returns 1..10 as the values.

The fetch/suspend lines return 1..10, with a row count of 1, and another ‘10’ 
with a row count of 0.

If I add another FETCH, I get this error while executing the routine:
>>attempt to fetch past the last record in a record stream At procedure 
>>'DEBUG_CURSOR_TESTS' line: 42, col: 3

And here’s my question: the last fetch has a row_count of 0, another fetch 
returns an error. However, if I make this cursor a scrollable cursor, and my 
first ‘fetch’ is a ‘fetch prior’, I immediately get this error:
>>attempt to fetch before the first record in a record stream At procedure 
>>'DEBUG_CURSOR_TESTS' line: 9, col: 3

I get the ‘past last record’ error when I use: 
fetch absolute 11 from c into :p_out1;


I wonder, should the first fetch next that returns no data, return row_count=0, 
or also the ‘fetch past record stream’ error?

With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

Re: [firebird-support] Read only transaccions and generators

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Aldo,

Generators are NOT influenced by transactions, so yes, that is correct.


With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

-Original Message- 
From: Aldo Caruso aldo.car...@argencasas.com [firebird-support]
Sent: Tuesday, March 14, 2017 12:13 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Read only transaccions and generators

Hello,

 I discovered that a read only transaction can increment a generator.

 Is that right ?

Thanks

Aldo





Posted by: Aldo Caruso 


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] Fb 3: error code 336068898 when recreating package

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dimitry,

>05.01.2017 12:17, 'Martijn Tonies (Upscene Productions)' 
>m.ton...@upscene.com
>[firebird-support] wrote:
>> Any idea what this error code means?
>
>   "Default values for parameters are allowed only in declaration of 
> packaged function @1.@2"

Thanks.

>   Make sure that you use fbclient.dll and firebird.msg of version 3.

I didn't the '@', that I usually see when mismatching fbclient/firebird.msg 
;)


With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird. 







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Fb 3: error code 336068898 when recreating package

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

Any idea what this error code means?

RECREATE PACKAGE BODY PCK_TEST failed
unknown ISC error 336068898
while executing:
RECREATE PACKAGE BODY PCK_TEST AS
begin
... code




With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

Re: [firebird-support] info

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Nico,

>I want to create a tablename in camelcase without using quotes.
>
>If I execute Create table MyTable(aField integer) then the tablename is 
>saved as MYTABLE. And yes then you can use camelcase >without quotes.
>
>Can you find a solution ?

The only solution to preserving case, is to use double quotes around the 
object names.




With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird. 

Re: [firebird-support] info

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Nico,

If you want the case preserved, you have to use double quotes, always.


With regards,

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

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

We want to create table- and fieldnames (Firebird 3) in camelcase. If we 
want to write a select statement we have to use quotes around a tablename. 
This is not userfriendly. Is there a way that we don't have to use the 
quotes in SQL-statements ?
We don't want to create our table- and fieldnames in lowercase or uppercase.

Nico Speleers
Analyst





Carfac bvba

Driving on experience


Rijksweg 53
9680 Maarkedal
Tel. +32 55 23 00 00
Fax +32 55 31 00 95
nico.spele...@carfac.com
www.carfac.be
[cid:image001.jpg@01D11613.C23F19C0][cid:image002.jpg@01D11613.C23F19C0]
 
[cid:image003.jpg@01D11613.C23F19C0] 

[Beschrijving: Beschrijving: Beschrijving: CarfacAS 
(klein)]


)


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




Posted by: Nico Speleers - Carfac BVBA 


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] Re: gbak with -se switch?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

 When connected to the database from another application, here:
 C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t  -v
 -user sysdba -pas masterkey
 "e:\temp\2014 FB Conference.fdb" "e:\temp\2014 FB Conference.bak"
>>>
>>> Hostless connection string, as I expected.
>>
>> As per my original e-mail, I'm not surprised this fails. Which is why I
>> switched to attempting to use the service manager. ;)
>
>IMHO, it would be easier to just add a host name to this command line.

Ah, that's what I tried at first, but failed with the -se option included.

I tried a connection string with host, but without -se now, and that works 
as well.

Seems there's multiple ways of doing things. ;)


With regards,

Martijn Tonies
Upscene Productions

Re: [firebird-support] Re: gbak with -se switch?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

>22.06.2016 12:14, 'Martijn Tonies (Upscene Productions)' 
>m.ton...@upscene.com
>[firebird-support] wrote:
>> When connected to the database from another application, here:
>> C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t -v
>> -user sysdba -pas masterkey
>> "e:\temp\2014 FB Conference.fdb" "e:\temp\2014 FB Conference.bak"
>
>   Don't you forget "martijn-ws-dev/3054:" in database connection string?..

This doesn't use the service manager. For the rest, see my original e-mail.


With regards,

Martijn Tonies
Upscene Productions







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Re: gbak with -se switch?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

>> As per original example:
>> Here’s the command:
>> C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t -se service_mgr –v
>> -user sysdba -pas masterkey
>>"MARTIJN-WS-DEV/3054:e:\temp\2014 FB Conference.fdb"
>>"e:\temp\2014 FB Conference.bak"
>>
>> In my book, this -looks- like a connection string with host.
>
>I understood you as that regular gbak does not work when the database is
>in use, so now you try the -se switch.

That is correct, but as you can see, using -se resulted in an error.

>> So I tried:
>>
>> C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t
>>-se "martijn-ws-dev/3054:service_mgr"
>
>Quotes are not needed here.

OK, nevertheless, I got the same error about 'multiple destinations'. Also,
where does the docs say to use the host before 'service_mgr' instead of
the (source) database string?

>>-v -user sysdba -pas masterkey
>>"e:\temp\2014 FB Conference.fdb" "e:\temp\2014 FB Conference.bak"
>
>Did you try using filenames without embedded spaces (and thus without
>quotes)?

No, cause the database name contains spaces ;) And it works fine using the
services API (via a Delphi component).

Could that be the cause of the multiple sources/destinations error? If so, 
I'll
take it this is a parsing error in gbak?


With regards,

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

Re: [firebird-support] Re: gbak with -se switch?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

>And no, I couldn't find this in the documentation.

FYI:

http://firebirdsql.org/manual/gbak-cmdline.html#gbak-cmdline-backup
"-SE[RVICE] 

This switch causes gbak to backup a remote database via the service manager. 
This causes the backup file to be created on the remote server, so the path 
format and filename must be valid on the remote server. The servicename is 
currently always the text service_mgr."

>> so the path format and filename must be valid on the remote server <<

But Dmitry S says you need to move the host to "service_mgr"?

An example would be nice. ;)


With regards,

Martijn Tonies
Upscene Productions

[firebird-support] gbak with -se switch?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

I’m trying to back up a database using gbak and the –se switch. But can’t seem 
to get it working.

Here’s the command:
C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t -se service_mgr –v
   -user sysdba -pas masterkey 
  "MARTIJN-WS-DEV/3054:e:\temp\2014 FB Conference.fdb" 
  "e:\temp\2014 FB Conference.bak"

gbak: ERROR:multiple sources or destinations specified
gbak: ERROR:Exiting before completion due to errors
gbak:Exiting before completion due to errors


Firebird 3 fails to back up when the database is in use, to I need to use the 
–se switch. But I can’t get it working, any clue?


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!

Re: [firebird-support] How to skip a trigger in the same trigger?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Gabor,

>>> First question: why do you have an UPDATE  in a BEFORE UPDATE
>> trigger on ?
>>
>>The table have many fields and a UNIQUE constraint with four fields, one
>>the ISDEFAULT from it (only one default accepted for groups of rows
>>depend on the other three fields (which included also in the UNIQUE)
>>value). If the user change the default row to not default and set other
>>to default in the application I need to set the old default row to not
>>default because if not do before set the new default the UPDATE violate
>>the UNIQUE.
>
>
>I agree with Dimitry, you might want to revise this design. If you have a
>table with a single value and a reference to this table, there can be only 
>1 default
>row, by design.
>
>Removing a row from this table and inserting a new one (or updating the old 
>row),
>would then always have a single row assigned to be the default and avoids
>the recursive trigger.

Alternatively, make this part of the trigger only fire when ISDEFAULT = T,
then you can safely set ISDEFAULT to 'F' from within the trigger.


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! 







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How to skip a trigger in the same trigger?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Gabor,

>> Hi,
>>
>> First question: why do you have an UPDATE  in a BEFORE UPDATE
>> trigger on ?
>
>The table have many fields and a UNIQUE constraint with four fields, one
>the ISDEFAULT from it (only one default accepted for groups of rows
>depend on the other three fields (which included also in the UNIQUE)
>value). If the user change the default row to not default and set other
>to default in the application I need to set the old default row to not
>default because if not do before set the new default the UPDATE violate
>the UNIQUE.


I agree with Dimitry, you might want to revise this design. If you have a
table with a single value and a reference to this table, there can be only 1 
default
row, by design.

Removing a row from this table and inserting a new one (or updating the old 
row),
would then always have a single row assigned to be the default and avoids
the recursive trigger.


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! 







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How to skip a trigger in the same trigger?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

First question: why do you have an UPDATE  in a BEFORE UPDATE
trigger on ?


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!


Hi All,

I have a BEFORE UPDATE trigger for MYTABLE1 with an IF ... UPDATE 
MYTABLE1 and after an IF ... INSERT INTO MYTABLE2 in it. If the UPDATE 
MYTABLE1 executed the trigger fire himself and the INSERT executed 
twice. How to skip it? RDB$SET_CONTEXT/RDB$GET_CONTEXT is a good 
solution? I never used them before.








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


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:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same Stored Procedure?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mike,

Database Workbench won't enable the Commit/Rollback buttons on a plain 
SELECT,
because this will require you to use them for every SELECT you perform.

In the case of a SELECT-able Stored Procedure, this means it won't be able 
to detect
your procedure is also modifying data.

In the SQL Editor, you can actively start a transaction by yourself and the 
commit/rollback
buttons will always be enabled.

Hope this helps, if not, feel free to write to me personally.


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!





I’m sorry I should have been a little bit more concise on what I’m doing.



I use Database Workbench v5 for all of my development needs.



Running the stored procedure setting the V_REPORT = 1 in Database Workbench, 
I do get a results set.  So far, so good.



However, if I set V_REPORT = 0 which should cause the UPDATE to be processed 
instead, I’m not able to commit as the “Commit” and “Rollback”  buttons are 
not enabled.



However, If I comment out the first portion (as shone below) leaving just 
the UPDATE clause it works fine and the  “Commit” and “Rollback”  buttons 
are not enabled.



/*

  IF (V_REPORT = 1) THEN

SUSPEND;

  ELSE */

UPDATE ACCT_CASE

   SET LEGAL_CASE_DATE = :ACCH_LEGAL_CASE_DATE

 WHERE ACCT_ID = :ACCT_ID

   AND CASE_ID = :CASE_ID;



Any ideas why?



Thanks,

Mike





From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, November 03, 2015 9:29 AM
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Why can't I have a SUSPEND or UPDATE in the 
same Stored Procedure?





hi,



you got an error or what?







regards,

Karol Bieniaszewski



 Oryginalna wiadomość 
Od: "'stwizard' stwiz...@att.net [firebird-support]" 

Data: 03.11.2015 14:59 (GMT+01:00)
Do: firebird-support@yahoogroups.com
Temat: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same 
Stored Procedure?



Greetings All,



Firebird v 2.5.4



Many times I would like to run a report before I do an update.  Why can’t I 
allow for both in one stored procedure?  Look at the end of this stored 
procedure where I use V_REPORT.



Thanks,

Mike



SET TERM ^^ ;

CREATE PROCEDURE X_CHK_LEGAL_CASE_DATE (

  V_REPORT SmallInt)

returns (

  ACCT_ID Integer,

  CASE_ID SmallInt,

  LEGAL_CASE_DATE Date,

  CASE_LEGAL_CASE_DATE Date,

  ACCH_LEGAL_CASE_DATE Date,

  ACCH_NOTE VarChar(200))

AS

DECLARE VARIABLE iAcctCaseCourtID Integer;

begin

  FOR SELECT ACCT_CASE_COURT_ID,

 CAST(CREATE_DATE AS DATE),

 ACCT_ID,

 CASE_ID

FROM ACCT_CASE_COURT

   WHERE STATUS_CODE = 'A'

ORDER BY ACCT_ID, CASE_ID

INTO :iAcctCaseCourtID, :LEGAL_CASE_DATE, :ACCT_ID, :CASE_ID DO

BEGIN

  SELECT LEGAL_CASE_DATE

FROM ACCT_CASE

   WHERE ACCT_ID = :ACCT_ID

 AND CASE_ID = :CASE_ID

INTO :CASE_LEGAL_CASE_DATE;



  IF (CASE_LEGAL_CASE_DATE IS NULL) THEN

BEGIN

  SELECT FIRST 1 CAST(CREATE_DATE AS DATE), NOTE

FROM ACCT_CASE_COURT_HIST

   WHERE ACCT_CASE_COURT_ID = :iAcctCaseCourtID

   ORDER BY ACCT_CASE_COURT_HIST_ID

INTO :ACCH_LEGAL_CASE_DATE, ACCH_NOTE;



  IF (V_REPORT = 1) THEN

SUSPEND;

  ELSE

UPDATE ACCT_CASE

   SET LEGAL_CASE_DATE = :ACCH_LEGAL_CASE_DATE

 WHERE ACCT_ID = :ACCT_ID

   AND CASE_ID = :CASE_ID;

END

END

end ^^

SET TERM ; ^^





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




Posted by: "stwizard" 


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] storing a sequence value in a trigger

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

Easiest is as follows:

define your sequence trigger to be:

IF (coalesce(new.idcolumn, 0) = 0)
then new.idcolumn = gen_id(mysequence, 1);


Then, in your trigger:

declare myassid bigint;
begin
  myassid = gen_id(mysequence, 1);
  insert into assignment(assid, ... other columns ... )
  values (:myassid, ... other columns ... )
/* other inserts */
  insert into ... 
end

or

insert into assignments(... other columns ... )
values ( ... )
returning_values assid into :myassid;


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!

From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, October 06, 2015 1:30 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] storing a sequence value in a trigger




I have three tables Guidance, Assignment and Review.  Assignment is a detail 
table to Guidance and Review is a detail table to Assignment.  When a new 
record is entered into the Guidance table I want to an after insert trigger on 
the Guidance table  to insert default values into Assignment and Review.  When 
the trigger inserts a new record in the assignment table how do I store the 
primary key for that record in the trigger so I can use it when the trigger 
goes on to insert a new record in the Review table.I've tried defining the 
trigger as below 

AS
DECLARE MyAssId BIGINT;
BEGIN
  MyAssId = insert into assignment(assid,GDLID, OCHID,ASSIGNED_DTE) Values  (< 
error occurs here)
(GEN_ID(AssKey_Gen,1),new.GDLID,1,CURRENT_DATE) RETURNING assid

END
^
COMMIT WORK ^
SET TERM ; ^


SQL error code = -104
Token unknown - line 6, column 13
insert


I've also tried defining the trigger as:

SET TERM ^ ;
CREATE OR ALTER TRIGGER AI_GDLID FOR GUIDANCE 
ACTIVE AFTER INSERT POSITION 20
   insert into assignment(assid,GDLID, OCHID,ASSIGNED_DTE) Values (<--- 
Error occurs here)
(Asskey,new.GDLID,1,CURRENT_DATE);
insert into Review(ASSID, REVIEW_DTE, GOTID, NOTE) Values
(asskey, CURRENT_Date, 1, 'New Guidance Added');
END
^

However I then get this error message:

Error at line 2: Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, column 12
select

What is the correct way to achieve what I want to do?

Re: [firebird-support] What is better: UPDATE or DELETE + INSERT?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

>> I need to rebuild some records in my database. I have to choices:
>>
>> 1. Firstly I delete set of records and then insert new ones.
>> 2. I update the existing ones.
>>
>> What solution is preferable? Will scenerio number 1. cause more garbage
>in
>> database and decrease its performance?
>
>They will generate a comparable amount of garbage, but I think updating
>the existing records is better than delete + insert, especially if there
>are also foreign key references to those records.

Agreed with Mark. From a logical point of view, if you want to modify data, 
update it.

Something else doesn't make sense.


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! 

Re: [firebird-support] Re: Hex string literal, blob not supported for conversion?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

>> This works:
>>
>> CREATE TABLE BMP_BLOBS
>> (
>>ID   INTEGER NOT NULL,
>>BMP BLOB SUB_TYPE 0 SEGMENT SIZE 80,
>>"COMMENT"  VARCHAR(20)  COLLATE ISO8859_1,
>> CONSTRAINT PK_BMP_BLOBS PRIMARY KEY (ID)
>> );
>>
>> insert into BMP_BLOBS
>> values (-1, X'101010', 'test')
>>
>> How is the declared function any different?
>
>UDFs were always quite limited.

Nevertheless, the parameter is BLOB. Why is one blob conversion supported,
but an other, that looks the same to the user, fails?

This is counter intuitive.


(and yes, I do read between the lines you don't feel compelled to change it)


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! 

Re: [firebird-support] Re: Hex string literal, blob not supported for conversion?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Lester,

>m.ton...@upscene.com [firebird-support] wrote:
>> Nevertheless, the parameter is BLOB. Why is one blob conversion 
>> supported,
>> but an other, that looks the same to the user, fails?
>
>Martijn ... I always thought that the 'blob' in a function had to be an
>existing blob element i.e. a field in an existing table, so trying to
>upload a static variable in the first example is wrong as it does not
>exist as a blob, while your insert is converting the static variable
>into a format that can be stored in the identified blob. Your blob
>function needs the field name of the blob you want to work with?

I know a bit about the implementation and blob descriptors etc.

Point is, when the hex literal is put into a table, Firebird transforms the 
literal
into a BLOB, but when it needs to pass a value to an external function, it 
can't
do the same.

That's confusing.

Why would an external function require a field name as the "blob value"? It
expects a blob value. From the user point of view, I see no reason why this
can't be a temporary blob, or a just-converted-blob, like the SQL INSERT
statement can.


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! 

Re: [firebird-support] Re: Hex string literal, blob not supported for conversion?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

>> I wonder, should this be possible:
>> select f_blobsize(X'10')
>> from rdb$database
>> DECLARE EXTERNAL FUNCTION F_BLOBSIZE
>>  BLOB
>>  RETURNS INTEGER FREE_IT
>>  ENTRY_POINT 'blobsize' MODULE_NAME 'FreeAdhocUDF';
>> I’m getting this error:
>> “ feature is not supported BLOB and array data types are not supported
>> for conversion operation “
>> But as far as I can see, X’10’ returns a binary string, a blob?
>
>X'10' means a binary string (char octets) but this is not a blob. AFAIU,
>the char->blob conversion is impossible in this context.

This works:

CREATE TABLE BMP_BLOBS
(
  ID   INTEGER NOT NULL,
  BMP BLOB SUB_TYPE 0 SEGMENT SIZE 80,
  "COMMENT"  VARCHAR(20)  COLLATE ISO8859_1,
CONSTRAINT PK_BMP_BLOBS PRIMARY KEY (ID)
);


insert into BMP_BLOBS
values (-1, X'101010', 'test')

How is the declared function any different?


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! 

[firebird-support] Hex string literal, blob not supported for conversion?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

I wonder, should this be possible:
select f_blobsize(X'10')
from rdb$database

DECLARE EXTERNAL FUNCTION F_BLOBSIZE
BLOB
RETURNS INTEGER FREE_IT
ENTRY_POINT 'blobsize' MODULE_NAME 'FreeAdhocUDF';

I’m getting this error:
“ feature is not supported BLOB and array data types are not supported for 
conversion operation “


But as far as I can see, X’10’ returns a binary string, a blob?

Firebird 2.5.

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!

Re: [firebird-support] Table Relationship Tools

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Vishal,

Is there any tool available which would show the relationship/linking 
between tables for Firebird database ?

Our Database Workbench tool does that.
http://www.upscene.com/documentation/dbw5/dia_pdm_reverse_engineering.htm


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!

[firebird-support] Plan question, what is a stream and what isn't?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

With the following plan:
PLAN SORT (JOIN (T T TL INDEX (TICKETLABELSTICKETTID)) PLAN (T T L NATURAL), L 
INDEX (TICKETLABELID))

Is the second PLAN expression is nested stream in the first SORT?

eg:
PLAN SORT (
JOIN (
T T TL INDEX (TICKETLABELSTICKETTID)
)
PLAN (
T T L NATURAL
), 
L INDEX (TICKETLABELID)
)

And how about the final INDEX specifier? 

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!

Re: [firebird-support] Scope of uniqueness constraint?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Tim,

Firebird constraints are evaluated immediately for the transaction.

So that means that during constraint validation, it doesn't see other 
transactions, and due to it's immediate evaluation, you can't temporarily 
ignore validation for your transaction either. (eg: increase all PK values 
by 1)


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!





If there's some code which says if there isn't an EXXON then create
one, and there's a uniqueness constraint such that there can't be two
Bxs, then clearly the above code can go wrong, in that the following
cannot succeed:

(1) Transaction 1 - check for EXXON, find it doesn't exist
(2) Transaction 1 - create EXXON
(3) Transaction 2 - check for EXXON, find it doesn't exist (because it
can't see the one created by transaction 1)
(4) Transaction 2 - create EXXON
(5) Transaction 1 - commit
(6) Transaction 2 - commit

This fails, as one would expect, due to the violation of the uniqueness
constraint. But my question is: does it fail at point (4), because the
uniqueness constraint is somehow active/visible/whatever across
transactions, or does it fail at point (6), because the uniqueness
constraint only takes account of committed stuff?

(Yes I do know that's what generators are for.)

Re: [firebird-support] Distributing/deploying Stored Procedures

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Andrew,

Welcome to the Firebird community.

I can pump SQL statements through the application to make certain changes the 
first time a new release is run (alter table etc) but am concerned at how I am 
supposed to update SPs (potentially a large number) without access to the FB 
server.  I considered extracting the metadata(FlameRobin), deleting 
superfluous statements and pumping what is left through the app but that seems 
like A LOT of work.


When modifying metadata, make sure everyone is logged out of your application. 
Although it’s possible to “do metadata stuff” with people connected, it’s not 
the preferred way.

When you want to change a large number of procedures and triggers, it’s 
certainly possible to simply re-create them, certainly the easiest.

However, when dropping, there will be dependencies from one procedure to 
another, so they have to be dropped in a specific order.

There’s an alternative, however:

1) drop all triggers (that call procedures)
2) alter all procedures to an empty body, this keeps the parameter signature in 
place
3) drop all procedure
4) create all procedures with an empty body to establish parameter signatures
5) alter all procedures with complete body
6) create all triggers

If you use a tool like our Database Workbench tool, you can alternatively 
compare your development to your production database and see what meta data has 
changed and extract a script, or automatically record a change script while 
doing all changes.

Or extract a script for all procedures that does these 
create-with-empty-body-and-then-alter-cycle.

Hope this helps.

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!






I’m a bit of a FB noob and am after some advice about Stored Procedures and the 
best way to distribute them to client sites.

 

We are in the process of redeveloping our software using FB as the back end (it 
was previously ISAM).  We have a reasonably large client base and our 
application is installed on many sites which run independently.

 

We intend to make use of Stored Procedures, Triggers etc and between releases 
(during development) these objects will be created, dropped, modified etc.  I 
have been tasked with finding a solution to distribute the update/upgrade SP.

 

At the client end they will access the data via our application (via ODBC).  
They will have no direct access to the FB server and no ISQL command.

 

I can pump SQL statements through the application to make certain changes the 
first time a new release is run (alter table etc) but am concerned at how I am 
supposed to update SPs (potentially a large number) without access to the FB 
server.  I considered extracting the metadata(FlameRobin), deleting superfluous 
statements and pumping what is left through the app but that seems like A LOT 
of work.

 

Also, do I bother with ‘alter procedure …..’ or do I simply drop them and 
(re)create them (after all, at release time, the SP is the SP)  

 

I am trying to automate the task as much as possible, I don’t want our support 
staff to have to intervene in an upgrade.

 

Surely I’m not the only one that has needed to do such a thing.  Does such a 
mechanism exist? Perhaps some suitable suggestions?

 

Re: [firebird-support] Trouble with a Firebird query

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,
Text values go inside single quotes:
WHERE mycol = ‘value’
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!
 

I am having trouble writing a query in iReport using  Firebird SQL

 

I can get the query to work in MS Access, but I’m not familiar with Firebird 
SQL, seems to be different .

 

The access version of my query that works is

 

 

SELECT CUSTOMFIELDVIEW.INFO AS OtherName, SO.NUM

FROM CUSTOMFIELDVIEW INNER JOIN (SO INNER JOIN CUSTOMER ON SO.CUSTOMERID = 
CUSTOMER.ID) ON CUSTOMFIELDVIEW.RECORDID = CUSTOMER.ID

WHERE (((SO.NUM)=763905) AND ((CUSTOMFIELDVIEW.CFID)=78));

 


WHERE(((CUSTOMFIELDVIEW.CFID)=78))  ),

 

But this version is missing calling the SO.NUM field and the WHERE 
(((SO.NUM)=763905)

 

If I add them to the query, iReport throws an error saying its invalid syntax

 

 

 

Any help appreciated

 

Thanks 

Re: [firebird-support] Charset in ISQL: How to show special characters

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

Looks like ISQL is right and EMS SQL Manager is wrong.

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Thursday, February 19, 2015 12:40 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Charset in ISQL: How to show special characters




Hello,

 

due to an import problem the database contains some records with wrong special 
characters.

 

The database runs on ISO8859_1. When I select the records in EMS SQL Manager 
(also configured for ISO8859_1) it shows following data:

K”cher

Sttzen

Tr„ger

 

In ISQL (isql localhost:myDB -u SYSDBA -p 'blabla' -charset ISO8859_1) it shows:

Köcher

Stützen

Träger

 

What needs to be done to get the same picture in isql as in the other tool? The 
motivation is to fix such data with an isql script. In this case I don´t get an 
matching records if query for

 

SELECT * FROM LPP_ISTSTUNDEN WHERE iststunden_code = 'K”cher';

 

Thanks

 

Niko

Re: AW: [firebird-support] Charset in ISQL: How to show special characters

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

my impression was the other way arround ;-)

Then I'm either misreading your e-mail, or you've switched the examples 
around?

When I change the charset in isql to any charset, that is definitly not 
used in the database I always get the same output from isql. I was 
expecting that the presentation of the same data (= varchar with special 
characters) would change in isql if I switch charset.

What application is show  ö  and what isn't? And is that valid?


With regards,

Martijn Tonies
Upscene Productions



Von: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]
Gesendet: Donnerstag, 19. Februar 2015 12:49
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Charset in ISQL: How to show special 
characters





Hi,



Looks like ISQL is right and EMS SQL Manager is wrong.



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!





From: mailto:firebird-support@yahoogroups.com

Sent: Thursday, February 19, 2015 12:40 PM

To: firebird-support@yahoogroups.com 
mailto:firebird-support@yahoogroups.com

Subject: [firebird-support] Charset in ISQL: How to show special characters








Hello,



due to an import problem the database contains some records with wrong 
special characters.



The database runs on ISO8859_1. When I select the records in EMS SQL Manager 
(also configured for ISO8859_1) it shows following data:

K”cher

Sttzen

Tr„ger



In ISQL (isql localhost:myDB -u SYSDBA -p 'blabla' -charset ISO8859_1) it 
shows:

Köcher

Stützen

Träger



What needs to be done to get the same picture in isql as in the other tool? 
The motivation is to fix such data with an isql script. In this case I don´t 
get an matching records if query for



SELECT * FROM LPP_ISTSTUNDEN WHERE iststunden_code = 'K”cher';



Thanks



Niko





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




Posted by: Parzival parzival1...@gmx.at


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] How do load a BLOB file using an Update query

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi Craig,


I have been using LoadFromFile in my Delphi apps to load a pdf file into 
my Firebird databases.  However, I need to replicate this using an update 
query.
The code below was my first attempt.  Using IBOjects within Delphi
  qryUpdateManifestBLOB.SQL.Clear;
  qryUpdateManifestBLOB.SQL.Add('UPDATE');
  qryUpdateManifestBLOB.SQL.Add('w_shipments');
  qryUpdateManifestBLOB.SQL.Add('SET');
  qryUpdateManifestBLOB.SQL.Add('w_shipments.US_manifest_doc =');
  qryUpdateManifestBLOB.SQL.Add('LoadFromFile');

Well, this obviously won't work, because whatever is in the .SQL property, 
is executed by the Firebird database engine. And that engine cannot execute 
Delphi code.

Use a parameter:
SQL.Add('SET myblobcol = :newblob');

and of course the primary key stuff.

Call:
qryUpdateManifestBLOB.Prepare;
qryUpdateManifestBLOB.ParamByName('newblob').LoadFromFile
qryUpdateManifestBLOB.ExecSQL;

which Firebird did not like because LoadToFile is an unknown function.

See above.


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! 

Re: [firebird-support] Re: Unicode text in Exception?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

 btw, in Firebird 3, the RDB$EXCEPTIONS.RDB$MESSAGE is still NONE.

 Would it make sense to encode the message according to the given 
 connection
 character set?

Do you mean changing RDB$EXCEPTIONS.RDB$MESSAGE to UTF8? It was
discussed but ended nowhere.

No, I meant a runtime encoding when sending the text to the client.

Or perhaps storing it as UNICODE_FSS, same as $SOURCE or $DESCRIPTIONs are 
stored?

 Now, I tried the following in the procedure to work around the above:

 exception test _utf8 'unicode string here';

 Any idea why this won't work either? Or at least, not in my test?

I tried without the _utf8 prefix and it worked:

I'm trying this in Database Workbench, this works:
select 'ТЕСТ НА БЪЛГАРСКИ'
from rdb$database

1) Connect as UTF8 charset

Yep.

2) Create SP with EXCEPTION ERROR 'some Cyrillic string';

Done. In the source, I see the following:

begin
  exception test _utf8 
X'31D0A2D095D0A1D0A220D09DD09020D091D0AAD09BD093D090D0A0D0A1D09AD098';
end

3) Validate that RDB$PROCEDURES.RDB$PROCEDURE_BLR (not 
RDB$PROCEDURE_SOURCE!) really contains a Unicode string

In the BLR, I see:

0502040101000700029B110002028006 //
0454455354150F0400210031D0A2D095 // TEST ... ! then the same as above in hex
D0A1D0A220D09DD09020D091D0AAD0
9BD093D090D0A0D0A1D09AD098
FF0E01020115070019014C

4) Connect as WIN1251, execute SP - error text is Cyrillic
5) Connect as WIN1250, execute SP - cannot transliterate character
between character sets

When executing the procedure, UTF8/WIN1251/WIN1250 connected, I get:
exception 1 TEST 1ТЕСТ НА БЪЛГАРСКИ At procedure 'TEST' line: 
4, col: 3

This -could- be my error, but how do I verify?

When I try in isql, I get the same.

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! 

Re: [firebird-support] Re: Unicode text in Exception?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

I'm still puzzled.

29.01.2015 19:15, Martijn Tonies wrote:

 2) Create SP with EXCEPTION ERROR 'some Cyrillic string';

 Done. In the source, I see the following:

 begin
exception test _utf8
 X'31D0A2D095D0A1D0A220D09DD09020D091D0AAD09BD093D090D0A0D0A1D09AD098';
 end

You don't need the _utf8 prefix with HEX string if you create SP in UTF8

The hex string is generated automagically when I connect with utf8, enter a 
string value
with Cyrillic character and save the procedure. Upon reloading it from the 
database, it
has this hex string. Without the prefix, the text is stored normal.

connection. When I tried with _utf8, I got some weird results too.
Without _utf8, everything worked fine. But I have a Cyrillic keyboard to
write the error text properly ;-)

I removed the _utf8 prefix so I only have the next string, connected with 
utf8/win1251/win1250
but I always get the same garbage returned.

I'm doing this using DevArt IBDAC, but I get the same using isql.


No clue why I can't get the correct text.


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! 

Re: [firebird-support] Re: Unicode text in Exception?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

btw, in Firebird 3, the RDB$EXCEPTIONS.RDB$MESSAGE is still NONE.

Would it make sense to encode the message according to the given connection 
character set?

 Now, I tried the following in the procedure to work around the above:

 exception test _utf8 'unicode string here';

Any idea why this won't work either? Or at least, not in my test?



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! 

Re: [firebird-support] Re: Unicode text in Exception?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

btw, in Firebird 3, the RDB$EXCEPTIONS.RDB$MESSAGE is still NONE.



 Oh bugger.

 Now, I tried the following in the procedure to work around the above:

 exception test _utf8 'unicode string here';

 When I look at RDB$PROCEDURES.RDB$PROCEDURE_SOURCE I can see
 the text is inserted as a hex string.

 But this doesn't seem to work either?

 Does this work at all with Exceptions?

AFAIU, your trick (exception parameter) should work.

Hmm, I'm puzzled then, cause it doesn't seem to work at first sight.


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!



Posted by: Martijn Tonies (Upscene Productions) m.ton...@upscene.com


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] Re: Unicode text in Exception?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

 Oh bugger.

 Now, I tried the following in the procedure to work around the above:

 exception test _utf8 'unicode string here';

 When I look at RDB$PROCEDURES.RDB$PROCEDURE_SOURCE I can see
 the text is inserted as a hex string.

 But this doesn't seem to work either?

 Does this work at all with Exceptions?

AFAIU, your trick (exception parameter) should work.

Hmm, I'm puzzled then, cause it doesn't seem to work at first sight.


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!

[firebird-support] Unicode text in Exception?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

I have an EXCEPTION object with a Unicode text in it, a stored procedure raises 
this exception.

The client application can view the Unicode text just fine when it retrieves 
the Exception meta data, but when the exception is raised, Firebird doesn’t 
seem to translate the text.

Any idea how to do this?

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!

Re: [firebird-support] Re: Unicode text in Exception?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

 I have an EXCEPTION object with a Unicode text in it, a stored procedure
 raises this exception.
 The client application can view the Unicode text just fine when it
 retrieves the Exception meta data, but when the exception is raised,
 Firebird doesn’t seem to translate the text.
 Any idea how to do this?

Historically, RDB$EXCEPTION_MESSAGE has NONE charset, so it's never
transliterated to the client charset. The engine simply has no idea that
you stored UTF8 there. Sigh.

Oh bugger.

Now, I tried the following in the procedure to work around the above:

exception test _utf8 'unicode string here';

When I look at RDB$PROCEDURES.RDB$PROCEDURE_SOURCE I can see
the text is inserted as a hex string.

But this doesn't seem to work either?

Does this work at all with Exceptions?


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!

Re: [firebird-support] Firebird 3.0 Development Admin Tools

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Christoph,
Database Workbench supports some of the new features in Firebird 3 – for 
example, the packages.
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!



I've to develop a new application which will be launched not before end of 
year. So I thought it's best to go for the 3.0 release (still in Beta, I know). 
But till the end of the year I assume 3.0 will be launched.




Nevertheless: I need a tool to maintain / develop the database. In the past I 
used sql manager for interbase firebird. But it's not supporting 3.0 yet. Any 
advice, which tools are able to handle 3.0 databases?




BR
Christoph

Re: [firebird-support] Re: How do you write dynamic sql in Firebird

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dmitry,

12.12.2014 23:08, 'Martijn Tonies wrote:

 But using ROWS instead of FIRST makes using a parameter possible

FIRST (:CNT) does the trick as well.

Oh, good one, let's see if I can remember. ;)


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!

Re: [firebird-support] How do you write dynamic sql in Firebird

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi,

First thing: SUSPEND is only used if the “caller” will ask for multiple rows, 
returning rows to the client or a caller-stored procedure. In your IN clause, 
this doesn’t make any sense, as an IN clause isn’t “looping over result”.

Next, an IN clause requires a list of values or a SELECT statement. A FOR 
EXECUTE STATEMENT is not a select-statement.

If you really need a dynamic statement, you have to use the complete UPDATE in 
a EXECUTE STATEMENT command:

EXECUTE STATEMENT 
‘update tblCampaign ... etc etc... ‘ ||
‘a.campaignid in (select first ‘ || cnt || ‘ c.campaignid etc etc ‘;


But using ROWS instead of FIRST makes using a parameter possible, so it seems:
IN (SELECT ... FROM ... ROWS :CNT)


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!






From: mailto:firebird-support@yahoogroups.com 
Sent: Friday, December 12, 2014 8:40 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] How do you write dynamic sql in Firebird




I'm trying to write a procedure where the number you select is a parameter.  
This is what I've come up with, but it doesn't work.




SET TERM ^ ;

CREATE PROCEDURE uspInitCampaignMaxCnt 
(
CampaignName Varchar(75)
, BatchNum INT
, Cnt INT
)
RETURNS 
(
CntBatch INT
)
AS 
DECLARE VARIABLE VSQL VARCHAR(1024);
BEGIN

SQL = 'SELECT FIRST ' || Cnt || ' c.CAMPAIGNID
FROM tblCampaign c
WHERE c.CAMPAIGNNA  
AND c.BADEMAIL = ''N'' 
AND c.ABORTCAMPAIGN = ''N''
AND c.BATCHNUM = -1';

UPDATE tblCampaign a SET 
a.BATCHNUM = :BatchNum 
WHERE a.CAMPAIGNID IN (FOR EXECUTE STATEMENT VSQL DO SUSPEND; );

SELECT COUNT(a.CampaignId) FROM tblCampaign a
WHERE a.CAMPAIGNNAME = :Campaig anything I've tried there.  Here's the 
error:



Engine Code: 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 27, column 28
FOR

Re: [firebird-support] RDB$GET_CONTEXT()

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Lane,

I've got a V2.5 DB, and using DB Workbench.

Good. ;)

Based on a hint from the Firebird Book, I tried the following query, hoping 
to get a Session_ID:
'select rdb$get_context('system', 'session_id') from rdb$database;'

I got back a Dynamic SQL error -804 Function unknown RDB$GET_CONTEXT
message. What am I doing wrong?

Well, I'm getting:
Invalid namespace name system passed to RDB$GET_CONTEXT

But at least the function works.


In Database Workbench, if you expand the UDFs node after establishing a 
database connection, is RDB$GET_CONTEXT listed?


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! 

Re: [firebird-support] How do you write dynamic sql in Firebird

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Dixon,

Is this using the ROWS on your UPDATE or on your SELECT?

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Friday, December 12, 2014 9:31 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] How do you write dynamic sql in Firebird




Martijn,



Thanks, ROWS worked perfect



I changed it to this and am getting what I want.



UPDATE tblCampaign a SET 
a.BATCHNUM = :BatchNum 
WHERE a.CAMPAIGNID IN (SELECT c.CAMPAIGNID
FROM tblCampaign c
WHERE c.CAMPAIGNNAME = :CampaignName 
AND c.CONTACTPERMISSION = 'Y'
   AND c.ABORTCAMPAIGN = 'N'
AND c.BATCHNUM = -1  )ROWS :Cnt;



Dixon

Re: [firebird-support] RDB$GET_CONTEXT()

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Lane,

In Database Workbench 5, I’ve an empty list as well (Firebird 3), even though 
the
function can be used, system table RDB$FUNCTIONS no longer includes these
system functions.

On your database, I’ll take it rdb$functions is empty as well.

I’m unsure why this should happen, is this the same when you create a new 
database?

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!





From: mailto:firebird-support@yahoogroups.com 
Sent: Friday, December 12, 2014 10:10 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] RDB$GET_CONTEXT()





On 12/12/2014 12:23 PM, 'Martijn Tonies (Upscene Productions)' 
m.ton...@upscene.com [firebird-support] wrote:


  Hello Lane,

  I've got a V2.5 DB, and using DB Workbench.

  Good. ;)

  Based on a hint from the Firebird Book, I tried the following query, hoping 
  to get a Session_ID:
  'select rdb$get_context('system', 'session_id') from rdb$database;'
  
  I got back a Dynamic SQL error -804 Function unknown RDB$GET_CONTEXT
  message. What am I doing wrong?

  Well, I'm getting:
  Invalid namespace name system passed to RDB$GET_CONTEXT

  But at least the function works.

  In Database Workbench, if you expand the UDFs node after establishing a 
  database connection, is RDB$GET_CONTEXT listed?

  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! 


***
Actually, there's nothing in the UDFs section of my DBWorkbench... on any of my 
DBs. Is there something missing from my installation?

Lane C.

Re: [firebird-support] Migration from Firebird 32 1.5.6 to Firebird 64 2.5.3

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Create a backup with Firebird 1.5, restore it with 2.5.

Make sure to keep a copy.

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, October 29, 2014 3:05 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Migration from Firebird 32 1.5.6 to Firebird 64 
2.5.3



Hi. 
I want to replace my Firebird 32 1.5.6 with the last  Firebird 64 2.5.3, on 64b

All my databases was created with 32b, 1.5.6 Firebird.

After installed Firebird 64, if a try to connect to database.FDB a received an 
error

database.fdb is not a valid database.

Solutions?
Thanks.

Re: [firebird-support] How do I count the number of duplicate rows in a table?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

select count(case_number) as counted, case_number
from ACCT_CASE_COURT
group by case_number

?

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, October 28, 2014 1:13 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] How do I count the number of duplicate rows in a 
table?

 


Greetings All,

Firebird 1.5.3

Should be elementary but, I'm drawing a blank on how to accomplish this.

I have a table (ACCT_CASE_COURT) that contains these fields (among others):

ACCT_CASE_COURT_IDINTEGERNOT NULLPK
ACCT_IDINTEGERNOT NULL
CASE_IDSMALLINTNOT NULL
CASE_NUMBERVARCHAR(20)NOT NULL
...

How do I form a SQL Select statement that would contain CASE_NUMBER in the 
first column (sorted) and number of times that the case number is found in the 
table in the second column (CNT)?

Any help truely appreciated.

Thanks,
Mike










This email is free from viruses and malware because avast! Antivirus 
protection is active. 
 

Re: [firebird-support] How do I count the number of duplicate rows in a table?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mike,

select
  count(CASE_NUMBER), CASE_NUMBER
from
  ACCT_CASE_COURT
group by CASE_NUMBER
having count(CASE_NUMBER)  1

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!





From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, October 28, 2014 1:27 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] How do I count the number of duplicate rows in 
a table?

 


Hi Martijn,

I did finally come up with this

SELECT DISTINCT(ACC.CASE_NUMBER),
   (SELECT COUNT(ACC2.ACCT_CASE_COURT_ID) FROM ACCT_CASE_COURT ACC2 WHERE 
ACC2.CASE_NUMBER = ACC.CASE_NUMBER) AS CNT
  FROM ACCT_CASE_COURT ACC
GROUP BY 1

Nut, I like yours better.

Now I need to exclude any that have a count less than 2 and I know I have to 
use HAVING but have not got it figured out yet.

Mike


  - Original Message - 
  From: mailto:m.ton...@upscene.com [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Tuesday, October 28, 2014 7:18 AM
  Subject: Re: [firebird-support] How do I count the number of duplicate rows 
in a table?



  select count(case_number) as counted, case_number
  from ACCT_CASE_COURT
  group by case_number

  ?

  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!


  From: mailto:firebird-support@yahoogroups.com 
  Sent: Tuesday, October 28, 2014 1:13 PM
  To: firebird-support@yahoogroups.com 
  Subject: [firebird-support] How do I count the number of duplicate rows in a 
table?

   
  Greetings All,

  Firebird 1.5.3

  Should be elementary but, I'm drawing a blank on how to accomplish this.

  I have a table (ACCT_CASE_COURT) that contains these fields (among others):

  ACCT_CASE_COURT_IDINTEGERNOT NULLPK
  ACCT_IDINTEGERNOT NULL
  CASE_IDSMALLINTNOT NULL
  CASE_NUMBERVARCHAR(20)NOT NULL
  ...

  How do I form a SQL Select statement that would contain CASE_NUMBER in the 
first column (sorted) and number of times that the case number is found in the 
table in the second column (CNT)?

  Any help truely appreciated.

  Thanks,
  Mike









--
  This email is free from viruses and malware because avast! Antivirus 
protection is active. 
   





--
  This email is free from viruses and malware because avast! Antivirus 
protection is active. 
   




  

Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Good morning SET. ;)

I thought of the concatenation trick, but found it ugly as a solution, your 
second query
is cleaner, I think.


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!



-Original Message- 
From: Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no 
[firebird-support]
Sent: Tuesday, October 14, 2014 9:23 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?

ACCT_CASE: Case Management table
ACCT_IDINTEGERNOT NULLPK
CASE_IDSMALLINTNOT NULLPK
CLT_IDINTEGERNOT NULLFK to CLIENT table   Need this for the 
JOIN

DEBTOR_CASE_DEBT:  Allows for multiple PERSON's to be associated with a 
DEBT
ACCT_IDINTEGERNOT NULLPK
CASE_IDSMALLINTNOT NULLPK
DEBT_NOSMALLINTNOT NULLPK
PERSON_IDINTEGERNOT NULLPK
STATUS_DATETIMESTAMPNOT NULL
STATUS_CODECHAR(1)NOT NULL

What am I attempting to do?  I need to know how many records are in the 
DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and 
'09/30/14'
and the STATUS_CODE = 'B (Bankruptcy Filed) and is for a specific CLT_ID 
(thus the join to ACCT_CASE to use CLT_ID).  I do not want to include the
PERSON_ID when fetching a COUNT() of the record, I only need to know how 
many debts are in this status for the client.  So only concerned with 
ACCT_ID,
CASE_ID and DEBT_NO.

So this SQL will return the correct number of records, now I just have to 
figure out how to return a count in one record.

  SELECT DISTINCT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO
 FROM DEBTOR_CASE_DEBT DCD
 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
  AND AC.CASE_ID = DCD.CASE_ID
WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
  AND DCD.STATUS_CODE = 'B'
  AND AC.CLT_ID = :V_CLT_ID

Did I provide enough information this time?  If not feel free to ask...

This is close to a perfect problem description, Mike, well done! The only 
(minor) thing lacking is a reason for you not wanting CLT_ID included...

I can think of two possible solutions:

a)
  SELECT COUNT(DISTINCT DCD.ACCT_ID||'-'||DCD.CASE_ID||'-'||DCD.DEBT_NO)
  FROM DEBTOR_CASE_DEBT DCD
  JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
   AND AC.CASE_ID = DCD.CASE_ID
  WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
AND DCD.STATUS_CODE = 'B'
AND AC.CLT_ID = :V_CLT_ID

b)
  SELECT COUNT(*)
  FROM DEBTOR_CASE_DEBT DCD
  WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
AND DCD.STATUS_CODE = 'B'
AND EXISTS(SELECT * FROM ACCT_CASE AC
   WHERE AC.ACCT_ID = DCD.ACCT_ID
 AND AC.CASE_ID = DCD.CASE_ID
 AND AC.CLT_ID = :V_CLT_ID)

Myself, I generally prefer to have single field primary keys, one benefit of 
this is that you can use solution a) without having to do tricks with 
concatenation.

HTH,
Set



Posted by: =?utf-8?B?U3ZlaW4gRXJsaW5nIFR5c3bDpnI=?= 
svein.erling.tysv...@kreftregisteret.no


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mike,

In the second query, you’re counting records and then do the DISTINCT, so the
result is 32, and if you “distinct” that result, there’s only 1 record, with a 
value
of 32.

What you seem to want, is to COUNT(DISTINCT(...))

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Monday, October 13, 2014 8:50 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is 
involved?

 


Greetings All,

Firebird 1.5.3 (Yes I know it is old)

Using the following syntax with 09/01/14 and 09/04/14 for the parameters 
fetches 20 distinct records

  SELECT DISTINCT DCD.ACCT_ID, AC.CLT_ID
 FROM DEBTOR_CASE_DEBT DCD
 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
  AND AC.CASE_ID = DCD.CASE_ID
WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
  AND DCD.STATUS_CODE = 'B'

Using this syntax with 09/01/14 and 09/04/14 for the parameters fetches a count 
of 32

  SELECT DISTINCT COUNT(DCD.PERSON_ID)
 FROM DEBTOR_CASE_DEBT DCD
 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
  AND AC.CASE_ID = DCD.CASE_ID
WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
  AND DCD.STATUS_CODE = 'B'

How do I accurately return the correct count using the COUNT() function?  In 
this case it should beturn 20 not 32

Any ideas appreciated.

Thanks,
Mike

Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mike,

Just happening to be testing this in Database Workbench.  Have used this 
product for years and just love it.

Thank you, that’s good to hear.

Thanks for you reply.
 
So my second SQL should have been as follows?  It results in an error Dynamic 
SQL Error SQL error code = -104 Token unknown - line 1, char 34 , 

COUNT only works on single column or *, so using COUNT on two columns won’t 
work.

I’m not sure what you’re trying to DISTINCT here, as the previous query counted 
PERSON_ID values in the result set.

  SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID)
 FROM DEBTOR_CASE_DEBT DCD
 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
  AND AC.CASE_ID = DCD.CASE_ID
WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
  AND DCD.STATUS_CODE = 'B'

So not sure if this is how I should have done it, but it appears to work as it 
returns 20 
 
  SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID)
 FROM DEBTOR_CASE_DEBT DCD
 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
  AND AC.CASE_ID = DCD.CASE_ID
WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
  AND DCD.STATUS_CODE = 'B'
 
Is that correct? 

Don’t think this will work for all combinations of ACCT_ID and CLT_ID, imagine:

101 || 1

is the same as 

10 || 11

Question is: what exactly are you trying to get from your query?


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!



  - Original Message - 
  From: mailto:m.ton...@upscene.com [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Monday, October 13, 2014 1:56 PM
  Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?



  Hello Mike,

  In the second query, you’re counting records and then do the DISTINCT, so the
  result is 32, and if you “distinct” that result, there’s only 1 record, with 
a value
  of 32.

  What you seem to want, is to COUNT(DISTINCT(...))

  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!


  From: mailto:firebird-support@yahoogroups.com 
  Sent: Monday, October 13, 2014 8:50 PM
  To: firebird-support@yahoogroups.com 
  Subject: [firebird-support] How do I return an accurate COUNT(*) when a JOIN 
is involved?

   
  Greetings All,

  Firebird 1.5.3 (Yes I know it is old)

  Using the following syntax with 09/01/14 and 09/04/14 for the parameters 
fetches 20 distinct records

SELECT DISTINCT DCD.ACCT_ID, AC.CLT_ID
   FROM DEBTOR_CASE_DEBT DCD
   JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
AND AC.CASE_ID = DCD.CASE_ID
  WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
AND DCD.STATUS_CODE = 'B'

  Using this syntax with 09/01/14 and 09/04/14 for the parameters fetches a 
count of 32

SELECT DISTINCT COUNT(DCD.PERSON_ID)
   FROM DEBTOR_CASE_DEBT DCD
   JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
AND AC.CASE_ID = DCD.CASE_ID
  WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
AND DCD.STATUS_CODE = 'B'

  How do I accurately return the correct count using the COUNT() function?  In 
this case it should beturn 20 not 32

  Any ideas appreciated.

Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mike,

So you want to count records in DEBT for a specific CLT_ID.

Now, in the DEBT table, there are records for multiple PERSONs, but what about 
ACCT_ID,
CASE_ID and DEBT_NO, which of these or what combination are unique with regard 
to each
ACCT_CASE?

If none, you would need a derived table, but these are available in Firebird 2 
onward.

The alternative is to create a VIEW for the DISTINCT query and COUNT on that.

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Monday, October 13, 2014 9:57 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?

 


Hi Martijn,

I knew I was going to get in trouble by not providing enought information as I 
thought by proving less it would be just a little bit clearer to understand, my 
bad...

Let's start over with an simplified explanation of the tables:

ACCT_CASE: Case Management table
ACCT_IDINTEGERNOT NULLPK
CASE_IDSMALLINTNOT NULLPK
CLT_IDINTEGERNOT NULLFK to CLIENT table   Need this for the JOIN
...

DEBTOR_CASE_DEBT:  Allows for multiple PERSON's to be associated with a DEBT
ACCT_IDINTEGERNOT NULLPK
CASE_IDSMALLINTNOT NULLPK
DEBT_NOSMALLINTNOT NULLPK
PERSON_IDINTEGERNOT NULLPK
STATUS_DATETIMESTAMPNOT NULL
STATUS_CODECHAR(1)NOT NULL

What am I attempting to do?  I need to know how many records are in the 
DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and 
'09/30/14' and the STATUS_CODE = 'B (Bankruptcy Filed) and is for a specific 
CLT_ID (thus the join to ACCT_CASE to use CLT_ID).  I do not want to include 
the PERSON_ID when fetching a COUNT() of the record, I only need to know how 
many debts are in this status for the client.  So only concerned with ACCT_ID, 
CASE_ID and DEBT_NO.

So this SQL will return the correct number of records, now I just have to 
figure out how to return a count in one record.

  SELECT DISTINCT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO
 FROM DEBTOR_CASE_DEBT DCD
 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
  AND AC.CASE_ID = DCD.CASE_ID
WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
  AND DCD.STATUS_CODE = 'B'
  AND AC.CLT_ID = :V_CLT_ID

Did I provide enough information this time?  If not feel free to ask...

Thanks so much,
Mike


  - Original Message - 
  From: mailto:m.ton...@upscene.com [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Monday, October 13, 2014 2:20 PM
  Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?



  Hello Mike,

  Just happening to be testing this in Database Workbench.  Have used this 
product for years and just love it.

  Thank you, that’s good to hear.

  Thanks for you reply.
   
  So my second SQL should have been as follows?  It results in an error 
Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , 

  COUNT only works on single column or *, so using COUNT on two columns won’t 
work.

  I’m not sure what you’re trying to DISTINCT here, as the previous query 
counted PERSON_ID values in the result set.

SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID)
   FROM DEBTOR_CASE_DEBT DCD
   JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
AND AC.CASE_ID = DCD.CASE_ID
  WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
AND DCD.STATUS_CODE = 'B'
  
  So not sure if this is how I should have done it, but it appears to work as 
it returns 20 
   
SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID)
   FROM DEBTOR_CASE_DEBT DCD
   JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
AND AC.CASE_ID = DCD.CASE_ID
  WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
AND DCD.STATUS_CODE = 'B'
   
  Is that correct? 

  Don’t think this will work for all combinations of ACCT_ID and CLT_ID, 
imagine:

  101 || 1

  is the same as 

  10 || 11

  Question is: what exactly are you trying to get from your query?


  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!



- Original Message - 
From: mailto:m.ton...@upscene.com [firebird-support] 
To: firebird-support@yahoogroups.com 
Sent: Monday, October 13, 2014 1:56 PM
Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?

  

Hello Mike,

In 

Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Mike,

No doubt our favorite SQL wizard from Norway, SET, will join in, perhaps he has 
an idea. 

As for my answer with the VIEW, now that I think of it, you would need the date
and status columns as well, so the DISTINCT is different. Darn.

You could use a SELECT-able Stored Procedure, return DCD.ACCT_ID, DCD.CASE_ID, 
DCD.DEBT_NO
and pass the STATUS_CODE, CLT_ID and DATEs as input parameters and COUNT the 
results from the
query, which include the DISTINCT inside the procedure.

Given that the routine would only return a few rows, it won’t be slow, I guess.
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!

From: mailto:firebird-support@yahoogroups.com 
Sent: Monday, October 13, 2014 10:19 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?

 


Hi Martijn,

I want to count the number of records in the DEBTOR_CASE_DEBT table (not DEBT) 
where the ACCT_ID, CASE_ID and DEBT_NO are distinct and specific to CLT_ID.

Is this possible in v1.5.3?

If not and I need to create a view and COUNT() that, can you provide an example 
or tell me where to find info on doing that?

Mike




  - Original Message - 
  From: mailto:m.ton...@upscene.com [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Monday, October 13, 2014 3:06 PM
  Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?



  Hello Mike,

  So you want to count records in DEBT for a specific CLT_ID.

  Now, in the DEBT table, there are records for multiple PERSONs, but what 
about ACCT_ID,
  CASE_ID and DEBT_NO, which of these or what combination are unique with 
regard to each
  ACCT_CASE?

  If none, you would need a derived table, but these are available in Firebird 
2 onward.

  The alternative is to create a VIEW for the DISTINCT query and COUNT on that.

  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!


  From: mailto:firebird-support@yahoogroups.com 
  Sent: Monday, October 13, 2014 9:57 PM
  To: firebird-support@yahoogroups.com 
  Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?

   
  Hi Martijn,

  I knew I was going to get in trouble by not providing enought information as 
I thought by proving less it would be just a little bit clearer to understand, 
my bad...

  Let's start over with an simplified explanation of the tables:

  ACCT_CASE: Case Management table
  ACCT_IDINTEGERNOT NULLPK
  CASE_IDSMALLINTNOT NULLPK
  CLT_IDINTEGERNOT NULLFK to CLIENT table   Need this for the JOIN
  ...

  DEBTOR_CASE_DEBT:  Allows for multiple PERSON's to be associated with a DEBT
  ACCT_IDINTEGERNOT NULLPK
  CASE_IDSMALLINTNOT NULLPK
  DEBT_NOSMALLINTNOT NULLPK
  PERSON_IDINTEGERNOT NULLPK
  STATUS_DATETIMESTAMPNOT NULL
  STATUS_CODECHAR(1)NOT NULL

  What am I attempting to do?  I need to know how many records are in the 
DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and 
'09/30/14' and the STATUS_CODE = 'B (Bankruptcy Filed) and is for a specific 
CLT_ID (thus the join to ACCT_CASE to use CLT_ID).  I do not want to include 
the PERSON_ID when fetching a COUNT() of the record, I only need to know how 
many debts are in this status for the client.  So only concerned with ACCT_ID, 
CASE_ID and DEBT_NO.

  So this SQL will return the correct number of records, now I just have to 
figure out how to return a count in one record.

SELECT DISTINCT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO
   FROM DEBTOR_CASE_DEBT DCD
   JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
AND AC.CASE_ID = DCD.CASE_ID
  WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
AND DCD.STATUS_CODE = 'B'
AND AC.CLT_ID = :V_CLT_ID

  Did I provide enough information this time?  If not feel free to ask...

  Thanks so much,
  Mike


- Original Message - 
From: mailto:m.ton...@upscene.com [firebird-support] 
To: firebird-support@yahoogroups.com 
Sent: Monday, October 13, 2014 2:20 PM
Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a 
JOIN is involved?

  

Hello Mike,

Just happening to be testing this in Database Workbench.  Have used this 
product for years and just love it.

Thank you, that’s good to hear.

Thanks for you reply.
 
So my second SQL should have been as follows?  It results in an error 
Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , 

COUNT 

[firebird-support] wait vs nowait on meta data statements

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

I noticed that with Classic 2.5, you can get “object in use” when trying to 
modify a
stored procedure that’s been previously executed, see also:
http://tracker.firebirdsql.org/browse/CORE-4016


When using “wait” on the transaction to save the procedure, instead of 
“nowait”, 
the problem goes away.

Is it safe to always use “wait” on such transactions?

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!

[firebird-support] Setting blob values to a binary hex literal returns strange results (PSQL)

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

I’ve got this following problem and example:

SET TERM ^^ ;
create PROCEDURE DEBUG_TEST
returns (
  C BLOB SUB_TYPE 0, 
  M2 Numeric(18,0), 
  I2 VarChar(10))
AS
declare variable i varchar(4) = X'4131';
declare variable m numeric(18) = 0x2121;
declare variable b blob; 
begin
c = 0x40; 
m2 = m;
i2 = i; 
suspend;
end ^^
SET TERM ; ^^

As you can see, output BLOB “c” gets a binary value of 0x40, as per hex literal 
in Firebird 2.5, a
single byte.

However, when I now query the routine:

select * from debug_test;

The blob returned is:
64

That is, ‘64’ in characters! Two characters!

I’ve verified this in Database Workbench, BLOB Editor for hex values, returned 
is:
3634

And using f_blobsize function in FreeAdhocUDF, which returns a size of “2”.


Any clue why this happens?



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!

Re: [firebird-support] Re: How to populate a new table column with a INTEGER sequence?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Wouldn’t leaving out the “temp trigger” and “foo” and simply doing:

update sometable set id = gen_id(some_table_id_gen, 1);

have the same result?

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!



From: mailto:firebird-support@yahoogroups.com 
Sent: Monday, September 22, 2014 2:36 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Re: How to populate a new table column with a 
INTEGER sequence?




Hi to all,

This is one approach (Maybe could be done one more efficient):

/**
* Firebird 2.5
*/

-- 1.Create new fields
ALTER TABLE some_table
  ADD id INTEGER DEFAULT 0 NOT NULL,
  ADD foo_dummy CHAR(1);

-- 2.Create generador
CREATE GENERATOR some_table_id_gen;

-- 3.Create temp trigger
SET TERM ^ ;
CREATE TRIGGER some_table_temp_trgr FOR some_table
ACTIVE BEFORE UPDATE POSITION 0
AS 
BEGIN 
  new.id = GEN_ID(some_table_id_gen, 1);
END^
SET TERM ; ^ 

-- 4.Update foo_dummy field and so set the sequency
--   into field ID
UPDATE some_table SET foo_dummy = '1';

-- 5.Drop temp trigger and foo_ new PK
ALTER TABLE some_table ADD CONSTRAINT some_table_pk PRIMARY KEY (ID);

-- 9.Create new trigger for mantaining new PK
SET TERM !! ;
CREATE TRIGGER some_table_set_id FOR some_table
BEFORE INSERT POSITION 0 AS
BEGIN
  NEW.id = GEN_ID(some_table_id_gen, 1);
END !!
SET TERM ; !!

/* */

Best regards,

Hernando.

Re: [firebird-support] Re: SubQuery Help

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

I think this needs a

GROUP BY a.Project_PK

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, September 16, 2014 6:12 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Re: SubQuery Help




I'd recommend using a left join with table alias. 

Example


Select
  a.Project_PK
, SUM(b.cost) AS Sum_of_Cost
, SUM(b.estimate_cost) AS Sum_of_Estimated_Cost
, SUM(c.quantity) AS Sum_of_Quantity
, SUM(c.estimate_quantity) AS Sum_of_Estimated_Quantity
FROM Projects a
LEFT JOIN Cost b ON a.project_pk = b.project_pk
LEFT JOIN Quantity c ON a.project_pk = c.project_pk

-- 

Dixon Epperson

Re: [firebird-support] Re: SubQuery Help

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

It did, but my fingers were quicker. 

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, September 16, 2014 6:38 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: SubQuery Help




Yes it does.   I posted a correction but apparently it never made it
Dixon



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

Re: [firebird-support] DROP CONSTRAINT only shows effect after commit?

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Patrick,

Yes, that is correct, meta data changes need to be committed as well.

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Thursday, August 28, 2014 6:02 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] DROP CONSTRAINT only shows effect after commit?



Hello,

any attempt to do a search ends with the screen


Oops!
Polls feature not enabled 



for some reason, so I have to ask directly:

Is it correct, that a commit is required after some contraints have been 
removed, before I can do some data changes which would violate those 
constraints?




Hoped to do it in one transaction, but I'm getting a violation error for one of 
the removed contraints when inserting some data, although a check via 
RDB$RELATION_CONSTRAINTS says the constraint isn't there anymore...




Best regards,

Patrick

Re: [firebird-support] Extracting check values from domain

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Alan,

The domain check constraint is available, however, it is a custom
written check constraint, like for example:

CREATE DOMAIN ... 
CHECK (
VALUE IN ('Y', 'N')
)

This source can be found in the system tables, table RDB$FIELDS
column RDB$VALIDATION_SOURCE


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!





Hi, can anyone help me with the following please?
As an example, I have a domain - YES_NO - with strangely enough only Y 
or N acceptable as input.
I then use this domain in various tables.
In Delphi I then check if 'Y' or 'N' is entered before trying to write 
to the database. Yes, I know the write will fail but I want to inform 
the user immediately on exit from the entry, rather than when getting to 
the final stage of data-entry, say clicking OK.
Instead of hard-coding (paraphrasing) If entry is not in 'Y','N' try 
again, can I get the domain and the domain constraints available to me 
with some sql code? Such as If entry is not in YES_NO.CHECK try again 
substituting the real values

I hope I've explained this.


Alan J Davies
Aldis

Re: [firebird-support] remove constraint

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Wolfgang,

ALTER TABLE tablename DROP CONSTRAINT INTEG_485


That should do the trick.

Or find the constraint and simply click Drop in Database Workbench. ;)


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!



-Original Message- 
From: Wolfgang Kluge wkl...@kb-labuan.com [firebird-support]
Sent: Monday, July 07, 2014 8:22 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] remove constraint

Good afternoon.

Can anypme give me a hint how I can by code remove a cpnstraiant in a
populated database?
The constraint type is check and the constraint name is integ_485

regards
Wolfgang Kluge




Posted by: Wolfgang Kluge wkl...@kb-labuan.com


++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu 
there.

Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] Get check constraints info

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hello Maxi,

Check constraints are table based, not column based.

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!


From: mailto:firebird-support@yahoogroups.com 
Sent: Thursday, June 05, 2014 5:52 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Get check constraints info




Hi,

I need to get a check constraints info, listing column names.

I do:

SELECT * 
  FROM rdb$triggers trg 
JOIN rdb$check_constraints chk_k ON trg.rdb$trigger_name = 
chk_k.rdb$trigger_name 
   
  WHERE 
  trg.rdb$relation_name = 'SCHEMA_AUTHOR'
AND 
  trg.rdb$trigger_type = 1 



But I need the column names to which affect.

How I have to do the sql?




















--
Maxi



b: http://maxirobaina.tumblr.com
t: http://twitter.com/maxirobaina