By accident, I solved my problem with inserting data into a memo field
that is created on the fly. I wanted to pass on the information of how I
did it and ask what the difference is between what I was doing in my
code as opposed to what I discovered.
The original code is posted below. I perused the list archives, finding
mention in several postings of the bind_param reading as
"DBI::SQL_LONGVARCHAR" instead of my code, which read
"SQL_LONGVARCHAR". If I had created a database template (fields
created but no data entered into the fields) through Access,
SQL_LONGVARCHAR works in the bind statements in my perl script.
On the other hand, if I dynamically created my table and LONCHAR
fields through my perl script, SQL_LONGVARCHAR doesn't work, but
requires the DBI::SQL_LONGVARCHAR. Can anyone explain why?
Newbie as I am, my only conclusion is that, since bind_param
statements are database sensitive and not cross-platform, the
SQL_LONGVARCHAR is not compatible with the dynamic table
creation method. Issuing DBI::SQL_LONGVARCHAR implements the
cross-platform nature of the DBI module, serving as a translator for
cross-platform compatibility. How close to the truth am I? And, if I am
far far away, please explain.
Brad Smith
On 4 Jan 2003 at 20:45, Brad Smith wrote:
> I've gotten though the process of creating my table dynamically. Now,
> I am having problems with inserting data into the Memo fields, even
> when I am using bind-param. Can anyone figure out what my problem is
> from my code below? Again, I am creating and adding data to an Access
> database, using ODBC. Thanks in advance.
>
> <code below>
>
> foreach $i (1..$in{'no_questions'}) {
> push @questions, ",question_"."$i"." LONGCHAR,".
> "question_"."$i"."_type
> VARCHAR(50),".
> "question_"."$i"."_answers
> LONGCHAR,".
>
> "question_"."$i"."_values LONGCHAR,".
> "question_"."$i"."_private
> BIT";
> }
>
> my $dbh = DBI->connect('dbi:ODBC:armsreach_reporting') || die
> DBI::errstr;
> my $sth = $dbh->do("CREATE TABLE $in{'report_name'} (sid
> COUNTER,
>
> report_name VARCHAR(64),
>
> creator
> VARCHAR(255),
>
> creation_date DATE,
>
> abstract LONGCHAR,
>
> instructions LONGCHAR,
>
> no_questions INTEGER
>
> @questions
>
> )
> ");
> $dbh->disconnect;
>
> foreach $i (1..$in{'no_questions'}) {
> push @results, ",question_"."$i"."
> LONGCHAR," .
> "question_"."$i"."_type
> VARCHAR(50)," .
> "answer_" ."$i"."
> LONGCHAR," .
> "question_"."$i"."_values
> LONGCHAR," .
> "question_"."$i"."_private BIT";
> }
>
> my $dbh = DBI->connect('dbi:ODBC:armsreach_reporting_results') || die
> DBI::errstr; my $sth = $dbh->do("CREATE TABLE $in{'report_name'} (sid
>
> COUNTER,
>
> report_name VARCHAR(64),
>
> creator
> VARCHAR(255),
>
> creation_date DATE,
>
> no_questions INTEGER
>
> @results
>
> )
> ");
> $dbh->disconnect;
>
> my $dbh = DBI->connect("dbi:ODBC:armsreach_reporting") || die
> DBI::errstr;
> my $sth = $dbh->prepare("INSERT INTO $in{'report_name'} (
>
> report_name, creator, creation_date, abstract, instructions,
> no_questions)
> VALUES (?,?,?,?,?,?)
> ") || die DBI::errstr;
>
> $sth->bind_param(1,$in{'report_name'},SQL_LONGVARCHAR);
> $sth->bind_param(2,$in{'creator'},SQL_LONGVARCHAR);
> $sth->bind_param(3,$in{'creation_date'},SQL_LONGVARCHAR);
> $sth->bind_param(4,$in{'abstract'},SQL_LONGVARCHAR);
> $sth->bind_param(5,$in{'instructions'},SQL_LONGVARCHAR);
> $sth->bind_param(6,$in{'no_questions'},SQL_LONGVARCHAR);
>
> $sth->execute();
> $dbh->disconnect();
>
> <code above>
>
> Brad Smith
>
