Re: db or file access?
On Tue, 12 Apr 2005 10:46:06 +0200, Christian Merz wrote: Hi Folks Well, I thought I'd better put my keyboard where my mouth is, so I wrote an article on this: http://savage.net.au/Ron/html/images-in-files.html -- Cheers Ron Savage, [EMAIL PROTECTED] on 13/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
The schema drawing tool Dico has moved out of beta
Hi Folks http://savage.net.au/Ron/html/drawing-tools.html -- Cheers Ron Savage, [EMAIL PROTECTED] on 13/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
DBI, MS Access, inserting/updating a Access Date/Time value
Hi,
Does anyone have any idea what is up with Microsoft Access and its
ridiculous Date/Time formatting options? It doesn't seem to matter what
format I attempt to insert with. The directory and db file permissions
are all Full Control. I'm using the # around the Date/Time format as
required. The database has nothing as the Format in Design view on the
General tab. The insert below has never worked once. On the up side if I
run an insert that has no mention of the Date/Time field then it will
just insert the record using the =Now() function as the default value
but that doesn't help much considering I would want to update the field.
There are no question marks in any of the field names as suggested on
many pages dicussing this error below. Thanks for any help! I'm using
MS Access, Perl, DBI DBD::ODBC win32 modules, IIS on XP.
Here is the error:
...
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access
Driver]COUNT field incorrect (SQL-07002)(DBD: st_execute/SQLExecute
err=-1) at C:\Inetpub\Scripts\somedir\dsntest.pl line 18.
...
Here is the code:
...
#!C:\perl\bin\perl.exe
#
#print Content-type: text/html\n\n;
use DBI;
# 2005-04-12 20:48:31
# 4/5/2004 5:3:45 PM
# 4/12/2005 10:28:14 PM
# d m h:m:s AM/PM
$dt = #4/5/2004 5:3:45 PM#;
$sql = INSERT INTO tblTest (TimeStamp) VALUES ($dt);
my $dbh = DBI-connect(dbi:ODBC:dsntest, , , { PrintError = 0,
RaiseError = 1 })
|| die database connection can't connect to database
$DBI::errstr;
my $sth = $dbh-prepare($sql) || die prepare statement- $DBI::errstr;
$sth-execute() || die execute statement- $DBI::errstr;
$sth-finish;
...
Re: db or file access?
On Wed, 13 Apr 2005 20:06:44 +1000, Ron Savage wrote: Well, I thought I'd better put my keyboard where my mouth is, so I wrote an article on this: http://savage.net.au/Ron/html/images-in-files.html It would be nice, IMHO, if your site had some sort of blog-like structure, so that people could comment on the site itself. BTW I tend to agree with you, without actually being convinced of its technological superiority. BTW one can use mod_rewrite to nicen up the URL for images. But files inside databases tend to blow up the actual database files. There's a lot of air in databases. Wasted disk space. -- Bart.
Re: DBI, MS Access, inserting/updating a Access Date/Time value
On Tue, 12 Apr 2005 23:34:09 -0700, Robb wrote: Does anyone have any idea what is up with Microsoft Access and its ridiculous Date/Time formatting options? I am guessing that MS-Access formats dates according to your settings in the Regional Settings control panel. -- Bart.
Re: db or file access?
From: Bart Lateur | It would be nice, IMHO, if your site had some sort of blog-like | structure, so that people could comment on the site itself. Good Idea, | | BTW I tend to agree with you, without actually being convinced of its | technological superiority. BTW one can use mod_rewrite to nicen up the | URL for images. | | But files inside databases tend to blow up the actual database files. | There's a lot of air in databases. Wasted disk space. I seem to be getting confused here about databases and file structures. Yes, a database holds data, when it comes to images, does the database not hold a pointer to the image? At the end of the day, the database itself is held on the hard drive the same as any other file in sectors as '1's and '0's so ultimately come from the same source. A database structure may seem to be held in one place, but is still probably fragmented all over the place. I am open to correction, my flame retardant suit is on and ready :) Gav... -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005
Re: DBI, MS Access, inserting/updating a Access Date/Time value
On Tue, 2005-04-12 at 23:34 -0700, Robb wrote:
Does anyone have any idea what is up with Microsoft Access and its
ridiculous Date/Time formatting options?
I asked that question daily in a previous life working with access, and
that that was with ms tools! I never got a sane answer.
I don't now if newer versions are any better but access 97 and 2000 both
had a nasty habit of sometimes ignoring any regional settings and just
using American dates.
As Bart suggested checking the regional settings for the box is worth
it. I would also check how the dsn is setup.
$dt = #4/5/2004 5:3:45 PM#;
$sql = INSERT INTO tblTest (TimeStamp) VALUES ($dt);
my $dbh = DBI-connect(dbi:ODBC:dsntest, , , { PrintError = 0,
RaiseError = 1 })
|| die database connection can't connect to database
$DBI::errstr;
my $sth = $dbh-prepare($sql) || die prepare statement- $DBI::errstr;
$sth-execute() || die execute statement- $DBI::errstr;
$sth-finish;
...
Do you get the same error if you use a bind value instead of putting $dt
directly in the string? ie
$sql = INSERT INTO tblTest (TimeStamp) VALUES (?);
$sth-execute($dt) || die execute statement- $DBI::errstr;
Also have you tried ODBC date formats as your going through a DSN?
mark
--
This email (and any attachments) is intended solely for the individual(s) to
whom addressed.
It may contain confidential and/or legally privileged information.
Any statement or opinions therein are not necessarily those of ITN unless
specifically stated.
Any unauthorised use, disclosure or copying is prohibited.
If you have received this email in error, please notify the sender and delete
it from your system.
Security and reliability of the e-mail and attachments are not guaranteed.
You must take full responsibility for virus checking.
Independent Television News Limited,
Registered No. 548648 England,
VAT Reg. No: GB 756 2995 81,
200 Gray's Inn Road, London WC1X 8XZ,
Telephone: 020 7833 3000.
Re: db or file access?
On Apr 13, 2005, at 4:25 AM, Gav wrote: From: Bart Lateur | BTW I tend to agree with you, without actually being convinced of its | technological superiority. BTW one can use mod_rewrite to nicen up the | URL for images. | | But files inside databases tend to blow up the actual database files. | There's a lot of air in databases. Wasted disk space. I seem to be getting confused here about databases and file structures. Yes, a database holds data, when it comes to images, does the database not hold a pointer to the image? At the end of the day, the database itself is held on the hard drive the same as any other file in sectors as '1's and '0's so ultimately come from the same source. A database structure may seem to be held in one place, but is still probably fragmented all over the place. I am open to correction, my flame retardant suit is on and ready :) Well as usual it depends. :-) There are many types of filesystems that can behave very differently depending on how they're used. For example if you don't care about journaling, ext2 is simple and fast. If you want journaling and loads of small files, you're better off with ReiserFS, etc... The different filesystems use different algorithms for determining where the files reside, they use different block sizes, they have different defragmentation techniques, etc... This all impacts how well each filesystem performs for your application. Add on top of that the different buffering and caching schemes of the OSes, and you can't really tell what's better unless you try out a number of combinations. If you're going to use a database as a filesystem where you actually store the binaries (as opposed to a filesystem 'pointer'), you have to look at the same issues: access algorithms, caching, block sizes, etc... Some databases can only be installed on top of 'cooked' filesystems, i.e. the database data resides on top of the filesystem and can only use the filesystem block size. Other databases can have 'raw' partitions for their data, which allows them to fully control how the partition is managed, up to the block size and caching mechanism. So if you have larger files, you can use 16KB page sizes for data pages, significantly increasing the throughput as opposed to the default 4KB. In my experience, you just have to try a number of scenarios to see which one is best for your application. I personally prefer keeping the binary data out of databases, where I have better control over the low-level access to that data, and I can better manage the backup and maintenance. H
fetchrow_array results in segmentation fault on user-defined Postgresql data type
Hello,
I am using PostgreSQL 7.4.7, Perl 3:5.8.5-9, perl-DBI 1.40-5 and PGUUID
(http://gborg.postgresql.org/project/pguuid)
PGUUID provides a user-defined UUID data type for Postgresql.
Perl script
my $select = SELECT objectid FROM directory WHERE internalpath=?LIMIT
1;
my $select_handle = $dbh-prepare($select);
my $sresult = $select_handle-execute($dirname);
if( $result != 1 )
{
die error: select failed on table=directory. dir=$dirname\n;
return;
}
if( $select_handle-rows == 0 )
{
die error: dirname \$dirname\ not found in the directory
table.\n;
return;
}
my $rc = $select_handle-rows;
print row count=$rc\n;
@DBI::data = $select_handle-fetchrow_array();
The call fetchrow_array() results in a segmentation fault.
DBI level 9 trace shows...
dbd_st_execute: statement =
SELECT objectid FROM directory WHERE internalpath= '/home/test' LIMIT 1
- execute= 1 at ./loaddb_rel line 258 via ./loaddb_rel line 106
after select.
rowsDISPATCH (DBI::st=HASH(0x8be658c) rc1/1 @1 g0 ima4
pid#16546) at ./loaddb_rel line 265 via ./loaddb_rel line 106
- rows for DBD::Pg::st (DBI::st=HASH(0x8be658c)~0x8be6610)
thr#8912008
dbd_st_rows
- rows= 1 at ./loaddb_rel line 265 via ./loaddb_rel line 106
rowsDISPATCH (DBI::st=HASH(0x8be658c) rc1/1 @1 g0 ima4
pid#16546) at ./loaddb_rel line 270 via ./loaddb_rel line 106
- rows for DBD::Pg::st (DBI::st=HASH(0x8be658c)~0x8be6610)
thr#8912008
dbd_st_rows
- rows= 1 at ./loaddb_rel line 270 via ./loaddb_rel line 106
row count=1
fetchrow_array DISPATCH (DBI::st=HASH(0x8be658c) rc1/1 @1 g1 ima0
pid#16546) at ./loaddb_rel line 274 via ./loaddb_rel line 106
- fetchrow_array for DBD::Pg::st (DBI::st=HASH
(0x8be658c)~0x8be6610) thr#8912008
dbd_st_fetch
dbih_setup_fbav for 1 fields = 0x8bbfad4
Segmentation fault
I can use PSQL to retrieve rows with the user-defined UUID data type
correctly so the PGUUID functions appear to be working. The postgresql
log shows only an unexpected client EOF.
When single stepping with the perl debugger, the script hangs @
dbih_setup_fbav for 1 fields = ...
Is there any known issues with DBI + Postgresql user-defined types?
Any other way(s) to debug this problem?
Thanks
DBD-Oracle
hello, Is your site down, I am trying to get the above module. Can you please redirect me to a place were i can get it, i am trying to use the ppm utility because compiling in windows is a headache. thanks
Re: DBI, MS Access, inserting/updating a Access Date/Time value
Robb wrote: Hi, Does anyone have any idea what is up with Microsoft Access and its ridiculous Date/Time formatting options? :) In VB, I use the format: #dd-mmm- HH:mm:ss# Your example: $dt = #4/5/2004 5:3:45 PM#; I would write as: $dt = #04-MAY-2004 17:03:34#; You have to pad your numbers. You can't just use 5:3:45 and expect Access ( or others ) to know what you want. Converting the month to a 3-character string version forces Access to recognise the 1st part as the day and the last part as the year. If you don't do this, it's selection of day/month/year positions appears to be random. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
