DBD::ODBC Precision Value errors 1

[Rieekan]

I'm trying to use DBI and the ODBC module to insert records into a MS Access database and I'm running into problems updating records into a column type of Memo when the data to insert is large (over 2000 characters).

Has anyone run into this problem using ODBC and if so, how did you get around it? Any help is very appreciated.

- Rieekan

 

[MikeLacey]

From the DBI documentation

Many databases support ``blob'' (binary large objects), ``long'', or similar datatypes (memo fields, in Access) for holding very long strings or large amounts of binary data in a single field. Some databases support variable length long values over 2,000,000,000 bytes in length.

Since values of that size can't usually be held in memory, and because databases can't usually know in advance the length of the longest long that will be returned from a SELECT statement (unlike other data types), some special handling is required.

In this situation, the value of the $h->{LongReadLen} attribute is used to determine how much buffer space to allocate when fetching such fields. The $h->{LongTruncOk} attribute is used to determine how to behave if a fetched value can't fit into the buffer.

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.

 

[Rieekan]

Mike,

Thanks ofr the reply, but I've read the documentation and that is referring to Select statements, I'm trying to Insert the information into the database, not retreive it back.

Have you had any experience inserting records into blob fields?

- Rieekan

 

[MikeLacey]

Ah ha!

Quite right.

Not very well documented this, I picked up a hint from the DBD::ODBC documentation to do with bind_param

$sth->bind_param(1, $str, DBI::SQL_LONGVARCHAR);

With this, you're telling DBI that this particular placeholder (1) is bound to the variable $str and that the database field is of type SQL_LONGVARCHAR (which equates to the Access type MEMO)

And no, I've not had to do this yet - but the day is young 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.

 

[Rieekan]

Believe it or not, I've tried that, and there seems to be a bug in the logic behind it because when I use it, I then get the error of "Missing Semi-colon ( at the end of the SQL statment." I just can't seem to win with this one.

Thanks for all of your help with this.

- Rieekan

 

[MikeLacey]

Rieekan,

Post your code mate. (A bit of it ) 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.

 

[Rieekan]

A bit would be better than all of it. It's a slightly large script.

Here's the part that matters.

if ($special_instructions)
{
	$sql = qq|insert into CASHOUT_INFO (SPECIAL_INSTRUCTIONS) values (?) where CASHOUT_SEQ_ID = $seq_id|;

	$st = $db->prepare($sql) or print &quot;Cannot prepare Cashout Special Instructions insert statement: &quot;. $db->errstr .&quot;<br><br>\n&quot;;
	
	$st->bind_param(1, $special_instructions, DBI::SQL_LONGVARCHAR);

	$st->execute() or print &quot;Cannot execute Cashout Special Instructions insert statement: &quot;. $db->errstr .&quot;<br><br>\n&quot;;
}

Let me know if you find anything that's insanely obvious with this code.

Thanks!

- Rieekan

 

[MikeLacey]

<grin>

I looked at that for several minutes before I saw it.

You don't need (can't have) a WHERE clause in an INSERT statement.

Assuming that CASHOUT_SEQ_ID is a sequence (or something) and so doesn't need to be specified, I think your code should be:

$sql = qq|insert into CASHOUT_INFO (SPECIAL_INSTRUCTIONS) values (?)|;

If you need to specify a value for CASHOUT_SEQ_ID it should look something like this:

$sql = qq|insert into CASHOUT_INFO
(SPECIAL_INSTRUCTIONS, CASHOUT_SEQ_ID)
values (?, $seq_id)|;

This is the kind of error *I* get when I copy and paste code.. Mike

&quot;Experience is the comb that Nature gives us, after we are bald.&quot;

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

 

[Rieekan]

Well, that just goes to show what wasn't on my mind this week. Too much talk about turkey around here I guess. I feel like one now.

Thanks Mike, I'm going to go crawl in a hole now and laugh silently about this.

- Rieekan

 

[MikeLacey]

*grin*

Happy thxgiving Rieekan, assuming you're from the US Mike

&quot;Experience is the comb that Nature gives us, after we are bald.&quot;

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