[SQL] how to store more than 3 MB of character data in Postgres Varchar field
Hi, We have a requirement wherein we have to store around 3 MB of data in Postgres database. We had gone through the postgres website http://www.postgresql.org/docs/7.4/interactive/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE The above link says "varchar" can store upto 1 GB of data. But when i try to insert using ODBC API's the varchar field takes only 65484 characters.But i could insert more characters using INSERT SQL command. Even in this case when we fetch the data it reads only 65540 characters. Remaining data is not read from the varchar field. We have tried in 2 ways 1. table is created without any limit specified for varchar. create table details (name varchar, data varchar); 2. create table utilntmlscripts (name character varying, data character varying(10485770)); ERROR: length for type 'varchar' cannot exceed 10485760 It's not allowing more than 10 MB of size during table creation. Is there anything needs to be configured to make the varchar field to support upto 1 GB. Even if it does not support 1 GB, can we store & retrieve atleast 10 MB of character data in Varchar field? Please send the details of how this can be acheived. Postgres Version 7.4.2 Accessing using UnixODBC driver from C++ application. thanks, Vadivel. *** FSS-Private *** "DISCLAIMER: This message is proprietary to Flextronics Software Systems Limited (FSS) and is intended solely for the use of the individual to whom it is addressed. It may contain privileged or confidential information and should not be circulated or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. FSS accepts no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus." ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] large object support in PostGres 7.4
Hi, We have API's in /usr/local/pgsql/include/libpq-fe.h which support large objects in PostGres extern int lo_open(PGconn *conn, Oid lobjId, int mode); extern int lo_close(PGconn *conn, int fd); extern int lo_read(PGconn *conn, int fd, char *buf, size_t len); extern int lo_write(PGconn *conn, int fd, char *buf, size_t len); extern int lo_lseek(PGconn *conn, int fd, int offset, int whence); extern Oid lo_creat(PGconn *conn, int mode); extern int lo_tell(PGconn *conn, int fd); extern int lo_unlink(PGconn *conn, Oid lobjId); extern Oid lo_import(PGconn *conn, const char *filename); extern int lo_export(PGconn *conn, Oid lobjId, const char *filename); My doubt is, do these API's operate on character data? i.e., My table schema is like this table (name varchar, script varchar). I have to store a large data(in character form) in the script column (upto 3 MB). As of my understanding the above mentioned API's work on the OID field. i.e,. table(name varchar, script oid). Is that correct? Please clarify this if anyone has already used this large object APIs and specify if this has any limitation on the size? thanks, -Vadivel *** FSS-Private *** "DISCLAIMER: This message is proprietary to Flextronics Software Systems Limited (FSS) and is intended solely for the use of the individual to whom it is addressed. It may contain privileged or confidential information and should not be circulated or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. FSS accepts no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus." ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQLCloseCursor() or SQLFreeStmt does not free memory allcated to read CLOB field in Oracle.
Hi, This is query realated to ODBC and CLOB field in Oracle. I feel some of you might have faced similar issue. Any hint would help me. I am using a C++ application that connects to Oracle Database using EasySoft ODBC driver. I face a strange problem when freeing the memory after i fetch the CLOB data from the Oracle table. The piece of Code is below, char *pClobBuffer = new char[10485670]; // 10 MB // Prepare the SQL statement strcpy(pQuery, "SELECT clobdata FROM clobtable") /* Execute the SQL statement. Check for errors. */ SQLExecDirect(stmtHandle,(SQLCHAR*)pQuery,SQL_NTS); SQLFetch(st mtHandle); SQLGetData(stmtHandle, 1, SQL_C_CHAR, clobBuffer, 10485670,&dError); SQLCloseCursor(stmtHandle); delete []pClobBuffer; The statement and connection handles are closed properly. Still i am seeing the memory usage is exactly increasing by 10 MB(size of the buffer we allocated to read the CLOB) for each query. This is not a memory leak, it's only the memory usage which is increasing for the process for every query involving this CLOB field. I tried changing the CLOB to Varchar in the database table, the above piece of Code works without increase in memory usage. NOTE: We observed there is some special handling required to free the memory buffer allocated to read the CLOB field. In DB2 site they are saying SQLFreeStmt does not free the CLOB fields, FREE Alocator statement must be used. Please suggest me if any special handling is required for ORACLE to free the CLOB data. Our Env: C++, Solaris, ODBC, ORACLE, EasySoft driver thanks, Vadivel. *** FSS-Private *** "DISCLAIMER: This message is proprietary to Flextronics Software Systems Limited (FSS) and is intended solely for the use of the individual to whom it is addressed. It may contain privileged or confidential information and should not be circulated or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. FSS accepts no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus." ---(end of broadcast)--- TIP 8: explain analyze is your f
