[HACKERS] talking to postgresql from C/C++

2003-03-07 Thread terr 

---BeginMessage---
I've been trying to get information on a programming interface to the database.  Over 
the last while Dave Page and I have emailed each other.  Dave has suggested I join the 
hacker maillist.

Thus I am forwarding the communication I sent to Dave.  I hope this is approriated in 
this channel.  If not please advise where I should go.


I'll look forward to some clarification about what is or is not available by way of 
talking to the database from languages like C.

Thanks.

Terrell Larson


--3Pql8miugIZX0722
Content-Type: message/rfc822
Content-Disposition: inline

Date: Fri, 7 Mar 2003 04:18:45 -0700
From: terr
To: Dave Page [EMAIL PROTECTED]
Subject: Re: Fwd: ODBC docs
Message-ID: [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5i
In-Reply-To: [EMAIL PROTECTED]; from [EMAIL PROTECTED] on Fri, Mar 07, 2003 at 
08:49:00AM -


Over at M$, I see the following:

* SQQLAllocConnect Function
* SQLAllocEnv Function
* SQLAllocHandle Function
* SQLAllocStmt Function
* SQLBindCol Function
* SQLBindParameter Function
* SQLBrowseConnect Function
* SQLBulkOperations Function
* SQLCancel Function
* SQLCloseCursor Function
* SQLColAttribute Function
* SQLColAttributes Function
* SQLColumnPrivileges Function
* SQLColumns Function
* SQLConnect Function
* SQLCopyDesc Function
* SQLDataSources Function
* SQLDescribeCol Function
* SQLDescribeParam Function
* SQLDisconnect Function
* SQLDriverConnect Function
* SQLDrivers Function
* SQLEndTran Function
* SQLError Function
* SQLExecDirect Function
* SQLExecute Function
* SQLExtendedFetch Function
* SQLFetch Function
* SQLFetchScroll Function
* SQLForeignKeys Function
* SQLFreeConnect Function
* SQLFreeEnv Function
* SQLFreeHandle Function
* SQLFreeStmt Function
* SQLGetConnectAttr Function
* SQLGetConnectOption Function
* SQLGetCursorName Function
* SQLGetData Function
* SQLGetDescField Function
* SQLGetDescRec Function
* SQLGetDiagField Function
* SQLGetDiagRec Function
* SQLGetEnvAttr Function
* SQLGetFunctions Function
* SQLGetInfo Function
* SQLGetStmtAttr Function
* SQLGetStmtOption Function
* SQLGetTypeInfo Function
* SQLMoreResults Function
* SQLNativeSql Function
* SQLNumParams Function
* SQLNumResultCols Function
* SQLParamData Function
* SQLParamOptions Function
* SQLPrepare Function
* SQLPrimaryKeys Function
* SQLProcedureColumns Function
* SQLProcedures Function
* SQLPutData Function
* SQLRowCount Function
* SQLSetConnectAttr Function
* SQLSetConnectOption Function
* SQLSetCursorName Function
* SQLSetDescField Function
* SQLSetDescRec Function
* SQLSetEnvAttr Function
* SQLSetParam Function
* SQLSetPos Function
* SQLSetScrollOptions Function
* SQLSetStmtAttr Function
* SQLSetStmtOption Function
* SQLSpecialColumns Function
* SQLStatistics Function
* SQLTablePrivileges Function
* SQLTables Function
* SQLTransact Function


This looks like the API.  Thanks.  Now - IMHO we should have these docs on our 
website instead of having to rely on M$.  

It this is truely not currently available I may be able to start flanging something 
up.  I suspect that there may be bits and peices around... perhaps the whole thing.

Also - I really want to find out how some of these functions are implemented.  The 
problem I'm looking at is that: 

(1) if I use ecpg then I get over 1000 parameters stuffed into some of the calls.  
This is just plain NUTZ.  Aside from it being virtually insane to try to pass this 
many parameters to a function call, it makes absolutly no sense to stack over 1000 
addresses, call the interface, find out the row is not present, tear down the 
addresses, build the row (about 100 fields), stack over 1000 addresses bak up, call 
the interface to post the row, and tear the stack back down only to do this all over 
again for the next row.

Passing this many addresses is so NUTZ that when I popped into the #C developers group 
on Freenode and also the #debian channels and asked if anyone knew the maximum number 
of parameters one could pass into a function call - that they started to abuse me!  
haha.  Some must have thought I was a 1st year uni student without much common sense.  
What can I say.  IMHO 1000+ parameters is an embarrasment.

(2) if I use libpg then I apparently have to map each and every column for each row 
over and over, column by column for each row in a manner that is very similar to (1).  
None of the columns change between calls to the database and none of the variables 
move around. (in this case the buffers are statically mapped - by design)  I should be 
able to build up a table of the mapping of MY C

Re: [HACKERS] talking to postgresql from C/C++

2003-03-07 Thread Christoph Haller 

Have you seen
libpq - C Library
Functions Associated with the COPY Command
This is best way to INSERT large amounts of data.

Regards, Christoph



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] talking to postgresql from C/C++

2003-03-07 Thread mlw 
I read your post, and I am struck by a few things, I am not sure I will 
answer all your points, but maybe a discussion is in order.

I use PostgreSQL with C++ all the time. I actually have a SQL class that 
abstracts libpq and ODBC, so I'm pretty much past a lot of the how I 
want to use it stuff.

Your first question, how many parameters can you pass in a function? The 
answer is as many as  you have room on your stack. The C and C++ stack 
frames are simple push based. There are no hard set limits.

Your desired structure based API is all well and good, but it is largely 
impractical. One would need a SQL aware preprocessor that parses the SQL 
query, interprets the results, and creates a conversion routine for the 
structure you wish to use, and warns you when it can't work. If you have 
to specify the conversion routine for each structure, I bet you would 
end up doing more work for your easier API.

Yes, SQL database interfacing is tedious. There is some plain and simple 
drudgery involved with communicating with one. Whether it is ODBC, 
libpq, Oracle OCI, it doesn't matter, you just got to do some of the 
interface coding yourself. It is yucky but it is the nature of the beast.

Your concern about going from ASCII to binary and back are valid in a 
sense, but not too critical. SQL databases are not fast. The time 
spent going from ASCII to binary at the server for an insert is fairly 
trivial when compared to the transactional overhead of the insert. On a 
query, one can use a binary cursor.

If you want to load a lot of data into the database in one function 
call, look at PostgreSQL's COPY command.  It is almost trivial to 
write a routine that sets up a copy, reads from a file, translates 
accordingly, and writes it to the database.

Aside from the aesthetic objections, do you have any real can't do 
issues with PostgreSQL, or SQL in general?



[EMAIL PROTECTED] wrote:



I've been trying to get information on a programming interface to the database.  Over the last while Dave Page and I have emailed each other.  Dave has suggested I join the hacker maillist.

Thus I am forwarding the communication I sent to Dave.  I hope this is approriated in this channel.  If not please advise where I should go.

I'll look forward to some clarification about what is or is not available by way of talking to the database from languages like C.

Thanks.

Terrell Larson

--3Pql8miugIZX0722
Content-Type: message/rfc822
Content-Disposition: inline
Date: Fri, 7 Mar 2003 04:18:45 -0700
From: terr
To: Dave Page [EMAIL PROTECTED]
Subject: Re: Fwd: ODBC docs
Message-ID: [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5i
In-Reply-To: [EMAIL PROTECTED]; from [EMAIL PROTECTED] on Fri, Mar 07, 2003 at 
08:49:00AM -
Over at M$, I see the following:

   * SQQLAllocConnect Function
   * SQLAllocEnv Function
   * SQLAllocHandle Function
   * SQLAllocStmt Function
   * SQLBindCol Function
   * SQLBindParameter Function
   * SQLBrowseConnect Function
   * SQLBulkOperations Function
   * SQLCancel Function
   * SQLCloseCursor Function
   * SQLColAttribute Function
   * SQLColAttributes Function
   * SQLColumnPrivileges Function
   * SQLColumns Function
   * SQLConnect Function
   * SQLCopyDesc Function
   * SQLDataSources Function
   * SQLDescribeCol Function
   * SQLDescribeParam Function
   * SQLDisconnect Function
   * SQLDriverConnect Function
   * SQLDrivers Function
   * SQLEndTran Function
   * SQLError Function
   * SQLExecDirect Function
   * SQLExecute Function
   * SQLExtendedFetch Function
   * SQLFetch Function
   * SQLFetchScroll Function
   * SQLForeignKeys Function
   * SQLFreeConnect Function
   * SQLFreeEnv Function
   * SQLFreeHandle Function
   * SQLFreeStmt Function
   * SQLGetConnectAttr Function
   * SQLGetConnectOption Function
   * SQLGetCursorName Function
   * SQLGetData Function
   * SQLGetDescField Function
   * SQLGetDescRec Function
   * SQLGetDiagField Function
   * SQLGetDiagRec Function
   * SQLGetEnvAttr Function
   * SQLGetFunctions Function
   * SQLGetInfo Function
   * SQLGetStmtAttr Function
   * SQLGetStmtOption Function
   * SQLGetTypeInfo Function
   * SQLMoreResults Function
   * SQLNativeSql Function
   * SQLNumParams Function
   * SQLNumResultCols Function
   * SQLParamData Function
   * SQLParamOptions Function
   * SQLPrepare Function
   * SQLPrimaryKeys Function
   * SQLProcedureColumns Function
   * SQLProcedures Function
   * SQLPutData Function
   * SQLRowCount Function
   * SQLSetConnectAttr Function
   * SQLSetConnectOption Function
   * SQLSetCursorName Function
   * SQLSetDescField Function
   * SQLSetDescRec Function
   * SQLSetEnvAttr Function
   * SQLSetParam Function
   * SQLSetPos Function
   * SQLSetScrollOptions Function
   * SQLSetStmtAttr Function
   * SQLSetStmtOption Function
   *

Re: [HACKERS] talking to postgresql from C/C++

2003-03-07 Thread Merlin Moncure 
mlw [mailto:[EMAIL PROTECTED] wrote:
 I use PostgreSQL with C++ all the time. I actually have a SQL class
that
 abstracts libpq and ODBC, so I'm pretty much past a lot of the how I
 want to use it stuff.

What about libpq++? I have not used the thing, but if he absolutely
insists on using C++ in his database interface that's at least worth
checking out.  Same for embedded C.

I often use the zeos toolkit for postgres, which works with C++ Builder,
Delphi, and Kylix.  If you use those tools I can vouch that they are a
good way to write apps with postgres.  The zeos connection toolkit is an
order of magnitude faster than pgodbc.


For tight oo integration with the database, I would take either Java or
(if you hail from *nix and can deal with mono) C#.

Merlin

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] talking to postgresql from C/C++

2003-03-07 Thread Jeroen T. Vermeulen 
On Fri, Mar 07, 2003 at 12:14:30PM -0500, Merlin Moncure wrote:
 
 What about libpq++? I have not used the thing, but if he absolutely
 insists on using C++ in his database interface that's at least worth
 checking out.  Same for embedded C.
 
And of course there's libpqxx.  I haven't heard from anyone who's tried
it on Borland C++ Builder yet, but I'd love to.  It is known to work on
several other compilers, so why not?  It's a lot more useful than the
old libpq++ IMHO.

See http://pqxx.tk/


Jeroen


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html