in response to Jeff his first reply
sorry about not sending this immediately to th list
------- Forwarded message follows -------
From: Frederik A.A. de Jonge <[EMAIL PROTECTED]>
To: "Jeff Urlwin" <[EMAIL PROTECTED]>
Subject: RE: insert LONG / BLOB in MS ACCESS == Brackets
around names?
Date sent: Thu, 17 Oct 2002 17:52:00 +0100
Hi Jeff
Thanks for the reply
After having sent my email (with some trepidation as I felt
that I was asking something that seems to be not a new problem)
I actually did find another of your answers where you refer to this file.
Steps taken:
A) I downloaded the latest version from (CPAN) DBD-ODBC-0.43.tar.gz
(I do not manage to install this, I assume because I dont have a C
compiler on my system
but this is another story)
B) I have read thru the longbin.pl file and used the parts that
- create a table
- write the longbinary data
THIS WORKED as claimed by you
#first success
$dbh->do('Create table longtest (id integer, LongStr longbinary)');
$sth = $dbh->prepare('insert into longtest (LongStr) values (?,?)');
$sth->bind_param(1, 10);
$sth->bind_param(2,$longstr, SQL_LONGVARBINARY});
$sth->execute();
once the table has been created like this I can
continue to add long_binary in the indicated way without problem
(I did actually use the same long str for the "binary", but I suppose
that this
does not realy make a difference)
#alternatively this also works
$dbh->do('Create table longtest (id counter, LongStr longtext)');
$sth = $dbh->prepare('insert into longtest (LongStr) values (?)');
$sth->bind_param(1,undef, SQL_LONGVARCHAR);
$sth->execute($longstr);
once the table has been created like this I can
continue to add long_strings in the indicated way without problem
BY THE WAY: these examples work without having to put the [fieldnames] in
brackets!
BUT IT ONLY seems to work for tables diretly created from perl::DBI => see
below
C) having seen that this worked, I realized that the table that I had been trying
to write to before had been created manually in MS Access
(Tables->new->DesignView and entering the fld names and definitions by hand)
Although as my first email says, MS Access reports via the table_info
that the type is -1 = SQL_LONGVARCHAR, it just will not accept the long data;
The table_info (see below) for the DateSR table (made manually and to which
I cannot write long data and for which I need to use brackets on the field names)
and the tables dtest or longtest (created under DBI control) and to which I can write
either long_c
har or long_binary date are exactly the same with reference to the type etc for the
long_fields.
(NB once made with the DBI, I can also toggle the data def in Access manually as
needed to change f
rom MEMO to OLE without affecting the ability to write the long data; but this is not
possible for
a table made first manually in ACCESS)
I would presume that this means that the fault (as always ?) is with uncle Bill Gates
and the MS Access product ??
However, in an attempt to prove this I made a second table manually............
to which I can write long_data without any problem ??? without needing brackets around
the field_na
me. So here everything seems OK.
On further refelction, the only difference between the first "DateSR" table and the
later tables is
that "DateSR" was possibly made in MS Access 97 before applying a massive update for
MSAccess in t
he form of ServicePack 1 and 2 as provided by MS; unfortunatly I cannot track exactly
which
occurred first.
So now I (we) are back to the starting point with as only conclusion: one freak table
does not allo
w long_data writing but (most) others do ??? and as second conclusion: DBI/DBD::ODBC
does not seem
at fault.
Sorry for all the bother and I would like to thank you (all) for all the great effort
being put int
o the DBI / DBD::ODBC stuff, it makes all the difference
Fred
ANNEXE
OUTPUT from table_info per row
Table Info Row 1 = E:/MS_Office_docs_2002/access_db/Fred_Private/t_p_dbi
DateSR
TABLE
NUM_OF_FIELDS = 5
FldName Type Prec Scale Null?
ID 4 10 0 no
DATE 11 19 0 yes
STRNUM 12 50 0 yes
RSTNUM 12 50 0 yes
LONGSTR -1 2147483647 0 yes
Table Info Row 2 = E:/MS_Office_docs_2002/access_db/Fred_Private/t_p_dbi
dtest
TABLE
NUM_OF_FIELDS = 11
FldName Type Prec Scale Null?
MY_SINGLE 7 7 0 yes
MY_DOUBLE 8 15 0 yes
MY_SHORT 5 5 0 yes
MY_LONG 4 10 0 yes
MY_BIT -7 1 0 no
MY_BYTE -6 3 0 yes
MY_DATETIME 11 19 0 yes
MY_TEXT 12 255 0 yes
MY_LONGTEXT -1 2147483647 0 yes
MY_LONGBINARY -4 1073741823 0 yes
MY_COUNTER 4 10 0 no
Table Info Row 3 = E:/MS_Office_docs_2002/access_db/Fred_Private/t_p_dbi
longtest
TABLE
NUM_OF_FIELDS = 2
FldName Type Prec Scale Null?
ID 4 10 0 no
LONGSTR -1 2147483647 0 yes
Table Info Row 4 = E:/MS_Office_docs_2002/access_db/Fred_Private/t_p_dbi
tAuTit
TABLE
NUM_OF_FIELDS = 3
FldName Type Prec Scale Null?
ID 4 10 0 no
AUTHOR 12 50 0 yes
TITLE 12 50 0 yes
From: "Jeff Urlwin" <[EMAIL PROTECTED]>
To: "Frederik A.A. de Jonge" <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
Subject: RE: insert LONG / BLOB in MS ACCESS == Brackets around names?
Date sent: Thu, 17 Oct 2002 09:42:32 -0400
> Frederik,
>
> In DBD::ODBC distribution under the mytest subdirectory, there is a
> longbin.pl. Check that out for a sample which works on MS-Access *and*
> inserts a long binary file (I used a .TIF file for my tests). The test
> basically takes an MD5 hash of the input file, inserts it, retrieves it from
> the db and then checks the MD5 hash against the database retrieved.
>
> Jeff
>
------- End of forwarded message -------
