[RESOLVED] altering data type of db table

[yssirhc]

I originally posted this in the sql forum, but after trying some queries within Access, I'm now thinking the real solution will need to be with perl code.

I created a database within MS Access - no SQL code - and set a field to "Memo". When I go to my web app that inserts some form data into the database, if I type too much into that one field on the form, I get "Invalid Precision Value".

I've been looking online and found this:
This error message is received when trying to store more than 255 characters in a MEMO field. The problem is (to my knowledge) an error in the MS Access ODBC driver. The problem is fixed by setting the data_type of the column to SQL_LONGVARCHAR in add_columns. SQL_LONGVARCHAR is a constant in the DBI module.

Only I have no idea how to set the data type of the column to longvarchar. and I'm not quite sure where I would put this additional code either, whether it would need to be in all my scripts or what.

[winracer]

what does you code look like?

[yssirhc]

well, this is part of the main page:

### connect to database #####
$dbhandle = DBI->connect("DBI:ODBC:bugzdead") or &errorPage("Couldn't connect to database: " . DBI->errstr());

#add code here that would alter the table?

### get list of bugs #####
$sql = "SELECT B.ID, B.Problem, B.Product, B.Version, B.Type, B.Severity, B.SubmittedBy, S.DateSubmitted, S.Status, R.AverageRating FROM Bugs AS B, Status AS S, Rating AS R WHERE B.ID=S.BugID AND S.BugID=R.BugID ORDER BY ID";
$query = $dbhandle->prepare($sql) or &errorPage("Couldn't prepare Select Bug List query: " . $dbhandle->errstr());
$query->execute() or &errorPage("Couldn't execute Select Bug List query: " . $query->errstr());

### put list of bugs into html table #####
while (@row = $query->fetchrow_array())
{
print "<tr>";
foreach $element(@row)
{
$makeLink = $row[0]; #ID - first element

if ($element eq $makeLink)
{
print "<td align=\"center\">"; #escape the quotes!
print "<a href=\"viewBug.cgi?ID=$element&user=$user\">"; #escape the quotes!
print $element;
print "</a>";
print "</td>";
}
else
{
print "<td>";
print $element;
print "</td>";
}
}
print "</tr>";
}
print "</table>";
print "</div>";

$query->finish();

### disconnect from database #####
$dbhandle->disconnect();


The form is written in perl too, but that's just because I needed to get the query string. The rest of it is just a print block of html.

And then the cgi that does the inserting into the database:

### connect to database #####
$dbhandle = DBI->connect("DBI:ODBC:bugzdead") or die "Couldn't connect to database: " . DBI->errstr();

#add code here that would alter the table?

if ($bugID eq '0') #new bug - insert into database (using submitBug form)
{
$sql = "INSERT into .....
$query = .....
$query->execute();
$query->finish();
}
else #bug already exists - update bug info (from editBug form)
{
$sql = "UPDATE .....
$query = .....
$query->execute();
$query->finish();
}

### disconnect from database #####
$dbhandle->disconnect();

[winracer]

I am reviewing it myself...
Did you read
http://dtemplatelib.sourceforge.net/LongStrings.htm

http://www.codeproject.com/KB/database/odbcrecordset.aspx?fid=580&df=90&mpp=25&sort=Position&select=1211154&tid=2899900

and I found this onehttp://www.mail-archive.com/dbi-users@perl.org/msg12880.html


hope this helps

[yssirhc]

THANK YOU!!! that last link with the e-mail messages fixed it for me. :)

for anyone who might me interested in the future, the code I used was:

$sql = "INSERT into Bugs (Problem, Product, Version, Type, Severity, Description, Steps, WorkAround, SubmittedBy) VALUES (?,?,?,?,?,?,?,?,?)";
$query = $dbhandle->prepare($sqlBugs) or die "Couldn't prepare Insert Bug query: " . $dbhandle->errstr();

#need to change the default type of memos in MS Access so that they can hold over 255 characters like they're supposed to
$query->bind_param(6, $formHash{description}, SQL_LONGVARCHAR);
$query->bind_param(7, $formHash{steps}, SQL_LONGVARCHAR);
$query->bind_param(8, $formHash{workAround}, SQL_LONGVARCHAR);

$query->execute($formHash{problem}, $formHash{product}, $formHash{version}, $formHash{type}, $formHash{severity}, $formHash{description}, $formHash{steps}, $formHash{workAround}, $formHash{user}) or die "Couldn't execute Insert Bug query: " . $query->errstr();
$query->finish();


It goes right after the prepare statement in the script that does the inserting/updating of the database. The first argument being which parameter in the query it is, starting with 1.

[winracer]

great glad the link helped and it is always great to see that you posted the code for someone looking for the same problem in the future.