Re: talking to postgresql from C/C++

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?

terr(at)terralogic(dot)net 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 <dpage(at)vale-housing(dot)co(dot)uk>
>Subject: Re: Fwd: ODBC docs
>Message-ID: <20030307041844(dot)I24536(at)saturn(dot)terralogic(dot)net>
>References: <03AF4E498C591348A42FC93DEA9661B8259D29(at)mail(dot)vale-housing(dot)co(dot)uk>
>Mime-Version: 1.0
>Content-Type: text/plain; charset=us-ascii
>Content-Disposition: inline
>User-Agent: Mutt/1.2.5i
>In-Reply-To: <03AF4E498C591348A42FC93DEA9661B8259D29(at)mail(dot)vale-housing(dot)co(dot)uk>; from dpage(at)vale-housing(dot)co(dot)uk on Fri, Mar 07, 2003 at 08:49:00AM -0000
>
>
>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 VARIABLES into the columns in the table ONCE at the beginning of a run and then use it over and over row by row as I post things into the database.
>
>(3) The update functions I have encountered so far are basically a SQL INSERT statement. This means that my code must go through the buffer containing the row to be inserted and build up a HUGE string with ugly codes in it - converting binary values into ACSII and eventually having done this HUGE amount of work, pass the string to the interface so that it can parse the string which is a HUGE amount of work and eventually get the data back into a mapped binary buffer from which the database manager can arrange to store it in the database on disk.
>
>To put it bluntly this sort of interface is INSANE.
>
>What I need is the following structure passed into the database manager:
>
>struct record_mapping {
> void *address_of_field; // a pointer to the data
> enum field_type; // as defined: pgint, pgfloat, pgvarchar, pgblob...
> char *field_name; // internal name in the database
> etc
> etc
> etc
>}
>
>and then I can call an initialization routine passing either this structure or the information in it to the database manager in say a cursor.
>
>If this interface does NOT exist then it should be created.
>
>If I were to do this then I would create the following functions:
>
>pg_cursor_address = pg_Create_Cursor ( db_handle, max_number_variables );
>
>if the address is NULL then the function failed. the error code should be in db_handle. If we can't put it into the handle, then we pass &success, an int.
>
>int success = pg_Define_Column_Entry ( pg_cursor_address, &myvar, pg_field_type, "column-name", .... );
>
>If it is successful, sucess = 0, if fails, sucess has the error code.
>
>pg_cursor_address = pg_Destroy_Cursor ( pg_cursor_address );
>
>This routine returns a NULL thus detroying the pointer. Meanwhile any malloc()'s are freed and the cursor is torn down.
>
>success = pg_Exec_Query ( pg_cursor_address, "insert");
>
>an example of how to insert a row. success = 0 => ok. non-zero carries an extensive list of reasons. The text of the reason should be retrivable with a sister function.
>
>success = pg_Exec_Query ( pg_cursor_address, "select where blah" );
>
>an example of how to fetch a row. Only the first selected row is copied into the transfer buffer. Again success = 0 implies you got the data.
>
>success = pg_Exec_Query ( pg_cursor_address, "next-row" );
>
>When the last row has been returned, a success is set to a non-zero.
>
>success = pg_Exec_Query ( pg_cursor_address, "replace-row" );
>
>In this case, the application fetched the row, updated it and wants the replacement posted into the database.
>
>------------
>
>I'll stop now. I just fired this off the top of my head. I'm looking to find an interface that will sort of work like this, and work relatively efficiently. Perhaps this is the ODBC interface and perhaps it is the libpg interface... but if this is in libpg I sure could not find it.
>
>IF this interface does NOT exist then I could perhaps write it. Now, I am not familiar with the source tree or what internal functions and structures might be available to support this. Clearly I could write an interface like this and hook it up to libpg - but the libpg from what I can tell does NOT support a cursor and needs to build up a call column by column and this is what we're trying to avoid.
>
>Given the excellent quality of PostgreSQL I just *HAVE* to believe a suitable interface already exists and I have just not been able to find it.
>
>Again, if it does not exist, with a very small amount of guidence I figure I can probably make it happen. If this is the direction we need to go, then I would like the opportunity to knock heads with some people who are already familiar with what is out there so as to define exactly HOW this interface should look so that it comes out looking clean and well thought out and not some botch created by an amature.
>
>Thanks for your help.
>
>
>
>
>
>
>On Fri, Mar 07, 2003 at 08:49:00AM -0000, Dave Page wrote:
>
>
>>Why not take a look at msdn.microsoft.com? There is some very useful
>>documentation in the Data Access SDK on ODBC - and after all, Microsoft
>>did invent it!
>>
>>Regards, Dave.
>>
>>
>>
>>>-----Original Message-----
>>>From: terr(at)terralogic(dot)net [mailto:terr(at)terralogic(dot)net]
>>>Sent: 07 March 2003 09:43
>>>To: Dave Page
>>>Subject: Re: Fwd: ODBC docs
>>>
>>>
>>>I've been through that documentation. THere is some docs on
>>>using the odbc interfce from JODBC and from Python, but there
>>>is nothing on how to use it from C or C++.
>>>
>>>In my case, I was planning on doing the database interface in
>>>C, bit C++
>>>
>>>
>>>On Fri, Mar 07, 2003 at 08:03:23AM -0000, Dave Page wrote:
>>>
>>>
>>>>Should be OK now. The server had a RAID controller failure.
>>>>
>>>>Regards, Dave.
>>>>
>>>>
>>>>
>>>>>-----Original Message-----
>>>>>From: terr(at)terralogic(dot)net [mailto:terr(at)terralogic(dot)net]
>>>>>Sent: 07 March 2003 00:45
>>>>>To: Dave Page
>>>>>Subject: Re: Fwd: ODBC docs
>>>>>
>>>>>
>>>>>This link takes me to here:
>>>>>http://gborg.postgresql.org/project/psqlodbc/projdisplay.php
>>>>>
>>>>>there is a link there which says "PostgreSQL Docs" the link
>>>>>does not work and I get this:
>>>>>
>>>>>Warning: pg_exec(): supplied argument is not a valid
>>>>>PostgreSQL link resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 184
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 187
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 190
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 191
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 192
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 193
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 194
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 195
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 196
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 197
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 198
>>>>>
>>>>>Warning: pg_result(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/globals.php on line 205
>>>>>
>>>>>Warning: pg_exec(): supplied argument is not a valid
>>>>>PostgreSQL link resource in
>>>>>/usr/local/www/www.postgresql.org/docs/view.php on line 23
>>>>>
>>>>>Warning: pg_numrows(): supplied argument is not a valid
>>>>>PostgreSQL result resource in
>>>>>/usr/local/www/www.postgresql.org/docs/view.php on line 25
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>On Fri, Mar 07, 2003 at 12:19:23AM -0000, Dave Page wrote:
>>>>>
>>>>>
>>>>>>It's rumoured that Bruce Momjian once said:
>>>>>>
>>>>>>
>>>>>>>You can find stuff at odbc.postgresql.org, I think.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>Hi Bruce,
>>>>>>
>>>>>>That's now deprecated. It's all at
>>>>>>http://gborg.postgresql.org/project/psqlodbc/ now.
>>>>>>
>>>>>>
>>>Regards, Dave.
>>>
>>>
>>>>>>
>>>>>>
>
>--3Pql8miugIZX0722--
>
>--DD12E476393.1047043276/postgresql.org--
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
>