From: Richard Huxton <dev@archonet.com>
To: Vadivel Subramaniam <vadivel.subramaniam@flextronicssoftware.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: how to store more than 3 MB of character data in Postgres
Date: Mon, 06 Jun 2005 12:46:25 +0100
Message-ID: <42A43791.7030306@archonet.com> (raw)
In-Reply-To: <OF070A806E.9AC2F48A-ON65257018.003D07AB-65257018.003DF74B@flextronicssoftware.com>
References: <OF070A806E.9AC2F48A-ON65257018.003D07AB-65257018.003DF74B@flextronicssoftware.com>
Vadivel Subramaniam wrote:
>
> I assume, it could not be a problem with ODBC. I am able to store 2.5 MB of
> data into Oracle using the same ODBC APIs.
Well, it certainly isn't to do with PG itself:
$ cat bigtest.pl
#!/usr/bin/perl
print "CREATE TABLE foo (a int, b varchar);\n";
print "INSERT INTO foo VALUES (1, '".('a' x 5000000)."');\n";
$ perl bigtest.pl | psql -Urichardh richardh
CREATE TABLE
INSERT 3634376 1
$ psql -Urichardh richardh
richardh=> SELECT a,length(b) FROM foo;
a | length
---+---------
1 | 5000000
(1 row)
> Only difference is that Oracle supports CLOB datatype, that is mapped to
> LONG VARCHAR in ODBC.
> The input parameter during insert is bound using the below ODBC API
> retStatus = SQLBindParameter(dStmtHandle, 1, SQL_PARAM_INPUT,
> SQL_C_CHAR, SQL_LONGVARCHAR, dLen, 1, pData, 0, &dLen);
>
> For PostGres also the same API is used. Here varchar/text at the Postgres
> schema is mapped to LONGVARCHAR at ODBC.
> We can not go for bytea etc, since the data is in character format.
>
> I tried using "text" also at the postgres schema, still the impact is same
> (only stores around 64K).
> Could you please provide some example, how the large character data ( more
> than 1 MB ) can be inserted to text/varchar (in PostGres) using ODBC?
Hmm - looking back at your original email, I see you're using the
UnixODBC driver. Might be worth trying the PG version:
http://gborg.postgresql.org/project/psqlodbc/genpage.php?downloads
First step though, should be to check whether there are any
configuration settings that apply to longvarchar, because it sounds like
the odbc driver should be mapping to that.
I can't help you with finding those settings, since I have only ever
used the "official" PG driver on Windows.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, vadivel.subramaniam@flextronicssoftware.com
Subject: Re: how to store more than 3 MB of character data in Postgres
In-Reply-To: <42A43791.7030306@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox