Message-ID: <42A43791.7030306@archonet.com>
Date: Mon, 06 Jun 2005 12:46:25 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Vadivel Subramaniam <vadivel.subramaniam@flextronicssoftware.com>,
	pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: how to store more than 3 MB of character data in Postgres
References: 
 <OF070A806E.9AC2F48A-ON65257018.003D07AB-65257018.003DF74B@flextronicssoftware.com>
In-Reply-To: 
 <OF070A806E.9AC2F48A-ON65257018.003D07AB-65257018.003DF74B@flextronicssoftware.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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