DBI assistance 1

[pcorchary]

(it shouldn't matter, but this is in relation to DBD:CSV)

I'm still very new to Perl, CGI and DBI - so don't assume that I know anything

Using DBI methods, is there a way to retrieve the column NAMES from a table? Further when I use bind_columns, is there a way to assign this with an array e.g.

@FIELD_NAMES=qw(name date city);

### open $dhb handle, etc.

my $sth->bind_columns(undef, @FIELD_NAMES)
or die "Bind column: ". $dbh->errstr();

$sth->excute() or die ..... etc.

can this be done? Better yet, can I SELECT the actual column names, parse them into an array in place of the =qw() above ...

Thanks in advance ...

 

[amarkiew]

I don't use the CSV in the DBD module but these should still apply.

You can get the column names of a statement using the statement attribute NAME, NAME_uc or NAME_lc. EX:
my $dbh = (... however you connect...);
my $stmt = qq(select * from table);
my $csrStmt = $dbh->prepare($stmt);
my @cols = @{$csrStmt->{NAME_lc});

@cols will now contain all the fields on "table" in lower case.
The NAME_lc attribute is an array reference containing all the columns in the select statement, in this case all the fields on "table".

Q2:
I don't know of a way to bind an array using bind_columns.
It requires a list of references, but you can loop through an array and using bind_col. You can use the NAME_lc array from above and bind them into a hash. Ex:

my (%rec, $indx);
foreach my $col (@{$csrStmt->{NAME_lc}}) {
$csrStmt->bind_col(++$indx, \$rec{$col});
}

This will loop through all the fields in you select statement and bind each result to a hash key of the column name in the hash %rec.
Accessing the values is now just $rec{fld1}, $rec{fld}....
An added benefit is code maintainence. When the SQL changes, so does your cursor without maintaining specific bind variables.

Hope this helps.
Andrew

 

[nexus1001]

to display the column names use the following attribute from the cursor you created to do your select statement:

$sth->{NAME}

As for binds I cant really help, sorry

 

[pcorchary]

Why, OH, Why does the code below, wrap "" around the data? When I run this, this "03-10-2002,01:20:20" string is inserted ... WHY?

(DBD:CSV)


sub insert {
my @values = qw(03-10-2002,02:20:20);

my $sql = ("INSERT INTO $loc_table (DATE,TIME) VALUES (?,?)"

#Prepare SQL
my $sth = $dbh->prepare($sql)
or die "Cannot prepare:" . $dbh->errsrt();

#execute SQL
$sth->execute(@values) or die "Cannot Execute: " . $sth->errstr();

#finish interaction with DB
$sth->finish();
}

 

[amarkiew]

my @values = qw(03-10-2002,02:20:20);
should have a space instead of a comma.

qw() does not consider a comma a delimiter.

 

[MikeLacey]

I've not had much luck with bind values and DBD::CSV - I just prepare and execute the SQL each time...... Mike
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.