Skip to search.

Breaking News Visit Yahoo! News for the latest.

×Close this window

firebird-support · Support for Users of Firebird Releases

The Yahoo! Groups Product Blog

Check it out!

Group Information

? Already a member? Sign in to Yahoo!

Yahoo! Groups Tips

Did you know...
Message search is now enhanced, find messages faster. Take it for a spin.

Messages

Advanced
Messages Help
Messages 116835 - 116864 of 121816   Oldest  |  < Older  |  Newer >  |  Newest
Messages: Show Message Summaries Sort by Date ^  
#116835 From: "Sergio H. Gonzalez" <shg_sistemas@...>
Date: Fri Feb 3, 2012 4:21 pm
Subject: selecting between dates with timestamps fields
shg_sistemas
Send Email Send Email
 
Hello! I have a timestamp field with an index

how can I select the fields from a given date, but using the index?

If I do:

where
     ((cast (MyTimeStamp as date)) between :from_date and :to_date)

I guess that is better not to do the cast and pass the params with date
+ time.

I use Delphi and I don't know how to add the time to the date in order
to pass the parameter to the query...

I'm trying with (to get all records from Feb 2nd)

where
     (MyTimeStamp between '02-01-2012 00:00:00' and '02-01-2012 23:59:59')

it uses the index, but I don't get all the records

Thanks!!!!

-s






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

#116836 From: "wang960" <wang960@...>
Date: Fri Feb 3, 2012 4:36 pm
Subject: Centos 6.2 64 bit and ibase.h missing
wang960
Send Email Send Email
 
hi,

using this to install firebird:

yum install firebird-classic.x86_64

it works in centos 6.2. now where is the ibase.h? i need that to compile some
other modules.

Thanks,

Wang

#116837 From: "Sergio" <shg_sistemas@...>
Date: Fri Feb 3, 2012 4:48 pm
Subject: Re: selecting between dates with timestamps fields
shg_sistemas
Send Email Send Email
 
> I'm trying with (to get all records from Feb 2nd)
>
> where
>     (MyTimeStamp between '02-01-2012 00:00:00' and '02-01-2012 23:59:59')
>
> it uses the index, but I don't get all the records


My mistake!... that seems to work OK... Now I just wonder if that is the *best*
way to do that... So far I see no problem, but just in case...

-s

#116838 From: Ann Harrison <aharrison@...>
Date: Fri Feb 3, 2012 5:12 pm
Subject: Re: best type for storing 0.686956521739130434782?
annwharrison
Send Email Send Email
 
On Thu, Feb 2, 2012 at 6:50 AM, angel_ignacio_colmenares
<angel_ignacio_colmenares@...> wrote:
> I'm writing a firebird provider for ServiceStack.OrmLite and unit tests
> ask for store  the value : 0.686956521739130434782 in a table.
> the datatable is simple:
>
> CREATE TABLE TEST
> (
>    v1 float,
>    v2 float
> );


Float is a useless datatype, having only seven digits of decimal
precision.  You'll do better with double - with about 16 decimal
digits of precision.  Even that isn't enough for an accurate
representation of your number, which needs 21 digits.  I think the
closest you'll come is decimal [19,19]

Ann

#116839 From: Russell Rose <russell@...>
Date: Fri Feb 3, 2012 1:08 pm
Subject: Using fbtracemgr
russ3ell
Send Email Send Email
 
Hi, I'm trying to use fbtracemgr, but I get nothing coming out after I've run a
query.  Here is my config file:
<database mydb.fdb>
       enabled true
log_transactions true
log_trigger_start true
log_trigger_end true
print_perf true
prin_plan true
log_procedure_start true
log_procedure_finish true
</database>

and here is my command:
fbtracemgr.exe -SE localhost:service_mgr -USER SYS DBA -PASS masterkey -NAME
"my_trace" -START -C "my_trace.cfg" > my_trace.log

Is there anything I'm doing wrong?  Have I left anything out?


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

#116840 From: ruchbah@...
Date: Fri Feb 3, 2012 3:25 pm
Subject: max() on primary key very slow
firefly13...
Send Email Send Email
 
Hallo,

SQL databases are always in for a surprise about performance of simple
statements.

I have a table with the column 'ID' as BIGINT unique primary key.

The table has about 4000000 entries, the ID counts up without gaps.

A simple

	 select max(ID) from T

takes about 14 seconds complete.

Execution plan:

	 PLAN (T NATURAL)

So Firebird seems to do plan a full table scan. (Hallo, anyone at home?
I have an unique index on that column ;-) )

Some databases can do so much better here: The same statement on a 100%
identical Derby database completes immediately, as does on an Oracle 10g XE.

Do I miss something ? Any suggestions ?

Best regards

Marc

#116841 From: Matthias Hanft <mh@...>
Date: Fri Feb 3, 2012 6:10 pm
Subject: Re: max() on primary key very slow
matthias_hanft
Send Email Send Email
 
ruchbah@... wrote:
>
> I have a table with the column 'ID' as BIGINT unique primary key.
>  select max(ID) from T
> So Firebird seems to do plan a full table scan. (Hallo, anyone at home?
> I have an unique index on that column ;-) )

As far as I know, you need a DESC index for MAX queries.

Matt

#116842 From: Thomas Steinmaurer <ts@...>
Date: Fri Feb 3, 2012 6:40 pm
Subject: Re: Using fbtracemgr
iblogmanager
Send Email Send Email
 
> Hi, I'm trying to use fbtracemgr, but I get nothing coming out after I've run
a query.  Here is my config file:
> <database mydb.fdb>
>        enabled true
> log_transactions true
> log_trigger_start true
> log_trigger_end true
> print_perf true
> prin_plan true
> log_procedure_start true
> log_procedure_finish true
> </database>
>
> and here is my command:
> fbtracemgr.exe -SE localhost:service_mgr -USER SYS DBA -PASS masterkey -NAME
"my_trace" -START -C "my_trace.cfg">  my_trace.log
>
> Is there anything I'm doing wrong?  Have I left anything out?

Do you get an error when starting the trace session or do you get a line
that the trace session has been started successfully?

With what connect string does your client appliation connect?

--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/

#116843 From: Thomas Steinmaurer <ts@...>
Date: Fri Feb 3, 2012 6:43 pm
Subject: Re: corrupted table or record?
iblogmanager
Send Email Send Email
 
> Firebird 1.5 on Windows, accessed via ODBC.
> Have a large table (some 50 million records) with the following structure:
>
> CREATE TABLE AUDIT_TRAIL
> (
>    AUDIT_ID                 INTEGER         NOT NULL,
>    AUDIT_TIME                  DATE,
>    USER_ID                  VARCHAR(    12)  COLLATE NONE,
>    TABLE_ID                SMALLINT,
>    ACTION                  SMALLINT,
>    PRIMARY_KEY              VARCHAR(   128)  COLLATE NONE,
>    DATA                     VARCHAR(  5000)  COLLATE NONE,
>    PATIENT_ID               INTEGER
> );
>
> When I run this query via ODBC:
>
> select
> *
> from
> audit_trail
> where
> audit_id>= 46262765
>
> The query will hang and I need to kill the app.
>
> I have figured out that the problem is with one record with AUDIT_ID 46270901.
>
> This suspicion is based on the following:
>
> select
> *
> from
> audit_trail
> where
> (audit_id>= 46262765 and
> audit_id<= 46270900) or
> audit_id>= 46270902
>
> This will run fine.
>
> select
> *
> from
> audit_trail
> where
> (audit_id>= 46262765 and
> audit_id<  46270901) or
> audit_id>  46270901
>
> This will hang/crash.
>
> If I run these queries in Workbench it will run fine, but the record
> with AUDIT_ID 46270901 will not show.
> In this table all AUDIT_ID's are consecutive, except for this 46270901
> that is either missing or I think corrupt.
>
> Is this normal behaviour?
> Could this record be fixed?
> Thanks for any insight.

- Have you run gfix to check for corruption?
- What's happening when you fool the optimizer to not use the index on
audit_id (I guess audit_id is the primary key column) by adding +0 to
the field in the where clause.


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/

#116844 From: Thomas Steinmaurer <ts@...>
Date: Fri Feb 3, 2012 6:44 pm
Subject: Re: max() on primary key very slow
iblogmanager
Send Email Send Email
 
> SQL databases are always in for a surprise about performance of simple
> statements.
>
> I have a table with the column 'ID' as BIGINT unique primary key.
>
> The table has about 4000000 entries, the ID counts up without gaps.
>
> A simple
>
>  select max(ID) from T
>
> takes about 14 seconds complete.
>
> Execution plan:
>
>  PLAN (T NATURAL)
>
> So Firebird seems to do plan a full table scan. (Hallo, anyone at home?
> I have an unique index on that column ;-) )
>
> Some databases can do so much better here: The same statement on a 100%
> identical Derby database completes immediately, as does on an Oracle 10g XE.
>
> Do I miss something ? Any suggestions ?

You need an DESCENDING index on the primary key column to speed up a MAX
operation, but I wonder what you are doing with that value then?
Hopefully not incrementing the value by 1 and use that as primary key
value then?


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/

#116845 From: Bart Smissaert <bart.smissaert@...>
Date: Fri Feb 3, 2012 6:57 pm
Subject: Re: corrupted table or record?
bartsmissaert
Send Email Send Email
 
Thanks for the suggestions.
For some strange reason all seems to be fine now. DB Workbench finds the
record
(and looks normal data) and works fine via ODBC as well. No idea what went
on there.

RBS

On Fri, Feb 3, 2012 at 6:43 PM, Thomas Steinmaurer <ts@...>wrote:

> **
>
>
> > Firebird 1.5 on Windows, accessed via ODBC.
> > Have a large table (some 50 million records) with the following
> structure:
> >
> > CREATE TABLE AUDIT_TRAIL
> > (
> > AUDIT_ID INTEGER NOT NULL,
> > AUDIT_TIME DATE,
> > USER_ID VARCHAR( 12) COLLATE NONE,
> > TABLE_ID SMALLINT,
> > ACTION SMALLINT,
> > PRIMARY_KEY VARCHAR( 128) COLLATE NONE,
> > DATA VARCHAR( 5000) COLLATE NONE,
> > PATIENT_ID INTEGER
> > );
> >
> > When I run this query via ODBC:
> >
> > select
> > *
> > from
> > audit_trail
> > where
> > audit_id>= 46262765
> >
> > The query will hang and I need to kill the app.
> >
> > I have figured out that the problem is with one record with AUDIT_ID
> 46270901.
> >
> > This suspicion is based on the following:
> >
> > select
> > *
> > from
> > audit_trail
> > where
> > (audit_id>= 46262765 and
> > audit_id<= 46270900) or
> > audit_id>= 46270902
> >
> > This will run fine.
> >
> > select
> > *
> > from
> > audit_trail
> > where
> > (audit_id>= 46262765 and
> > audit_id< 46270901) or
> > audit_id> 46270901
> >
> > This will hang/crash.
> >
> > If I run these queries in Workbench it will run fine, but the record
> > with AUDIT_ID 46270901 will not show.
> > In this table all AUDIT_ID's are consecutive, except for this 46270901
> > that is either missing or I think corrupt.
> >
> > Is this normal behaviour?
> > Could this record be fixed?
> > Thanks for any insight.
>
> - Have you run gfix to check for corruption?
> - What's happening when you fool the optimizer to not use the index on
> audit_id (I guess audit_id is the primary key column) by adding +0 to
> the field in the where clause.
>
> --
> With regards,
> Thomas Steinmaurer (^TS^)
> Firebird Technology Evangelist
>
> http://www.upscene.com/
> http://www.firebirdsql.org/en/firebird-foundation/
>
>


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

#116846 From: "angel_ignacio_colmenares" <angel_ignacio_colmenares@...>
Date: Fri Feb 3, 2012 7:42 pm
Subject: Re: best type for storing 0.686956521739130434782?
angel_ignaci...
Send Email Send Email
 
thanks Ann,
You're right, the best option seems to be decimal (18,18).
(19,19) doesn't work :
SQL error code = -842. Precision must be from 1 to 18


--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> On Thu, Feb 2, 2012 at 6:50 AM, angel_ignacio_colmenares
> <angel_ignacio_colmenares@...> wrote:
> > I'm writing a firebird provider for ServiceStack.OrmLite and unit tests
> > ask for store  the value : 0.686956521739130434782 in a table.
> > the datatable is simple:
> >
> > CREATE TABLE TEST
> > (
> >    v1 float,
> >    v2 float
> > );
>
>
> Float is a useless datatype, having only seven digits of decimal
> precision.  You'll do better with double - with about 16 decimal
> digits of precision.  Even that isn't enough for an accurate
> representation of your number, which needs 21 digits.  I think the
> closest you'll come is decimal [19,19]
>
> Ann
>

#116847 From: Ann Harrison <aharrison@...>
Date: Fri Feb 3, 2012 8:07 pm
Subject: Re: max() on primary key very slow
annwharrison
Send Email Send Email
 
On Fri, Feb 3, 2012 at 10:25 AM,  <ruchbah@...> wrote:
>
> SQL databases are always in for a surprise about performance of simple
> statements.

Particularly true when you switch between record locking and MVCC.

>
> I have a table with the column 'ID' as BIGINT unique primary key.
>
> The table has about 4000000 entries, the ID counts up without gaps.
>
> A simple
>
>        select max(ID) from T
>
> takes about 14 seconds complete.
>
> Execution plan:
>
>        PLAN (T NATURAL)
>
> So Firebird seems to do plan a full table scan. (Hallo, anyone at home?
> I have an unique index on that column ;-) )

Yes, there is someone at home.  The problem is that in an MVCC system,
the MAX value in the index may belong to a record that's not
appropriate for your transaction.  Firebird indexes cannot be
traversed backward and can change between reads.

> Some databases can do so much better here: The same statement on a 100%
> identical Derby database completes immediately, as does on an Oracle 10g XE.

Right.  And neither of those has index entries for transactions with
different snapshots of the database.

If you actually need to find the MAX often, create a descending index.
  If you're using the MAX to figure out what the next id should be, use
generators (aka sequences).

Good luck,

Ann
>
> Do I miss something ? Any suggestions ?
>
> Best regards
>
> Marc
>
>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>

#116848 From: Net Newbie <netfirebird@...>
Date: Fri Feb 3, 2012 9:24 pm
Subject: join to select top 1 of another table
netfirebird@...
Send Email Send Email
 
what is the best way to do a join to a "top 1 row" select of second
table? For example CAR and CAR_IMAGES to display just one image.
I wrote following select, this works. However I think there must be a
clever way to use "ROWS 1" instead of "min() and group by".

SELECT
CMRK.CAR_MARK_NAME,
CMDL.CAR_MODEL_NAME,
fltp.fuel_type_name,
C.MANUF_YEAR,
C.KILOMETER,
C.PRICE,
C.CURRENCY_CODE,
c.CAR_INFO,
clr.COLOR_NAME,
p.name,
p.phone,
p.email,
ci.car_image_id

FROM
CAR c join CAR_MARK cmrk on (C.CAR_MARK_ID=CMRK.CAR_MARK_ID)
join CAR_MODEL cmdl on (C.CAR_MODEL_ID=CMDL.CAR_MODEL_ID)
join  party p on (c.party_id=p.party_id)
left join fuel_type fltp on (c.fuel_type_id=fltp.fuel_type_id)
left join color clr on (c.color_id=clr.color_id)
left join (select car_id, min(car_image_id) as car_image_id from
car_image group by car_id) ci on (c.car_id=ci.car_id)
where
c.car_id=:car_id

#116849 From: "Leyne, Sean" <Sean@...>
Date: Fri Feb 3, 2012 9:45 pm
Subject: RE: join to select top 1 of another table
seanchk_98
Send Email Send Email
 
> what is the best way to do a join to a "top 1 row" select of second table? For
> example CAR and CAR_IMAGES to display just one image.
> I wrote following select, this works. However I think there must be a clever
> way to use "ROWS 1" instead of "min() and group by".
>
> SELECT
> CMRK.CAR_MARK_NAME,
> CMDL.CAR_MODEL_NAME,
> fltp.fuel_type_name,
> C.MANUF_YEAR,
> C.KILOMETER,
> C.PRICE,
> C.CURRENCY_CODE,
> c.CAR_INFO,
> clr.COLOR_NAME,
> p.name,
> p.phone,
> p.email,
> ci.car_image_id
> FROM CAR c
>   join CAR_MARK cmrk on (C.CAR_MARK_ID=CMRK.CAR_MARK_ID)
>   joinCAR_MODEL cmdl on (C.CAR_MODEL_ID=CMDL.CAR_MODEL_ID)
>   join  party p on (c.party_id=p.party_id)
>   left join fuel_type fltp on (c.fuel_type_id=fltp.fuel_type_id)
>   left join color clr on (c.color_id=clr.color_id)
>   left join (
>      select car_id, min(car_image_id) as car_image_id from car_image group by
car_id
>    ) ci on (c.car_id=ci.car_id) where c.car_id=:car_id

SELECT
   CMRK.CAR_MARK_NAME,
   ...
   p.email,
   (
      select min(ci.car_image_id) from car_image ci where ci.car_id = c.car_id
   ) as ci.car_image_id
FROM CAR c
   join CAR_MARK cmrk on (C.CAR_MARK_ID=CMRK.CAR_MARK_ID)
   join CAR_MODEL cmdl on (C.CAR_MODEL_ID=CMDL.CAR_MODEL_ID)
   join party p on (c.party_id=p.party_id)
   left join fuel_type fltp on (c.fuel_type_id=fltp.fuel_type_id)
   left join color clr on (c.color_id=clr.color_id)


Sean

P.S. I would suggest that the "headshot" image for a given car be stored as a
field in the Car table, to save yourself for the extra select.

#116850 From: Chuck Belanger <phytotech@...>
Date: Sat Feb 4, 2012 5:02 am
Subject: How to rename the database after backup/restore?
belangerc94805
Send Email Send Email
 
Hello:

After using Firebird (2.5 SS) exclusively in a desktop application
environment, I've created an associated app that stores data at an
internet address.

I just finished the code to allow me via a remote site to backup and
immediately restore as a maintenance operation the database.

My plan was to back up to a temp file, then restore that to another temp
file, Disconnect, Shutdown the database with GFIX then FTP.rename the
original database to a filename with time stamp string and then
FTP.rename the temp file to the original database name.

My mistake is that GFIX shutdown would allow this to happen.

How do you allow a rename of the database in an FTP operation? Or is
there a better way of doing what I am trying to do? I am hesitant to do
a restore directly over the original database, but maybe someone can
advise me on that?

Thank you,

Chuck Belanger

#116851 From: Chuck Belanger <phytotech@...>
Date: Sat Feb 4, 2012 7:32 am
Subject: Re: How to rename the database after backup/restore?
belangerc94805
Send Email Send Email
 
This is how I ended up doing it:

Initially I have been using IBOServices to do my local hard drive FDB
backups.

My routine that seems to work now:
1. Check for connected users using a query of MON$Attachments, other
than connection used in the utility app.
2. Disconnect the app I'm using.
3. GFIX shut force 0
4. Do a IBOServices backup with verbose, mainly to make sure the backup
file actually exists on the server when the code progresses to the
restore line. This creates a file with a time string embedded in its name.

5. IBOServices RESTORE simply does not work to write over the existing
FDB, so I use a ShellAPI call to GBAK using the service mgr -r o switch.
Works fine.

The backup gives me some confidence that I have a good file if something
should go wrong when restoring directly to the DB.

Afterward, I re-open the queries for the program, after putting the DB
online and re-connecting.

Is there something I'm missing for doing this DB backup/restore maintenance?

Thank you,

Chuck Belanger
> Hello:
>
> After using Firebird (2.5 SS) exclusively in a desktop application
> environment, I've created an associated app that stores data at an
> internet address.
>
> I just finished the code to allow me via a remote site to backup and
> immediately restore as a maintenance operation the database.
>
> My plan was to back up to a temp file, then restore that to another temp
> file, Disconnect, Shutdown the database with GFIX then FTP.rename the
> original database to a filename with time stamp string and then
> FTP.rename the temp file to the original database name.
>
> My mistake is that GFIX shutdown would allow this to happen.
>
> How do you allow a rename of the database in an FTP operation? Or is
> there a better way of doing what I am trying to do? I am hesitant to do
> a restore directly over the original database, but maybe someone can
> advise me on that?
>
> Thank you,
>
> Chuck Belanger
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>

#116852 From: "peppepolpo" <peppepolpo@...>
Date: Sat Feb 4, 2012 9:18 am
Subject: impossible behaviour ... FB bug ...or I need shaper eyes :)
peppepolpo
Send Email Send Email
 
plainly said, an impossible thing is happening with a few simple stored
procedures in FB 2.1 .

I asked for paid help at
http://www.vworker.com/RentACoder/misc/BidRequests/ShowBidRequest.asp?lngBidRequ\
estId=1824928

If you want to help for free, you can download db and explanations from this
link
http://www.sendspace.com/file/y81xsn
...and I will owe you a beer :)

Thank you

Peppe

#116853 From: Mark Rotteveel <mark@...>
Date: Sat Feb 4, 2012 10:31 am
Subject: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
mark@...
Send Email Send Email
 
On 4-2-2012 10:18, peppepolpo wrote:
> plainly said, an impossible thing is happening with a few simple stored
procedures in FB 2.1 .
>
> I asked for paid help at
>
http://www.vworker.com/RentACoder/misc/BidRequests/ShowBidRequest.asp?lngBidRequ\
estId=1824928
>
> If you want to help for free, you can download db and explanations from this
link
> http://www.sendspace.com/file/y81xsn
> ...and I will owe you a beer :)

Maybe it is me, but I don't see any clear problem description and I am
not about to download random files from unknown websites.

Mark

--
Mark Rotteveel

#116854 From: "patrick_marten" <patrick_marten@...>
Date: Sat Feb 4, 2012 11:24 am
Subject: Re: Firebird ODBC driver: connection to an embedded server by using security2.fdb
patrick_marten
Send Email Send Email
 
Hello Thomas,

sorry for the late reply. I got distracted by other things :(

Now that some time has passed, I'm having trouble to remember the last status on
this issue, unfortunately.
Trying to restore it in my memory, but the result is not the same, I believe. So
I'm more or less starting from zero again.

I've tried to divide the post into separate parts to keep some kind of
structure...


========
Part 1:
========
To my question

>> creation of a ODBC connection to an embedded server works with SYSDBA only,
>> is that correct? I was not able to create one with another user.

you replied with

> No. You should be able to connect with a different user as well. It
> might fail for some reason because the specified user doesn't have
> sufficient privileges on database objects ...

I've tried to set it up again and apparently something was wrong with my
previous attempts. Now I can connect with every combination:
- SYSDBA + (correct / wrong password)
- any other users with random passwords
- and also without any users / passwords at all

so in the end it comes down to what you said:

>> Embedded works that way: The provided user/password combination isn't
>> checked against the security database, but SQL privileges are checked
>> for the provided user when it comes to accessing tables, views etc ...

I think I get it now, but one question regarding this remains:
If I'm not totaly wrong, there is no real user management for the embedded
solution. How can the provided user / his privileges be checked then?

Is it enough to say

CREATE ROLE READACCESS;
GRANT READACCESS TO SOMEUSERNAME;

although the user "SOMEUSERNAME" doesn't really exist?


========
Part 2:
========
When I said "Now I can connect with every combination" above, I meant that the
test connection works from the ODBC interface. When I try to access the
"database" in Excel, I'm getting the error message "bad parameters on attach or
create database CHARACTER SET ISO8859_1 is not defined". My database is created
with this character set and that's also the character set of the ODBC entry.

There are several topics on several forums regarding this issue, but most are
pretty old and somehow there are tons of attempts to fix it and as far as I've
seen no proper solution. Files are being copied back and forth, path variables
etc...

Can't believe it's that complicated and weird...

In my case the end user has our application installed. It uses a firebird
database. It can be installed as an embedded solution or as a client-server
solution. At this point ODBC isn't relevant yet.
It can be set up additionaly, in case the end user needs some additional data
for exports etc.

How has it to be set up correctly, so that he can still use the "normal"
application and also ODBC?


========
Part 3:
========
Assuming everything above is sorted and set up properly, so that also access via
excel works. The roles become relevant. As far as I see, the user, which was
used for creation of the database (the owner) automatically has full access /
all privileges.

If I add another user to security.fdb - let's call him "ODBCREADUSER", create a
role "ODBCREADROLE" and grant this role to this user, it is not enough. I still
need to grant the "select"-rights for desired tables to this role (GRANT SELECT
ON CUSTOMERS TO ODBCREADROLE; etc.) - so far correct?

If at some point I need to apply some changes to the structure of my database,
i.e. add some new tables, the owner has full access / all privileges on the new
tables automatically, but for the additional user "ODBCREADUSER" or better to
say for the role used by that user, i.e. "ODBCREADROLE", it doesn't happen
automatically, so I need to call "GRANT SELECT ON NEW_TABLE1 TO ODBCREADROLE;"
explicitely for all new tables everytime I change the structure, right?

What if only some new fields have been added? Do I have to do anything regarding
the roles in such a case?


Kind regards,
Patrick


--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:
>
> > creation of a ODBC connection to an embedded server works with SYSDBA only,
is that correct? I was not able to create one with another user.
>
> No. You should be able to connect with a different user as well. It
> might fail for some reason because the specified user doesn't have
> sufficient privileges on database objects ...
>
> > If that's not correct:
> > what can be the reason for the problem I have when trying to create a
connection with a different user.
>
> Don't know without telling us the error message you get.
>
> > If that's correct:
> > the only way would be to grant SYSDBA read only rights, correct? Is that
possible at all?
>
> No.
>
>
> --
> With regards,
> Thomas Steinmaurer
>
> * Upscene Productions - Database Tools for Developers
> http://www.upscene.com/
>
> * My Blog
> http://blog.upscene.com/thomas/index.php
>
> * Firebird Foundation Committee Member
> http://www.firebirdsql.org/en/firebird-foundation/
>
>
>
> > --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@>  wrote:
> >>
> >>> how can I get Firebird ODBC driver to connect to an embedded server by
using the security2.fdb?
> >>>
> >>> Is there any reason, why the security2.fdb is being ignored in that case
at least by default?
> >>>
> >>> That way the ODBC connection can only be created with SYSDBA as user and
there is no way to make the connection read only. I mean yes, you can check the
checkbox "read" when creating the connection, but the end user can uncheck it at
any time and gat a write access to the DB...
> >>>
> >>> For the ODBC connection to a normal server he could do the same of course,
but since in that case the connection cen be created with a user, who has read
rights only, it doesn't matter if the checkbox gets unchecked.
> >>>
> >>> Why can't it work the same with embedded server? That doesn't make sense
to me... Can I accomplish it in any way?
> >>
> >> Embedded works that way: The provided user/password combination isn't
> >> checked against the security database, but SQL privileges are checked
> >> for the provided user when it comes to accessing tables, views etc ...
> >>
> >>
> >> --
> >> With regards,
> >> Thomas Steinmaurer
> >>
> >> * Upscene Productions - Database Tools for Developers
> >> http://www.upscene.com/
> >>
> >> * My Blog
> >> http://blog.upscene.com/thomas/index.php
> >>
> >> * Firebird Foundation Committee Member
> >> http://www.firebirdsql.org/en/firebird-foundation/
> >>
> >
> >
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://www.firebirdsql.org and click the Resources item
> > on the main (top) menu.  Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > Yahoo! Groups Links
> >
> >
> >
>

#116855 From: philippe makowski <makowski@...>
Date: Sat Feb 4, 2012 12:39 pm
Subject: Re: Centos 6.2 64 bit and ibase.h missing
tempo93fr
Send Email Send Email
 
wang960  [2012-02-03 17:36] :
> hi,
>
> using this to install firebird:
>
> yum install firebird-classic.x86_64
>
> it works in centos 6.2. now where is the ibase.h? i need that to compile some
other modules.
as usual in packages :


yum install firebird-devel

#116856 From: "peppepolpo" <peppepolpo@...>
Date: Sat Feb 4, 2012 2:00 pm
Subject: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
peppepolpo
Send Email Send Email
 
Hi Mark,

I thought that

www.sendspace.com/

and

www.vworker.com

would be known enough, one as a file hosting site, the other as a freelancers'
opportunities site.

Explanations are a bit long (readme.txt) and useless without the test database
(polimi.err), so I compressed both into a ZIP file.

A closer look will tell that is actually an innocent ZIP file what I invite to
download.

Is there a better way I can save a file to download and overcome any paranoia ?

As a further test, I uploaded the ZIP file as a google shared doc, too

https://docs.google.com/open?id=0B0Iy-caPIiXgOTY2MWM1ZmEtNzc0Zi00ZTFhLWJmNDAtYTR\
lMmZjMzFkMDAy

Thank you

Peppe

#116857 From: Reinier Olislagers <reinierolislagers@...>
Date: Sat Feb 4, 2012 2:09 pm
Subject: Re: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
reinier_olis...
Send Email Send Email
 
On 4-2-2012 15:00, peppepolpo wrote:
> Explanations are a bit long (readme.txt) and useless without the test
> database (polimi.err), so I compressed both into a ZIP file.
Quick look at readme (note: haven't read it, or tried it out).

In your first example: perhaps you need to do a commit after your delete
statement and before calling the stored procedure, e.g. something like
(air code, not tested):

delete from a_job
where d_ini='2012-03-05';
commit;

select result
from DUPLICATEJOB (
'a1f61856-1c13-11e1-addd-00219bfd1931',
'a7df0588-4388-11e1-addd-00219bfd1931',
'2012-03-05',
null);


Regards,
Reinier

#116858 From: "peppepolpo" <peppepolpo@...>
Date: Sat Feb 4, 2012 3:50 pm
Subject: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
peppepolpo
Send Email Send Email
 
Hi Reiner,

> perhaps you need to do a commit after your delete

not so ... and not so easy :)

I tried all the easy things :)

Thank you

Peppe

#116859 From: "peppepolpo" <peppepolpo@...>
Date: Sat Feb 4, 2012 10:44 pm
Subject: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
peppepolpo
Send Email Send Email
 
334 coders went thru my request of paid help in vworker.com until now.

None took it... :(

Peppe

#116860 From: Thomas Steinmaurer <ts@...>
Date: Sun Feb 5, 2012 9:55 am
Subject: Re: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
iblogmanager
Send Email Send Email
 
Peppe,

> Hi Reiner,
>
>> perhaps you need to do a commit after your delete
>
> not so ... and not so easy :)
>
> I tried all the easy things :)

Without providing the mandatory UDFs, one can't execute your stored
procedures.

As you are using SPs in other SPs, possibly you see some older PSQL code
executing when you are developing the stored procedures? Or perhaps
there is something wrong with your logic. ;-)



--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/

#116861 From: "peppepolpo" <peppepolpo@...>
Date: Sun Feb 5, 2012 10:24 am
Subject: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
peppepolpo
Send Email Send Email
 
somebody put me into the right track.

My SQL client seems to be too old.

When using IB_SQL created with IBObjects v. 4.8.7 my SP returns -60.

I downloaded the last version of IB_SQL (which uses IBO v. 5.0.2) and the SP
correctly returns 0.

I have the solution of my problem now.

This costed me several hours of work and frustration :(

Thank you

Peppe

#116862 From: Mark Rotteveel <mark@...>
Date: Sun Feb 5, 2012 10:44 am
Subject: Re: Re: impossible behaviour ... FB bug ...or I need shaper eyes :)
mark@...
Send Email Send Email
 
On 5-2-2012 11:24, peppepolpo wrote:
> somebody put me into the right track.
>
> My SQL client seems to be too old.
>
> When using IB_SQL created with IBObjects v. 4.8.7 my SP returns -60.
>
> I downloaded the last version of IB_SQL (which uses IBO v. 5.0.2) and the SP
correctly returns 0.
>
> I have the solution of my problem now.
>
> This costed me several hours of work and frustration :(

Good to hear that you solved it. I do want to advise you in the future
to take the time to post your actual question or at least a more
substantial introduction to your problem directly to this mailing-list
instead of making us jump through hoops like going to a different site
(and even there the actual question still isn't explained). And then
requiring us to download all kinds of files to even discover what your
actual problem is.

You are the one with the problem, so it is up to you to make it easy to
get actual help.

Mark
--
Mark Rotteveel

#116863 From: Net Newbie <netfirebird@...>
Date: Mon Feb 6, 2012 1:40 am
Subject: Dynamically building select inside stored procedure
netfirebird@...
Send Email Send Email
 
What is a good and recommended way to dynamically build a query inside
stored procedure?

For example a typical web portal search engine. user can select one or
many input parameters for a search.
For example car_model, fuel_type, color, gearbox_type etc. then into
select statement where part should be put only those what have value.

I started to look at first at "EXECUTE STATEMENT". but then in
documentation found sentence "Operations will be slow because the
embedded statement has to be prepared every time it is executed.".

then thought probably there is a better way? how to do it?

#116864 From: "nathanelrick" <nathanelrick@...>
Date: Mon Feb 6, 2012 7:27 am
Subject: Is it possible to change the charset of all column ?
nathanelrick
Send Email Send Email
 
Hello,

Is it possible to change the charset of all columns and also the default
database charset to NONE? i just want to change the charset definition, not the
data himself. This because i want that it is the client to be responsible to
know in with charset he store the data.

i know their is some tools to make a backup of the database in SQL script and
after we can edit this script (like in mysql), but our database is too big for
such script (+300 GO) and the backup / restore will take weeks :(

Messages 116835 - 116864 of 121816   Oldest  |  < Older  |  Newer >  |  Newest
Add to My Yahoo!      XML What's This?

Copyright © 2010 Yahoo! Inc. All rights reserved.
Privacy Policy - Terms of Service - Guidelines NEW - Help