Обсуждение: Array data and the Perl DBI

I am having problems retrieving data stored in arrays using the Perl 
DBI.  I have noticed some others have had problems like this, and it looked 
like some fixes were forthcoming.  I know how to kludge a fix by specifying 
each element of the array separately in the query, but that is an 
unacceptable solution for me.  I want to know if there's any way to get 
this to work as advertised.  It fails on the fetch - segmentation 
fault.  Thanks, Kathy

#***********************************************Code***************************
#!/packages/bin/perl
use DBI;

my 
$dbh=DBI->connect("dbi:Pg:host=$host;dbname=$db;user=$user;password=$passwd");
my $statement="select channels[2] from test_array where ta_key='A'";
my $sth=$dbh->prepare($statement);
$sth->execute();
@answer=$sth->fetchrow_array;
$rc=$sth->finish;
$dbh->disconnect;


#******************************************Table definition 
*************************
#Table definition
CREATE TABLE test_array (    ta_key char(1) not null,    channels numeric(10,4) []
);

insert into test_array (ta_key,channels) values 
('A','{1.93,2.0,3.5,5.0,5.666}');

I couldn't find this in the archives...  In an ecpg program, given the
statement (taken from the manual):

EXEC SQL INSERT INTO sometable VALUES (:v1, ¹foo¹, :v2);

Is there a way to create the record where ':v1' is NULL - as with indicators
in a FETCH?  Or, do I have to generate different SQL statements in the C
program?

Wes

wespvp@syntegra.com wrote:
> I couldn't find this in the archives...  In an ecpg program, given the
> statement (taken from the manual):
> 
> EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);

Indicators works as expected.  e.g.:

EXEC SQL INSERT INTO sometable         VALUES (:v1 :v1_ind, 'foo', :v2 :v2_ind);

> 
> Is there a way to create the record where ':v1' is NULL - as with indicators
> in a FETCH?  Or, do I have to generate different SQL statements in the C
> program?
> 
> Wes
> 

-- 
Edmund Bacon <ebacon@onesystem.com>

On 7/16/04 11:11 AM, "Edmund Bacon" <ebacon@onesystem.com> wrote:

> Indicators works as expected.  e.g.:
> 
> EXEC SQL INSERT INTO sometable
>         VALUES (:v1 :v1_ind, 'foo', :v2 :v2_ind);

Hmm.  I tried that.  Just tried it again and it worked.  Must have had a
typo before.  Sorry.

What about if I have something like:
... VALUES (TO_TIMESTAMP(:expirationDate, 'YYYYMMDDHH24MISS'), 'foo', :v2)

How can I insert NULLS for expirationDate when there's a function in the
way?  Using
  ... VALUES (TO_TIMESTAMP(:expirationDate, 'YYYYMMDDHH24MISS') :ind1, ...

won't work.

Wes

wespvp@syntegra.com wrote:
> On 7/16/04 11:11 AM, "Edmund Bacon" <ebacon@onesystem.com> wrote:
> 
> 
>>Indicators works as expected.  e.g.:
>>
>>EXEC SQL INSERT INTO sometable
>>        VALUES (:v1 :v1_ind, 'foo', :v2 :v2_ind);
> 
> 
> Hmm.  I tried that.  Just tried it again and it worked.  Must have had a
> typo before.  Sorry.
> 
> What about if I have something like:
> 
>  ... VALUES (TO_TIMESTAMP(:expirationDate, 'YYYYMMDDHH24MISS'), 'foo', :v2)
> 
> How can I insert NULLS for expirationDate when there's a function in the
> way?  Using
> 
>    ... VALUES (TO_TIMESTAMP(:expirationDate, 'YYYYMMDDHH24MISS') :ind1, ...
> 
> won't work.

No, but ... VALUES (TO_TIMESTAMP(:expirationDate :ind1, 'YYYYMMDDHH24MISS'), ..
should

-- 
Edmund Bacon <ebacon@onesystem.com>

What is the scope of a BEGIN or an implicit transaction in ECPG?  Is it a
physical subroutine, or can it span functions containing EXEC SQL
statements?

For example, if I have:


void a (xxx)
{  EXEC SQL SELECT ... for UPDATE;  EXEC SQL UPDATE ...
}

void b (xxx)
{  EXEC SQL begin;
  a(xxxx);
  a(yyyy);
  EXEC SQL COMMIT;
}


Do the multiple calls to 'a' remain in the same transaction?

Wes  

wespvp@syntegra.com wrote:
> What is the scope of a BEGIN or an implicit transaction in ECPG?  Is
> it a physical subroutine, or can it span functions containing EXEC
> SQL statements?

It is independent of the control flow of your C program.  Only the order 
of the actually executed SQL statements matters.

> For example, if I have:
>
>
> void a (xxx)
> {
>    EXEC SQL SELECT ... for UPDATE;
>    EXEC SQL UPDATE ...
> }
>
> void b (xxx)
> {
>    EXEC SQL begin;
>
>    a(xxxx);
>
>    a(yyyy);
>
>    EXEC SQL COMMIT;
> }
>
>
> Do the multiple calls to 'a' remain in the same transaction?

Indeed

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/