dbi-interbase-devel — Development list for DBD::InterBase

[Dbi-interbase-devel] problems compiling DBD-InterBase-0.30

[Grosmann, R. <R.G...@sd...>]

hi all,


lately I have posted a question about problems with compiling the
DBD-InterBase-0.30 module on the DBI-users list. A list user redirected
me to this list.

I work on a Solaris platform,
I use perl-5.6.0
I have already installed DBI-1.21

During compilation, I first get error message about some SQL-defines
being undefined. I have 'fixed' this by editing dbdimp.h and commenting
out the undef-lines before the #include <ibase.h>.

This being done, the compiler stops after the resulting warnings,
complaining about some other missing definitions. I would expect these
in ibase.h, but they are not there.
Is this a problem which has occurred (and fixed) before? Should I try an
earlier version of DBD-InterBase?

I am trying this module because I want to find out if it is possible to
create blobs with this module. 

thanks in advance, ruud



output of make
==============


ruud@loki$ make
gcc -c -I/opt/interbase/include
-I/opt/perl-5.6.0-AP618/lib/site_perl/5.6.0/sun4-solaris/auto/DBI
-fno-strict-aliasing -I/opt/gdbm-1.8.0/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64 -O      -DVERSION=\"0.30\"  -DXS_VERSION=\"0.30\"
-fPIC -I/opt/perl-5.6.0-AP618/lib/5.6.0/sun4-solaris/CORE  dbdimp.c
In file included from dbdimp.h:68,
                 from InterBase.h:15,
                 from dbdimp.c:13:
/opt/interbase/include/ibase.h:2736: warning: `SQL_FLOAT' redefined
/opt/perl-5.6.0-AP618/lib/site_perl/5.6.0/sun4-solaris/auto/DBI/dbi_sql.h:31:
warning: this is the location of the previous definition
/opt/interbase/include/ibase.h:2737: warning: `SQL_DOUBLE' redefined
/opt/perl-5.6.0-AP618/lib/site_perl/5.6.0/sun4-solaris/auto/DBI/dbi_sql.h:33:
warning: this is the location of the previous definition
/opt/interbase/include/ibase.h:2739: warning: `SQL_DATE' redefined
/opt/perl-5.6.0-AP618/lib/site_perl/5.6.0/sun4-solaris/auto/DBI/dbi_sql.h:35:
warning: this is the location of the previous definition
/opt/interbase/include/ibase.h:2740: warning: `SQL_BLOB' redefined
/opt/perl-5.6.0-AP618/lib/site_perl/5.6.0/sun4-solaris/auto/DBI/dbi_sql.h:45:
warning: this is the location of the previous definition
/opt/interbase/include/ibase.h:2741: warning: `SQL_ARRAY' redefined
/opt/perl-5.6.0-AP618/lib/site_perl/5.6.0/sun4-solaris/auto/DBI/dbi_sql.h:49:
warning: this is the location of the previous definition
dbdimp.c: In function `dbd_db_login6':
dbdimp.c:444: `isc_dpb_SQL_dialect' undeclared (first use in this
function)
dbdimp.c:444: (Each undeclared identifier is reported only once
dbdimp.c:444: for each function it appears in.)
dbdimp.c: In function `ib_st_fetch':
dbdimp.c:1577: `ISC_TIMESTAMP' undeclared (first use in this function)
dbdimp.c:1577: parse error before `)'
dbdimp.c:1581: `ISC_DATE' undeclared (first use in this function)
dbdimp.c:1581: parse error before `)'
dbdimp.c:1585: `ISC_TIME' undeclared (first use in this function)
dbdimp.c:1585: parse error before `)'
*** Error code 1
make: Fatal error: Command failed for target `dbdimp.o'

Re: [Dbi-interbase-devel] bug in tpb ?

[Ritz D. <dan...@gm...>]

the problem is that the dbd starts a default transaction after connect()
or after AutoCommit is set to 0. set_tx_param only changed the TBP but
didn't start a new transaction....

i made some changes already commited to sourceforge cvs, the changelog is:
- no longer start a default transaction on connect or after AutoCommit is set to 0
- set_tx_param closes all statement handles and commit transaction in AutoCommit mode
- with AutoCommit = 0 the new set TPB applies after a new transaction is started 
  (old closed after commit() or rollback(), or with the first prepare() or do() if set_tx_param is
  called directly after DBI->connect() )
- updated doc for set_tx_param making this clear

should fix this:

-daniel

----- Original Message ----- 
From: "Pavel Zheltouhov" <pa...@tv...>
To: "DBD-InterBase Devel" <dbi...@li...>
Sent: Tuesday, December 25, 2001 12:54 PM
Subject: [Dbi-interbase-devel] bug in tpb ?


> 
> this code worked properly only if commits called 
> before operators
> second insert must be failed but locked.
> 
> is it bug or feature ?
> 
> use DBI;
> # create table ltab (a int not null,name char(15) not null, 
> # constraint ltab_pk primary key ( a ,name ));
> # 
> my $dbname='localhost:/tmp/z.gdb';
> 
>  my $dbh1 = DBI->connect("dbi:InterBase:db=$dbname;ib_dialect=3",
>   'sysdba','masterkey',
>     {AutoCommit => 0,RaiseError => 1 }) || die "can't connect\n";
>  my $dbh2 = DBI->connect("dbi:InterBase:db=$dbname;ib_dialect=3",
>   'sysdba','masterkey',
>     {AutoCommit => 0,RaiseError => 1 }) || die "can't connect\n";
>  
> $dbh1->func(
>         -access_mode     => 'read_write',
>         -lock_resolution => 'no_wait',
>         'set_tx_param');
> $dbh2->func(
>         -access_mode     => 'read_write',
>         -lock_resolution => 'no_wait',
>         'set_tx_param');
>  $stmt = "INSERT INTO ltab VALUES(?, 'Yustina')";
> 
>   $dbh1->commit; # if this commits not called - tpb will lost !
>   $dbh2->commit;
> 
>  $dbh1->do($stmt, undef, 1);  # must pass
>  $dbh2->do($stmt, undef, 1);  # must fail 
>  $dbh1->commit;
>  $dbh2->commit;
>  $dbh1->disconnect;
>  $dbh2->disconnect;
> 
> -- 
> Pavlo
> 
> 
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> https://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> 

[Dbi-interbase-devel] bug in tpb ?

[Pavel Z. <pa...@tv...>]

this code worked properly only if commits called 
before operators
second insert must be failed but locked.

is it bug or feature ?

use DBI;
# create table ltab (a int not null,name char(15) not null, 
# constraint ltab_pk primary key ( a ,name ));
# 
my $dbname='localhost:/tmp/z.gdb';

 my $dbh1 = DBI->connect("dbi:InterBase:db=$dbname;ib_dialect=3",
  'sysdba','masterkey',
    {AutoCommit => 0,RaiseError => 1 }) || die "can't connect\n";
 my $dbh2 = DBI->connect("dbi:InterBase:db=$dbname;ib_dialect=3",
  'sysdba','masterkey',
    {AutoCommit => 0,RaiseError => 1 }) || die "can't connect\n";
 
$dbh1->func(
        -access_mode     => 'read_write',
        -lock_resolution => 'no_wait',
        'set_tx_param');
$dbh2->func(
        -access_mode     => 'read_write',
        -lock_resolution => 'no_wait',
        'set_tx_param');
 $stmt = "INSERT INTO ltab VALUES(?, 'Yustina')";

  $dbh1->commit; # if this commits not called - tpb will lost !
  $dbh2->commit;

 $dbh1->do($stmt, undef, 1);  # must pass
 $dbh2->do($stmt, undef, 1);  # must fail 
 $dbh1->commit;
 $dbh2->commit;
 $dbh1->disconnect;
 $dbh2->disconnect;

-- 
Pavlo

[Dbi-interbase-devel] table reservation

[Pavel Z. <pa...@tv...>]

I wrote table reservation support.
It seem works.Call style changed from original.I think it better.
Patch created for 0.28.4.
Please include this patch into cvs storage.


-- 
Pavlo

[Dbi-interbase-devel] DBD-InterBase-0.29 (development)

[Ritz D. <dan...@gm...>]

hi

i made big changes on dbd-interbase, not yet commited to sourceforge-cvs...

here are the test results, only the interesting ones:
t/40cursoron........ok
t/70nestedoff.......ok
t/70nestedon........ok
All tests successful.

the new version works on all my scripts, but i think more tests are needed...
your turn...

you can find the .tar.gz, a changelog and a snapshot from my local cvsroot at:
http://ritz.dnsart.com/interbase/

edwin: please give me an ok before i commit the changes.

regards
-daniel

[Dbi-interbase-devel] table reservation

[Michael S. <sm...@ma...>]

I've read in InterBase.pm, that 
"Currently, table reservation is not yet supported".
However, in InterBase.xs I see:
..............
      else if (strEQ(tx_key, "-reserving")) 
      {
        if (SvROK(ST(i + 1)) && SvTYPE( SvRV(ST(i + 1)) ) == SVt_PVAV) 
        {
          av = (AV*)SvRV(ST(i + 1));
          for (j = 0; j < av_len(av); j++) 
          {
            sv = *av_fetch(av, j, FALSE);
            if (SvROK(sv) && SvTYPE(sv) == SVt_PVHV) 
            {
              hv = (HV*)SvRV(sv);
              sv = *hv_fetch(hv, "for", 3, FALSE);

              if (strnEQ(SvPV(sv, PL_na), "shared", 6)) 
              {
                *tpb++ = isc_tpb_shared;
              }

              if (strnEQ(SvPV(sv, PL_na), "protected", 9)) 
              {
                *tpb++ = isc_tpb_protected;
              }

              sv = *hv_fetch(hv, "table", 5, FALSE);
              /* unfinished 
                            
              */                          
            } 
..............

Is the coding of "Table Reservation" almost done ?!

[Dbi-interbase-devel] Installation problems on Win32

[Paul L. T. <p.t...@cl...>]

Hi,

I've been struggling trying to install DBD::InterBase 0.28.4 on Windows 2000
running ActiveState 5.6.0.613.

Started by installing DBI using ActiveState's ppm which downloads and
installs DBI from the ActiveState web site. Then found the version of
DBD::InterBase did not contain a PPD file when I tried to install from
ActiveState. So I downloaded the zipped version of DBD::InterBase. Then I
downloaded the Borland 5.5 C++ compiler from the Borland site. I followed
the instructions in the readme but failed. I then shopped around for a VC++
6.0 compiler and tried again. Failed again. Perl is installed properly and
runs my CGI scripts. The Firebird 0.9 server runs my local computer. Any
ideas? I read that Peter Wilkinson had created a PPD distro or file. The
result of the compile attempts are as follows:

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:\WINNT\system32>cd c:\my applications\perl\dbd-interbase

C:\My Applications\Perl\DBD-InterBase>perl makefile.pl
Using DBI 1.14 installed in C:/Program Files/ClickOut
Software/Perl/site/lib/auto/DBI

InterBase bin directory

a) other
Your choice:  a
Directory:  c:/program files/clickout software/firebird/bin

Interbase include directory

a) other
Your choice:  a
Directory:  c:/program files/clickout software/firebird/include

Visual C++ directory

1) C:\Program Files\Microsoft Visual Studio\VC98/bin
a) other
Your choice:  a
Directory:  c:/program files/microsoft visual studio/vc98/bin

Interbase lib directory

a) other
Your choice:  a
Directory:  c:/program files/clickout software/firebird/lib

Full path to your test database:  c:/program files/clickout
software/firebird/examples/v5/EMPLOYEE.GDB
Username:  SYSDBA
Password:  masterkey
Checking if your kit is complete...
Looks good
Unable to find a perl 5 (by these names: C:\Program Files\ClickOut
Software\Perl\bin\Perl.exe minipe
rl perl perl5 perl5.6.0, in these dirs: C:\Program Files\ClickOut
Software\Perl\bin\ C:\Perl\bin\ C:
\PROGRA~1\Borland\Delphi5\Projects\Bpl C:\PROGRA~1\Borland\Delphi5\Bin
C:\WINNT\system32 C:\WINNT C:
\WINNT\System32\Wbem C:\Program Files\Microsoft Visual
Studio\Common\MSDev98\Bin C:\Program Files\Mi
crosoft Visual Studio\Common\Tools C:\Program Files\Microsoft Visual
Studio\VC98\bin C:\Program File
s\Common Files\InstallShield\ C:\Program Files\ClickOut Software\Perl\bin)
Running '0 -IC:\Program Files\ClickOut Software\Perl\lib C:\Program
Files\ClickOut Software\Perl\lib
\ExtUtils\xsubpp -v 2>&1' exits with status 1 at (eval 27) line 17.
Running '0 C:\Program Files\ClickOut Software\Perl\lib\ExtUtils\xsubpp
temp000 2>&1' exits with stat
us 1 at (eval 27) line 43.
Using DBI 1.14 installed in C:/Program Files/ClickOut
Software/Perl/site/lib/auto/DBI
Writing Makefile for DBD::InterBase

C:\My Applications\Perl\DBD-InterBase>

Thanks

Paul

Re: [Dbi-interbase-devel] Re: commit not committing

[Ritz D. <dan...@gm...>]

it works ok for me...

test environments:
- windows 2000, perl 5.6.1, dbd-interbase 0.28.4, ib 601,
- conectiva linux 6.0 (kernel 2.2.19/ix86), perl 5.6.0, ib 60 ss, dbd-interbase 0.28.4

i tested with a local database and with a remote database...no problems

what about your gcc on redhat 7.1? it is 2.96 right? 2.96 is known to have
problems. the latest official stable 2.9x release is 2.95.3.....2.96 was _never_ 
released as 'stable' (one of the reasons why i don't use RH...). 
may be that's the problem why the dbd is not working right...

another question: RH on x86 or on alpha or IA64?

-daniel

----- Original Message ----- 
From: "Adam Clarke" <Ada...@St...>
To: <dbi...@li...>
Sent: Thursday, October 18, 2001 3:00 AM
Subject: [Dbi-interbase-devel] Re: commit not committing


> I guess this could be a bug. Have you tried doing a seperate prepare and
> then execute?
> 
> i.e.
> 
> my $sth = $dbh->prepare('UPDATE user_info SET unavailable=?') or die
> $dbh->errstr;
> $dbh->execute($state) or die $sth->errstr;
> 
> P.S. (Gratuitous advice :)
> 
> Note the use of the placeholder ?. Use this instead of string interpolation
> for safety, security and sanity of your code. A 'do' would look like this
> ...
> 
> $dbh->do('UPDATE user_info SET unavailable=?', undef, $state) or die
> $dbh->errstr;
> 
> ----- Original Message -----
> > Date: Wed, 17 Oct 2001 11:14:11 -0700
> > From: Brad Greenlee <br...@fo...>
> 
> > Sorry to bug you all, but I'm at my wit's end. For some reason, I can't
> get anything to commit. I'm trying to do an UPDATE to a table, and I've
> tried every combination of AutoCommit on/off, using the commit statement,
> using prepare vs. do, etc. The test program below executes just fine, but
> when I check the table, it isn't updated. If I then type in 'commit' via
> isql, the update shows up. If I run the script again without having typed
> commit manually, it deadlocks.
> >
> > I've got Interbase SS 6.0.1 running on Linux (RH 7.1), with Perl 5.6.1,
> and DBD::Interbase 0.28.4.
> 
> > --------------- BEGIN db_test.pl ----------------
> > #!/usr/local/bin/perl
> >
> > use DBI;
> >
> > my $state = $ARGV[0];
> >
> > # connect to database
> > my $dbh =
> DBI->connect("dbi:InterBase:database=/opt/interbase/data/portal.gdb;host=sv-
> toby",'sysdba','masterkey',{AutoCommit => 1}) or die $dbh->errstr;
> >
> > # make all users available
> > $dbh->do("UPDATE user_info SET unavailable=$state") or die $dbh->errstr;
> >
> > # disconnect from database
> > $dbh->disconnect;
> > --------------- END db_test.pl ----------------
> 
> 
> 
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> https://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> 

[Dbi-interbase-devel] Re: commit not committing

[Adam C. <Ada...@St...>]

I guess this could be a bug. Have you tried doing a seperate prepare and
then execute?

i.e.

my $sth = $dbh->prepare('UPDATE user_info SET unavailable=?') or die
$dbh->errstr;
$dbh->execute($state) or die $sth->errstr;

P.S. (Gratuitous advice :)

Note the use of the placeholder ?. Use this instead of string interpolation
for safety, security and sanity of your code. A 'do' would look like this
...

$dbh->do('UPDATE user_info SET unavailable=?', undef, $state) or die
$dbh->errstr;

----- Original Message -----
> Date: Wed, 17 Oct 2001 11:14:11 -0700
> From: Brad Greenlee <br...@fo...>

> Sorry to bug you all, but I'm at my wit's end. For some reason, I can't
get anything to commit. I'm trying to do an UPDATE to a table, and I've
tried every combination of AutoCommit on/off, using the commit statement,
using prepare vs. do, etc. The test program below executes just fine, but
when I check the table, it isn't updated. If I then type in 'commit' via
isql, the update shows up. If I run the script again without having typed
commit manually, it deadlocks.
>
> I've got Interbase SS 6.0.1 running on Linux (RH 7.1), with Perl 5.6.1,
and DBD::Interbase 0.28.4.

> --------------- BEGIN db_test.pl ----------------
> #!/usr/local/bin/perl
>
> use DBI;
>
> my $state = $ARGV[0];
>
> # connect to database
> my $dbh =
DBI->connect("dbi:InterBase:database=/opt/interbase/data/portal.gdb;host=sv-
toby",'sysdba','masterkey',{AutoCommit => 1}) or die $dbh->errstr;
>
> # make all users available
> $dbh->do("UPDATE user_info SET unavailable=$state") or die $dbh->errstr;
>
> # disconnect from database
> $dbh->disconnect;
> --------------- END db_test.pl ----------------

[Dbi-interbase-devel] commit not committing

[Brad G. <br...@fo...>]

Sorry to bug you all, but I'm at my wit's end. For some reason, I can't get anything to commit. I'm trying to do an UPDATE to a table, and I've tried every combination of AutoCommit on/off, using the commit statement, using prepare vs. do, etc. The test program below executes just fine, but when I check the table, it isn't updated. If I then type in 'commit' via isql, the update shows up. If I run the script again without having typed commit manually, it deadlocks.

I've got Interbase SS 6.0.1 running on Linux (RH 7.1), with Perl 5.6.1, and DBD::Interbase 0.28.4.

TIA for any help.

Regards,

Brad <br...@fo...>

--------------- BEGIN db_test.pl ----------------
#!/usr/local/bin/perl

use DBI;

my $state = $ARGV[0];

# connect to database
my $dbh = DBI->connect("dbi:InterBase:database=/opt/interbase/data/portal.gdb;host=sv-toby",'sysdba','masterkey',{AutoCommit => 1}) or die $dbh->errstr;

# make all users available
$dbh->do("UPDATE user_info SET unavailable=$state") or die $dbh->errstr;

# disconnect from database
$dbh->disconnect;
--------------- END db_test.pl ----------------

[Dbi-interbase-devel] numeric data and place holders

[swade <sw...@di...>]

Hi,
    I've noticed some funny stuff about handling some numeric or decimal
data.

perl5.6.1 interbase (latest), dbi,dbd (latest)

I have a field numeric(4,2) If I use place holders to update the data with a
number like
1.03 it will update. If I update using .03 it will insert a 0. I switched
from using placeholders
on that field to number='$value' and it updated correctly. Same results
using a decimal field, etc

Also id have to actually write it down because I can't remember the issues
at the moment
but depending on how a table or view,etc is setup, a field with a value of
say .06 for instance will be pulled as .60
I believe its in a case where you might have a raw data table then a view
which sums up the data, and you then
pull the data without cast(sum(field) as numeric(4,2)) for instance. Sorry I
can't give any
more info on this one..my brain frys everytime I try to figure out the
conditions it happens under so I just
cast everything now to make sure I don't miss something. Has anyone noticed
this? seems to happen using
group by or a view with sum.

Ibconsole pulls all of these correctly.

thanks,
shawn

Re: [Dbi-interbase-devel] Timestamp field and timezone

[Edwin P. <ed...@po...>]

willems Luc wrote:
> 
> On Wednesday 26 September 2001 02:44, you wrote:
> 
> Hello Edwin ,
> 
> if i open a terminal and do date ,
> i get the correct date. I suppose that that means that my time zone should be
> ok. Also , my clock is synchronised with xntpd .
> 
> I Checked my SuSE configurqtion and timezone is set to Europe./Brussels what
> good is for me ,i live in belgium)
> 
> So , All is ok except for DBD:Interbase ???

sorry, I just tried to figure out what goes wrong. 
what's the result of `make test`, esp. t/40alltypes.t ?
what's the result of the query using isql?

the driver itself doesn't do any time or date calculation, it just
decodes (using isc_decode_* functions) the fetched value into tm struct,
and formats it using strftime().

rgds,
Edwin.

-- 
PerlMonks home node: http://perlmonks.org/?node=pope
CPAN home: http://search.cpan.org/search?author=edpratomo

Re: [Dbi-interbase-devel] Timestamp field and timezone

[willems L. <wil...@pa...>]

On Wednesday 26 September 2001 02:44, you wrote:

Hello Edwin ,

if i open a terminal and do date ,
i get the correct date. I suppose that that means that my time zone should be 
ok. Also , my clock is synchronised with xntpd .

I Checked my SuSE configurqtion and timezone is set to Europe./Brussels what 
good is for me ,i live in belgium)

So , All is ok except for DBD:Interbase ???


> willems Luc wrote:
> > ...
> >
> > I think this has to do with some time zone settings , but how can i make
> > sure that DBD::interbase uses the local time instead of some GMT time ?
>
> It should.
> On Linux, GMT is used internally, but the setting of current locale
> should be used by DBD::InterBase (and isql), as with strftime().
>
> rgds,
> Edwin.

[Dbi-interbase-devel] Timestamp field and timezone

[willems L. <wil...@pa...>]

	Hello ,


if have some tables with timestamp fields. If i do a select query on the 
table , all timestamp fields have 1 hour more than origenal inserted .

i use folowin function to generated the time defenition for the prepare 
statement.

our $dateformat="%d/%m/%Y";
our $timeformat="%T";

sub getdateformat {
  return {
     ib_timestampformat => "$dateformat $timeformat",
     ib_dateformat => "$dateformat",
     ib_timeformat => "$timeformat",
   };
};

...
$sth = $dbh->prepare($query,getdateformat);
...

I think this has to do with some time zone settings , but how can i make sure 
that DBD::interbase uses the local time instead of some GMT time ?

		luc

[Dbi-interbase-devel] rounding in IB 5.6 (Sco) with perl]

[Bill K. <bi...@so...>]

Hi,

>
> I have a field in the db which is numeric(15,2)
>
> When I sum in isql I get:
>
>                    SUM
> ======================
>
>              1858.7500
>
> Field values are as follows:
>
>                 COIN
> ======================
>
>               222.25
>               333.33
>               444.55
>               666.66
>               111.11
>                 0.00
>                 0.00
>                80.85
>
> When I extract using perl and add the values I get: 1858.74
>
> Why?
>
> Should I be rounding before adding as a previous post suggests?
>
>
> If so, does anyone know how to round to 2 decimal places in perl
> (apart from sprintf)
>
> thanks
> bill

[Dbi-interbase-devel] Problem with STORED PROCEDURE

[willems L. <wil...@pa...>]

Hello ,

I'm converting some scripts From IBPerl to DBD::Interbase version 0.28.
I'm using some stored procedures that look like this :

ALTER PROCEDURE "INSERT_VIP"
(
  AARTID INTEGER,
  AVIP VARCHAR(64),
  ASEND VARCHAR(1),
  AREPORTER VARCHAR(64),
  ACATEGORIE VARCHAR(64),
  AGEMEENTE VARCHAR(64),
  ATITEL VARCHAR(127),
  ATEKST VARCHAR(16384)
)
RETURNS
(
  AID INTEGER
)
AS

DECLARE VARIABLE AVIPID INTEGER;
DECLARE VARIABLE AREPORTID INTEGER;
DECLARE VARIABLE AGEMID INTEGER;
DECLARE VARIABLE ACATID INTEGER;

BEGIN
   /* Get Artikel ID nr */
  AID = GEN_ID(GEN_ARTIKELS_VIP,1);

   /* Convert reporter,categorie,gemeente naar ID nr) */
   EXECUTE PROCEDURE NAAM2USERID
          :AREPORTER RETURNING_VALUES :AREPORTID;

   EXECUTE PROCEDURE NAAM2USERID
          :AVIP RETURNING_VALUES :AVIPID;

   EXECUTE PROCEDURE CATEGORIE2CATEGORIEID
          :ACATEGORIE RETURNING_VALUES :ACATID;

   EXECUTE PROCEDURE GEMEENTE2GEMEENTEID
          :AGEMEENTE RETURNING_VALUES :AGEMID;
   /* Insert Artikel */
   INSERT INTO ARTIKELS_VIP
     ( ID,ARTID,VIPID,REPORTERID, CATEGORIEID, GEMEENTEID,
      TITEL , TEKST , INDATUM , BEWERKDATUM ,SEND)
  VALUES
      (:AID,:AARTID,:AVIPID, :AREPORTID,:ACATID,:AGEMID,
     :ATITEL,:ATEKST,'NOW', 'NOW' ,:ASEND);
END

When i invoke this with following code , it doesn't return the "AID" value 
like defined in the procedure ?

 $sth = $dbh->prepare("SELECT * FROM INSERT_ARTIKEL (?,?,?,?,?,?,?)") || 
            error(0,"statement error : $sth->errstr");
  #order of parameters
  # STATUSID
  # REPORTER
  # REDACTEUR
  # CATEGORIE
  # GEMEENTE
  # TITEL
  # TEKST
  $data[0] = $header->{status};
  $data[1] = $header->{reporter};
  $data[2] = $header->{redactor};
  $data[3] = $header->{category};
  $data[4] = $header->{city};
  $data[5] = $header->{title};
  $data[6] = $text;

  #execute SQL instruction
  $sth->execute(@data);
  $dataref = $sth->fetch;

the result is undefined , so this means noting is returned ?
What is going wrong ?

	luc

Re: [Dbi-interbase-devel] connection methods..

[Daniel R. <dan...@gm...>]

hello

i don't have speed problems with dbd/ib via tcp/ip.
but i've tested it only with my home network (10Mbps)
i never used dbiproxy. if you have tried it please let me 
know it worked.

rgds
-daniel

----- Original Message ----- 
From: "swade" <sw...@di...>
To: <dbi...@li...>
Sent: Thursday, August 02, 2001 7:20 AM
Subject: [Dbi-interbase-devel] connection methods..


> I just have a ?. Using perl/dbi i can connect to IB via tcpip. Is there any
> advantage/disadvantage to using the dbi proxy? When I switched to IB I
> noticed I didn't need dbi proxy as when I was using oracle..
> 
> I guess my question is this (sample setup)
> IB DB --nic1 --- hub--- clients
> [_____nic2 ---- ibport (for when I'm at home)
> 
> Clients to IB db is fast. I noticed that since switching to IB that running
> a test script from home the perl/dbi connection is *very* slow as in 20
> seconds for one record as opposed to when I was using oracle with dbiproxy
> and connecting from home. Likewise IBConsole is much faster than a perl
> script on my test box at home. Should I try dbiproxy? kinda like the ease of
> IB tcpip...nothing else to mess with but development at
> home is rather trying.
> 
> Sorry if this is a dumb question...thanks!
> shawn
> 
> 
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> 

Re: [Dbi-interbase-devel] 40alltests.t needs to set dialect 3

[Daniel R. <dan...@gm...>]

this is not a problem in t/40alltypes.t. the dialect is set correct to 3.
the problem is that employee.gdb is a dialect 1 database. 
ib 6 documentation says (GetStart.pdf):

------------------------------------------------------------------------------------------------
FEATURES AVAILABLE ONLY IN DIALECT 3 DATABASES:
The following features are available only in dialect 3 clients AND DATABASES because they
conflict with dialect 1 usage.

** Delimited identifiers
Identifiers can now be keywords, contain spaces, be case sensitive, and contain
non-ASCII characters. Such identifiers must be delimited by double quotes. String
constants must be delimited by single quotes.

** INT64 data storage
In dialect 3 databases, data stored in DECIMAL and NUMERIC columns is stored as INT64
when the precision is greater than 9. This is true only for columns that are created in
dialect 3. These same datatypes are stored as DOUBLE PRECISION in dialect 1 and in all
older InterBase versions. This change in storage also requires different arithmetic
algorithms.

** DATE and TIME datatypes
In dialect 3, the DATE datatype holds only date information. This is a change from earlier
InterBase versions in which it stored the whole timestamp.
Dialect 3 allows the use of the TIME datatype, which hold only the time portion of the
timestamp.
-----------------------------------------------------------------------------------------------

but 40alltypes.t has to test the new types, so it needs a dialect 3 database!

rgds
-daniel

----- Original Message ----- 
From: "Mark D. Anderson" <md...@di...>
To: <dbi...@li...>
Sent: Wednesday, August 01, 2001 6:13 PM
Subject: [Dbi-interbase-devel] 40alltests.t needs to set dialect 3


> running make test of DBD-InterBase-0.28.4 i got a failure in t/40alltypes.t
> this was using a test database of  /opt/interbase/examples/v5/employee.gdb
> i don't recall right now the interaction between the dialect setting on a gdb,
> and the dialect setting at connect time, but it seems like something should be
> done. btw, seems like that test db should maybe be the default answer in
> Makefile.PL?
> 
> t/30insertfetch.....ok
> t/40alltypes........DBD::InterBase::db do failed: Invalid token
> -Dynamic SQL Error
> -SQL error code = -104
> -Database SQL dialect 1 does not support reference to TIME datatype
> DBD::InterBase::db prepare failed: Undefined name
> -Dynamic SQL Error
> -SQL error code = -204
> -Table unknown
> -BUILTIN
> Can't call method "execute" on an undefined value at t/40alltypes.t line 131.
> 
> 
> t/40alltypes........ok 1/25
> 
> t/40alltypes........NOK 2
> 
> t/40alltypes........NOK 3
> 
> t/40alltypes........dubious
>         Test returned status 22 (wstat 5632, 0x1600)
> DIED. FAILED tests 2-25
>         Failed 24/25 tests, 4.00% okay
> 
> 
> 
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> 

[Dbi-interbase-devel] connection methods..

[swade <sw...@di...>]

I just have a ?. Using perl/dbi i can connect to IB via tcpip. Is there any
advantage/disadvantage to using the dbi proxy? When I switched to IB I
noticed I didn't need dbi proxy as when I was using oracle..

I guess my question is this (sample setup)
IB DB --nic1 --- hub--- clients
[_____nic2 ---- ibport (for when I'm at home)

Clients to IB db is fast. I noticed that since switching to IB that running
a test script from home the perl/dbi connection is *very* slow as in 20
seconds for one record as opposed to when I was using oracle with dbiproxy
and connecting from home. Likewise IBConsole is much faster than a perl
script on my test box at home. Should I try dbiproxy? kinda like the ease of
IB tcpip...nothing else to mess with but development at
home is rather trying.

Sorry if this is a dumb question...thanks!
shawn

Re: [Dbi-interbase-devel] set_txn_param fix confirmed

[Daniel R. <dan...@gm...>]

it was the first time i was using XS...

why it is better? 
* short answer: because it now works :-)
* long answer: 
because SvRV dereferences a Perl reference (RV*) and returns an SV*, AV* or HV*. 
to which one you have to cast it to can be figured out using SvTYPE. to check if a SV*
contains a reference you can use SvROK....(SvRV should always be used in combination 
with SvROK -> value = SvRV(SvROK(reference));

but av_fetch() returns a SV** which is only a pointer to an SV*, not a RV*, so dereferencing
a non RV* crashes Perl....

see http://www.perldoc.com/perl5.6/pod/perlguts.html for explaination of these macros,
and http://www.perldoc.com/perl5.6/pod/perlxs.html for an XS manual,
http://www.perldoc.com/perl5.6/pod/perlxstut.html for a (still incomplete) XS tutorial, 
http://gisle.aas.no/perl/illguts/ for detailed description of Perl data structures

rgds
-daniel


----- Original Message ----- 
From: "Mark D. Anderson" <md...@di...>
To: <dbi...@li...>
Sent: Wednesday, August 01, 2001 7:02 PM
Subject: [Dbi-interbase-devel] set_txn_param fix confirmed


> thanks for the fix in 28.4.
> 
> for us XS neophytes, what was the bug?
> 
> why is 
>             svp = av_fetch(av, j, FALSE);
>             if ( strEQ( SvPV_nolen(*svp), "read_committed" )) 
> 
> better than
>                     sv = (SV*)SvRV(*av_fetch(av, j, FALSE));
>                     if ( strEQ( SvPV(sv, PL_na), "read_committed" )) {
> 
> -mda
> 
> 
> 
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> 

[Dbi-interbase-devel] set_txn_param fix confirmed

[Mark D. A. <md...@di...>]

thanks for the fix in 28.4.

for us XS neophytes, what was the bug?

why is 
            svp = av_fetch(av, j, FALSE);
            if ( strEQ( SvPV_nolen(*svp), "read_committed" )) 

better than
                    sv = (SV*)SvRV(*av_fetch(av, j, FALSE));
                    if ( strEQ( SvPV(sv, PL_na), "read_committed" )) {

-mda

[Dbi-interbase-devel] 40alltests.t needs to set dialect 3

[Mark D. A. <md...@di...>]

running make test of DBD-InterBase-0.28.4 i got a failure in t/40alltypes.t
this was using a test database of  /opt/interbase/examples/v5/employee.gdb
i don't recall right now the interaction between the dialect setting on a gdb,
and the dialect setting at connect time, but it seems like something should be
done. btw, seems like that test db should maybe be the default answer in
Makefile.PL?

t/30insertfetch.....ok
t/40alltypes........DBD::InterBase::db do failed: Invalid token
-Dynamic SQL Error
-SQL error code = -104
-Database SQL dialect 1 does not support reference to TIME datatype
DBD::InterBase::db prepare failed: Undefined name
-Dynamic SQL Error
-SQL error code = -204
-Table unknown
-BUILTIN
Can't call method "execute" on an undefined value at t/40alltypes.t line 131.


t/40alltypes........ok 1/25

t/40alltypes........NOK 2

t/40alltypes........NOK 3

t/40alltypes........dubious
        Test returned status 22 (wstat 5632, 0x1600)
DIED. FAILED tests 2-25
        Failed 24/25 tests, 4.00% okay

[Dbi-interbase-devel] 0.28.4 release

[Daniel R. <dan...@gm...>]

hello

i made a new release: 0.28.4.

this release fixes:
- set_tx_param bug (isolation_level), discovered by mark d. anderson
  but it still needs some checks about sv being a valid string
- disconnect fails after failed do() or prepare() in AutoCommit mode, discovered by peter köller

rgds
-daniel

Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[<pko...@me...>]

Hello Daniel,

thanks for your prompt help. No more sleepless nights :-) Great work!

Regards,

Peter

> -----Urspr=FCngliche Nachricht-----
> Von: dbi...@li...
> [mailto:dbi...@li...]Im Auftrag von
> Daniel Ritz
> Gesendet am: Dienstag, 31. Juli 2001 14:00
> An: dbi...@li...
> Betreff: Re: [Dbi-interbase-devel] How to close open transactions with
> AutoCommit =3D 1 in case of a failed prepare?
>
> hello peter
>
> yep, it's a bug! a failed prepare does no rollback or commit in
> AutoCommit mode and disconnect doesn't too, but they
> should...
> i'll fix this asap. for now there's a little workaround:
>
> set AutoCommit to 0 right before the disconnect: (this makes
> disconnect to do a commit)
>     $_db->{AutoCommit} =3D 0;
>
> with this added to line 87 in the test script -> the tables are
> created and altered.
>
> rgds
> -daniel
>
>
> ----- Original Message -----
> From: "Peter K=F6ller" <pko...@me...>
> To: <dbi...@li...>
> Sent: Tuesday, July 31, 2001 10:58 AM
> Subject: Re: [Dbi-interbase-devel] How to close open transactions
> with AutoCommit =3D 1 in case of a failed prepare?
>
>
> Hello Daniel,
>
> now I have isolated a test script which can reproduce the problem I
> mentioned yesterday.
>
> The problem seems to be the following:
>
> - prepare fails because of non-existing table
> - disconnect (because the user can select another database)
> - connect again to the same database because the user wants the same on=
e
> - create tables
> - ERROR!!!
>
> If the disconnect and connect are left out, then no ERROR occurs.
>
> I attached a test script derived from my perl program.
>
> Is this a bug?
>
> Peter
>
> > -----Urspr=FCngliche Nachricht-----
> > Von: dbi...@li...
> > [mailto:dbi...@li...]Im Auftrag vo=
n
> > Daniel Ritz
> > Gesendet am: Montag, 30. Juli 2001 19:11
> > An: dbi...@li...
> > Betreff: Re: [Dbi-interbase-devel] How to close open transactions wit=
h
> > AutoCommit =3D 1 in case of a failed prepare?
> >
> > hello
> >
> > i've created two scripts doing what you described. the first is with
> > AutoCommit =3D 0, the second with AutoCommit =3D 1.
> >
> > first: create an empty db with ib console.
> > - when you first run the script you see the error message for the
> > failed prepare,
> >   the table is cerated and some data is inserted into it.
> > - second run: display content of the table created before.
> >
> > you can find the scripts at http://ritz.dnsart.com/interbase
> >
> > one question: what version of dbd-interbase are you using?
> >
> >
> > rgds
> > -daniel
> >
> > ps: if you problem still exists and you're using an up to date
> > version of the dbd
> > then it's probaly a bug (a missing commit or something). in this
> > case please send me
> > a code example that causes the error. i then try to fix it...
> >
> > ----- Original Message -----
> > From: "Peter K=F6ller" <pko...@me...>
> > To: <dbi...@li...>
> > Sent: Monday, July 30, 2001 11:32 AM
> > Subject: [Dbi-interbase-devel] How to close open transactions
> > with AutoCommit =3D 1 in case of a failed prepare?
> >
> >
> > > Hello!
> > >
> > > Is there a good portable solution for doing the following:
> > >
> > > 1. select something
> > > 2. if prepare failed (e. g. the database tables don't exist)
> > then goto 3.
> > >      else goto 4.
> > > 3. create database tables
> > > 4. execute statement
> > >
> > > I run into the problem, that create tables fails because of open
> > > transactions. Even disconnect fails in Step 3.
> > >
> > > "DBD::InterBase::db disconnect failed: Unsuccessful execution
> caused by
> > > system er
> > > ror that does not preclude successful execution of subsequent
> statements
> > > -cannot disconnect database with open transactions (1 active)"
> > >
> > > I use connect with AutoCommit set to true.
> > >
> > > Would the following be a good portable solution for
> > DBD::InterBase and other
> > > database drivers, too???
> > >
> > >   $db->{AutoCommit} =3D 0;
> > >   $st =3D $db->prepare($sql);
> > >   if (!$st) {
> > >     print "error ...";
> > >     $db->rollback();
> > >     return;
> > >   }
> > >   $st->execute();
> > >   if (!$st) {
> > >     print "error ...";
> > >     $db->rollback();
> > >     return;
> > >   }
> > >   ...
> > >   $db->commit();
> > >
> > > With other DBD drivers, e. g. ODBC I had no problem with
> > creating database
> > > tables after a prepare failed, even if AutoCommit was set to
> > true. There was
> > > no error message about open transactions. I worry about
> database drivers
> > > which don't support transactions.
> > >
> > > Any suggestions?
> > >
> > > Regards,
> > >
> > > Peter
> > >
> > >
> > > _______________________________________________
> > > Dbi-interbase-devel mailing list
> > > Dbi...@li...
> > > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> > >
> >
> >
> > _______________________________________________
> > Dbi-interbase-devel mailing list
> > Dbi...@li...
> > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> >
>
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[Daniel R. <dan...@gm...>]

hello peter

yep, it's a bug! a failed prepare does no rollback or commit in AutoCommit mode and disconnect doesn't too, but they
should...
i'll fix this asap. for now there's a little workaround:

set AutoCommit to 0 right before the disconnect: (this makes disconnect to do a commit)
    $_db->{AutoCommit} = 0;

with this added to line 87 in the test script -> the tables are created and altered.

rgds
-daniel


----- Original Message -----
From: "Peter Köller" <pko...@me...>
To: <dbi...@li...>
Sent: Tuesday, July 31, 2001 10:58 AM
Subject: Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?


Hello Daniel,

now I have isolated a test script which can reproduce the problem I
mentioned yesterday.

The problem seems to be the following:

- prepare fails because of non-existing table
- disconnect (because the user can select another database)
- connect again to the same database because the user wants the same one
- create tables
- ERROR!!!

If the disconnect and connect are left out, then no ERROR occurs.

I attached a test script derived from my perl program.

Is this a bug?

Peter

> -----Ursprüngliche Nachricht-----
> Von: dbi...@li...
> [mailto:dbi...@li...]Im Auftrag von
> Daniel Ritz
> Gesendet am: Montag, 30. Juli 2001 19:11
> An: dbi...@li...
> Betreff: Re: [Dbi-interbase-devel] How to close open transactions with
> AutoCommit = 1 in case of a failed prepare?
>
> hello
>
> i've created two scripts doing what you described. the first is with
> AutoCommit = 0, the second with AutoCommit = 1.
>
> first: create an empty db with ib console.
> - when you first run the script you see the error message for the
> failed prepare,
>   the table is cerated and some data is inserted into it.
> - second run: display content of the table created before.
>
> you can find the scripts at http://ritz.dnsart.com/interbase
>
> one question: what version of dbd-interbase are you using?
>
>
> rgds
> -daniel
>
> ps: if you problem still exists and you're using an up to date
> version of the dbd
> then it's probaly a bug (a missing commit or something). in this
> case please send me
> a code example that causes the error. i then try to fix it...
>
> ----- Original Message -----
> From: "Peter Köller" <pko...@me...>
> To: <dbi...@li...>
> Sent: Monday, July 30, 2001 11:32 AM
> Subject: [Dbi-interbase-devel] How to close open transactions
> with AutoCommit = 1 in case of a failed prepare?
>
>
> > Hello!
> >
> > Is there a good portable solution for doing the following:
> >
> > 1. select something
> > 2. if prepare failed (e. g. the database tables don't exist)
> then goto 3.
> >      else goto 4.
> > 3. create database tables
> > 4. execute statement
> >
> > I run into the problem, that create tables fails because of open
> > transactions. Even disconnect fails in Step 3.
> >
> > "DBD::InterBase::db disconnect failed: Unsuccessful execution caused by
> > system er
> > ror that does not preclude successful execution of subsequent statements
> > -cannot disconnect database with open transactions (1 active)"
> >
> > I use connect with AutoCommit set to true.
> >
> > Would the following be a good portable solution for
> DBD::InterBase and other
> > database drivers, too???
> >
> >   $db->{AutoCommit} = 0;
> >   $st = $db->prepare($sql);
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   $st->execute();
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   ...
> >   $db->commit();
> >
> > With other DBD drivers, e. g. ODBC I had no problem with
> creating database
> > tables after a prepare failed, even if AutoCommit was set to
> true. There was
> > no error message about open transactions. I worry about database drivers
> > which don't support transactions.
> >
> > Any suggestions?
> >
> > Regards,
> >
> > Peter
> >
> >
> > _______________________________________________
> > Dbi-interbase-devel mailing list
> > Dbi...@li...
> > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> >
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>