Module DBI - MS-Access and MEMO-Field 
Author Message
 Module DBI - MS-Access and MEMO-Field

Hi Folks,

I'm usin DBI:ODBC to work with MS-ACCESS-Database. Creation of a table
with SQL works proper. Also a MEMO-typed field is created.
But if I try to INSERT via SQL a new record (including MEMO-data) an
error message occurs like the following:

[Microsoft][ODBC Microsoft Access Driver]Ungltiger Genauigkeitswert
(SQL-
S1104)(DBD: _rebind_ph/SQLBindParameter err=-1)

and nothing happens - the data are not be written into the database.

With  WIN32:ODBC all works proper but I want to use DBI::ODBC because
of portability.
Is there someone can help me?

Thanks
Thomas



Thu, 01 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field
On Mon, 14 Feb 2000 21:31:23 GMT, "Douglas Gardiner"

Quote:

>What does your INSERT statement look like and how is it presented.  I found
>that I had to use different syntax with INSERT on MS-Access.  Also found
>that my error checking routine would form duplicate entries.
>> I'm usin DBI:ODBC to work with MS-ACCESS-Database. Creation of a table
>> with SQL works proper. Also a MEMO-typed field is created.
>> But if I try to INSERT via SQL a new record (including MEMO-data) an
>> error message occurs like the following:

>> [Microsoft][ODBC Microsoft Access Driver]Ungltiger Genauigkeitswert
>> (SQL-
>> S1104)(DBD: _rebind_ph/SQLBindParameter err=-1)

>> and nothing happens - the data are not be written into the database.

Hi Douglas,
fine that there is somebody trying to help me. That is my
INSERT-Statement:

$sqlstr=$thdb->prepare(q{INSERT INTO postings VALUES
(?,?,?,?,?,?,?)});

$sqlstr->execute($newthnumber,$posnumber,$ebne,$posauthor,$posdatum,$posemail,$postext)||
print "Fehler Postings",$sqlstr->errstr;

And $postext contains my MEMO-Fiel. If there less then 255 Characters
- all is fine but with more than 255 characters, the known
error-message occurs and the record will not INSERT.

Thanks
Thomas



Sat, 03 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field

Well the SQL statement is missing some things.  I'm not sure about sending information to the
DB in the order of the table (it can be done, but I haven't tried it), but I do know you will
need to tell those value where to be put.   Syntax:

INSERT INTO table_name (Column names here seperated by commas) VALUES (actual values here by
same order seperated by commas).

Hope that helps.

Quote:

> On Mon, 14 Feb 2000 21:31:23 GMT, "Douglas Gardiner"

> >What does your INSERT statement look like and how is it presented.  I found
> >that I had to use different syntax with INSERT on MS-Access.  Also found
> >that my error checking routine would form duplicate entries.

> >> I'm usin DBI:ODBC to work with MS-ACCESS-Database. Creation of a table
> >> with SQL works proper. Also a MEMO-typed field is created.
> >> But if I try to INSERT via SQL a new record (including MEMO-data) an
> >> error message occurs like the following:

> >> [Microsoft][ODBC Microsoft Access Driver]Ungltiger Genauigkeitswert
> >> (SQL-
> >> S1104)(DBD: _rebind_ph/SQLBindParameter err=-1)

> >> and nothing happens - the data are not be written into the database.

> Hi Douglas,
> fine that there is somebody trying to help me. That is my
> INSERT-Statement:

> $sqlstr=$thdb->prepare(q{INSERT INTO postings VALUES
> (?,?,?,?,?,?,?)});

> $sqlstr->execute($newthnumber,$posnumber,$ebne,$posauthor,$posdatum,$posemail,$postext)||
> print "Fehler Postings",$sqlstr->errstr;

> And $postext contains my MEMO-Fiel. If there less then 255 Characters
> - all is fine but with more than 255 characters, the known
> error-message occurs and the record will not INSERT.

> Thanks
> Thomas



Sat, 03 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field

Well the SQL statement is missing some things.  I'm not sure about sending information to the
DB in the order of the table (it can be done, but I haven't tried it), but I do know you will
need to tell those value where to be put.   Syntax:

INSERT INTO table_name (Column names here seperated by commas) VALUES (actual values here by
same order seperated by commas).

Hope that helps.

Quote:

> On Mon, 14 Feb 2000 21:31:23 GMT, "Douglas Gardiner"

> >What does your INSERT statement look like and how is it presented.  I found
> >that I had to use different syntax with INSERT on MS-Access.  Also found
> >that my error checking routine would form duplicate entries.

> >> I'm usin DBI:ODBC to work with MS-ACCESS-Database. Creation of a table
> >> with SQL works proper. Also a MEMO-typed field is created.
> >> But if I try to INSERT via SQL a new record (including MEMO-data) an
> >> error message occurs like the following:

> >> [Microsoft][ODBC Microsoft Access Driver]Ungltiger Genauigkeitswert
> >> (SQL-
> >> S1104)(DBD: _rebind_ph/SQLBindParameter err=-1)

> >> and nothing happens - the data are not be written into the database.

> Hi Douglas,
> fine that there is somebody trying to help me. That is my
> INSERT-Statement:

> $sqlstr=$thdb->prepare(q{INSERT INTO postings VALUES
> (?,?,?,?,?,?,?)});

> $sqlstr->execute($newthnumber,$posnumber,$ebne,$posauthor,$posdatum,$posemail,$postext)||
> print "Fehler Postings",$sqlstr->errstr;

> And $postext contains my MEMO-Fiel. If there less then 255 Characters
> - all is fine but with more than 255 characters, the known
> error-message occurs and the record will not INSERT.

> Thanks
> Thomas



Sat, 03 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field
[rearranged to be in conversational order]

Quote:


> > $sqlstr=$thdb->prepare(q{INSERT INTO postings VALUES
> > (?,?,?,?,?,?,?)});

> Well the SQL statement is missing some things.  I'm not sure about
> sending information to the DB in the order of the table (it can be
> done, but I haven't tried it), but I do know you will need to tell
> those value where to be put.   Syntax:

> INSERT INTO table_name (Column names here seperated by commas) VALUES
> (actual values here by same order seperated by commas).

Nope, you are wrong, the original poster's insert statement is perfectly
fine.  The column names are optional and in DBI the question marks are
place-holders for the values.

Thomas, the solution might be in the execute statement.  Try putting in
the column types in the execute, e.g.:

$sqlstr->execute('foo'=>CHAR,1=>INT,'bar'=>BLOB...) or whatever.

--
Jeff



Sat, 03 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field
Well then that the way to insert using the predefined order for a SQL
statement (I haven't tried predefined order) however, my statement still
works and has been working for some time.  If you look up the syntax for a
SQL statement, you will find it to be true.  Otherwise, you have to know the
order of your columns

http://w3.one.net/~jhoffman/sqltut.htm#Adding Data

See the above link for further info.

Otherwise the error showing at 255, would point to a problem of the
container size (column names for example have a max length of 255
characters, but the memo field max is 64k) with Access.

Quote:
> [rearranged to be in conversational order]



> > > $sqlstr=$thdb->prepare(q{INSERT INTO postings VALUES
> > > (?,?,?,?,?,?,?)});

> > Well the SQL statement is missing some things.  I'm not sure about
> > sending information to the DB in the order of the table (it can be
> > done, but I haven't tried it), but I do know you will need to tell
> > those value where to be put.   Syntax:

> > INSERT INTO table_name (Column names here seperated by commas) VALUES
> > (actual values here by same order seperated by commas).

> Nope, you are wrong, the original poster's insert statement is perfectly
> fine.  The column names are optional and in DBI the question marks are
> place-holders for the values.

> Thomas, the solution might be in the execute statement.  Try putting in
> the column types in the execute, e.g.:

> $sqlstr->execute('foo'=>CHAR,1=>INT,'bar'=>BLOB...) or whatever.

> --
> Jeff



Sun, 04 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field

Quote:

> Well then that the way to insert using the predefined order for a SQL
> statement (I haven't tried predefined order)

So if you haven't tried it, why are you commenting on someone else that
is using it?  That is called the blind leading the blind.

Quote:
> however, my statement still
> works and has been working for some time.

Sure, your statement works.  But you told the original poster that his
syntax was "missing some things" which is flat out incorrect, he was not
missing anything.

Quote:
> http://w3.one.net/~jhoffman/sqltut.htm#Adding Data

> See the above link for further info.

Hmm, even that brief tutorial shows that the original poster's insert
syntax was valid so I am even more mystified how you could claim he was
missing something.

--
Jeff



Sun, 04 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field
Well he can either define the order as posted earlier or he has to get it
RIGHT OTHERWISE YOU WILL GET AN ERROR FOR PUTTING CONENT LONGER THAN THE
CONTAINER ...IDIOT.

Maybe you should try looking at the requirements for MS Access before you
post your ludicrious statements, especially since you have no proof that
anything you do actually works.  Most of the character types have a content
length of 255 characters which is what he's running into, and it is safer to
call out specifics than to just haphazardly throw data in there and hope you
have it right.
Do your homework before to place beligerent post here.  Otherwise your
aforementioned post can lead to holes, and be exploited.

What did, you take Purl Gurl's place as the designated troll?


Quote:

> > Well then that the way to insert using the predefined order for a SQL
> > statement (I haven't tried predefined order)

> So if you haven't tried it, why are you commenting on someone else that
> is using it?  That is called the blind leading the blind.

> > however, my statement still
> > works and has been working for some time.

> Sure, your statement works.  But you told the original poster that his
> syntax was "missing some things" which is flat out incorrect, he was not
> missing anything.

> > http://w3.one.net/~jhoffman/sqltut.htm#Adding Data

> > See the above link for further info.

> Hmm, even that brief tutorial shows that the original poster's insert
> syntax was valid so I am even more mystified how you could claim he was
> missing something.

> --
> Jeff



Sun, 04 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field

Quote:
>Well the SQL statement is missing some things.  I'm not sure about sending information to the
>DB in the order of the table (it can be done, but I haven't tried it), but I do know you will
>need to tell those value where to be put.   Syntax:

>INSERT INTO table_name (Column names here seperated by commas) VALUES (actual values here by
>same order seperated by commas).

>Hope that helps.

Sorry , but it doesn't !!!


Sun, 04 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field

Quote:
>Thomas, the solution might be in the execute statement.  Try putting in
>the column types in the execute, e.g.:

>$sqlstr->execute('foo'=>CHAR,1=>INT,'bar'=>BLOB...) or whatever.

This doesn't work -

INSERT-Statement:

$sqlstr=$thdb->prepare("INSERT INTO postings VALUES (?)");
$sqlstr->execute($s=>MEMO)|| print "Fehler Postings",$sqlstr->errstr;

$s contains a string with more than 255 characters (with a number of
characters less then 256 it works fine).

The statement above occurs the following error-message:

execute called with 2 bind variables when 1 are needed at
D:\Programme\UltraEdit\test.pl line 8.



Sun, 04 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field
When you have that entry less than 256 chars, what does the output look like in the database.  Is
the data in the right location?  If everything is in the right location, for the table description
what about looking in design view of the table for each column to see the char type. Apart from a
possible problem with data length (highly unlikely), the only thing I can think of is to see if
your parameters from the earlier statement which set up the table translated fully.  I know from
the help files that the text filed in Access store up 255, and the only thing I can think of is
that the datatype didn't get set up properly.
Quote:

> >Well the SQL statement is missing some things.  I'm not sure about sending information to the
> >DB in the order of the table (it can be done, but I haven't tried it), but I do know you will
> >need to tell those value where to be put.   Syntax:

> >INSERT INTO table_name (Column names here seperated by commas) VALUES (actual values here by
> >same order seperated by commas).

> >Hope that helps.

> Sorry , but it doesn't !!!



Sun, 04 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field

Quote:

> $sqlstr=$thdb->prepare("INSERT INTO postings VALUES (?)");
> $sqlstr->execute($s=>MEMO)|| print "Fehler Postings",$sqlstr->errstr;

Sorry, I left out a step, try:

$sqlstr=$thdb->prepare("INSERT INTO postings VALUES (?)");
$sqlstr->bind_param(1, $s, {type => 'MEMO'} );
$sqlstr->execute;

--
Jeff



Sun, 04 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field

Quote:
>Sorry, I left out a step, try:

>$sqlstr=$thdb->prepare("INSERT INTO postings VALUES (?)");
>$sqlstr->bind_param(1, $s, {type => 'MEMO'} );
>$sqlstr->execute;

>--
>Jeff

Running this code:

#!/usr/local/bin/perl -w
use DBI;  

$thdb = DBI->connect('DBI:ODBC:Forum') || die "Kann Datenbank:
".$DBI->errstr;

$s="Die hfhdnnf vbhtzghbf ghnb gtnfhhgnznfbhgnungntungnh njgnvhfn
nthgnf rnnfhnn WKJDB!FKU KDB jdfdj jsbdfjsdf1b kjsdbkjsdf1b
ksjdbfjsdbf sajh7kdfbjdswf ksjbdfjksd kj7ashdb1fkjdfb asdkh7ijf1bsjdkf
k?j3fjwef n lkkwdmnkck jebjeje  jebejejefje jebfje3fbjbef
jebfje3fbejf3b jebfjeb3fbejnj jebfjebfje  je3fbjefje";

#variable $s contains more than 255 characters !!!

$sqlstr=$thdb->prepare("INSERT INTO postings VALUES (?)");
$sqlstr->bind_param(1, $s, {type => 'MEMO'} );
$sqlstr->execute($s)|| print "Fehler Postings",$sqlstr->errstr;

results in this error-message:

DBD::ODBC::st bind_param failed: [Microsoft][ODBC Microsoft Access
Driver]Ungltiger Genauigkeitswert  (SQL-S1104)(DBD:
_rebind_ph/SQLBindParameter err=-1) at D:\Programme\UltraEdit\test.pl
line 8.
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access
Driver]Ungltiger Genauigkeitswert  (SQL-S1104)(DBD:
_rebind_ph/SQLBindParameter err=-1) at D:\Programme\UltraEdit\test.pl
line 9.
Fehler Postings[Microsoft][ODBC Microsoft Access Driver]Ungltiger
Genauigkeitswert  (SQL-S1104)(DBD: _rebind_ph/SQLBindParameter err=-1)



Mon, 05 Aug 2002 03:00:00 GMT  
 Module DBI - MS-Access and MEMO-Field
On Wed, 16 Feb 2000 16:03:13 -0600, Douglas Gardiner

Quote:

>When you have that entry less than 256 chars, what does the output look like in the database.  Is
>the data in the right location?

Well, the data are in the MEMO-Field

Quote:
> If everything is in the right location, for the table description
>what about looking in design view of the table for each column to see the char type.

The char-type of this certain field is MEMO , that means that 64k
Characters should be contained

Quote:
> Apart from a
>possible problem with data length (highly unlikely), the only thing I can think of is to see if
>your parameters from the earlier statement which set up the table translated fully.  I know from
>the help files that the text filed in Access store up 255, and the only thing I can think of is
>that the datatype didn't get set up properly.

The whole sourcecode , reduced to the most important topics follow:

--- CREATEing table -------

use DBI;    # ODBC-Modul einbinden
$fdb = DBI->connect('DBI:ODBC:Forum') || die "Kann Datenbank:
".$fdb->errstr;

my $sqlstr;

$sqlstr=$fdb->prepare("CREATE TABLE postings (postext MEMO)");
$sqlstr->execute()|| print "Kann Tabelle POSTINGS nicht
erstellen".$sqlstr->errstr;

$sqlstr->finish;
$fdb->disconnect;

------ Now source for INSERT a RECORD --------
use DBI;  

$thdb = DBI->connect('DBI:ODBC:Forum') || die "Kann Datenbank:
".$DBI->errstr;

$s="Die hfhdnnf vbhtzghbf ghnb gtnfhhgnznfbhgnungntungnh njgnvhfn
nthgnf rnnfhnn WKJDB!FKU KDB jdfdj jsbdfjsdf1b kjsdbkjsdf1b
ksjdbfjsdbf sajh7kdfbjdswf ksjbdfjksd kj7ashdb1fkjdfb asdkh7ijf1bsjdkf
k?j3fjwef n lkkwdmnkck jebjeje  jebejejefje jebfje3fbjbef
jebfje3fbejf3b jebfjeb3fbejnj jebfjebfje  je3fbjefje";

#the variable $s contains more than 255 Characters !!!

$sqlstr=$thdb->prepare("INSERT INTO postings VALUES (?)");
$sqlstr->execute($s)|| print "Fehler Postings",$sqlstr->errstr;

Thats all. Craetion of table is right - INSERT less than 256
Characters in MEMO-Fiel ok - more than 255 Charcter : the described
error occurs.



Mon, 05 Aug 2002 03:00:00 GMT  
 
 [ 14 post ] 

 Relevant Pages 

1. DBD::ODBC DBI and MS Access memo field

2. DBI to MSAccess DB - truncates memo fields

3. DBI and MSAccess - memo fields truncated

4. Perl DBI won't return Access Memo fields

5. DBD::ODBC DBI and Microsoft Access memo field

6. DBD/DBI ODBC and Microsoft Access 97 memo field

7. DBI select with memo fields

8. ODBC and Access memo fields

9. DBD::ODBC truncates Memo type Access field

10. Hints on using memo field from Access 97

11. Inserting to a hyperlinked field in MS Access using ActiveState

12. DBD::ODBC MS-Access "MEMO" fields?

 

 
Powered by phpBB® Forum Software