Libpq coding assistance

Started by Scott Holmesabout 25 years ago11 messagesgeneral
Jump to latest
#1Scott Holmes
scottholmes@sbcglobal.net

I am in need of some assistance in coding a c module using libpq on a
windows platform. Specifically, I need some help with providing the
input buffer for lo_write. The code sample provided in the
documentation contains this snippet:
------------

lobj_fd = lo_open(conn, lobjId, INV_WRITE);

while ((nbytes = read(fd, buffer,BUFSIZE)) > 0)
{
tmp = lo_write(conn, lobj_fd, buffer, nbytes);
if (tmp < nbytes)
fprintf(stderr, "error while reading large object\n");
}

(void) CloseHandle(fd);
(void) lo_close(conn, lobj_fd);
------------

I need to change the read() statement to use ReadFile, thus:

------------
lobj_fd = lo_open(conn, lobjId, INV_WRITE);

while (ReadFile(fd, buffer, BUFSIZE, &nbytes, NULL)!=EOF )
{
tmp = lo_write(conn, lobj_fd, buffer, nbytes);
if (tmp < nbytes)
fprintf(stderr, "error while reading large object\n");
}

(void) lo_close(conn, lobj_fd);
(void) CloseHandle(fd);
--------------

This, of course, doesn't work. The program is reported as no longer responding.
An infinite loop I suspect. If anyone is doing this or knows how to code it,
I would be grateful.

--
---------------------------------------------------------------------
Scott Holmes http://pages.sbcglobal.net/scottholmes
scottholmes@sbcglobal.net

Independent Programmer/Analyst Passport 4GL
PHP HTML Composer PostgreSQL Informix 4GL, SQL
---------------------------------------------------------------------
There are more things in heaven and earth, Horatio,
than are dreamt of in your philosophy
---------------------------------------------------------------------

#2Doug McNaught
doug@wireboard.com
In reply to: Scott Holmes (#1)
Re: Libpq coding assistance

Scott Holmes <scottholmes@sbcglobal.net> writes:

------------

lobj_fd = lo_open(conn, lobjId, INV_WRITE);

while ((nbytes = read(fd, buffer,BUFSIZE)) > 0)
{

}

------------

I need to change the read() statement to use ReadFile, thus:

------------
lobj_fd = lo_open(conn, lobjId, INV_WRITE);

while (ReadFile(fd, buffer, BUFSIZE, &nbytes, NULL)!=EOF )
{

}
--------------

This, of course, doesn't work. The program is reported as no longer
responding. An infinite loop I suspect. If anyone is doing this or
knows how to code it, I would be grateful.

Sounds like ReadFile (a Windows call?) isn't behaving the way you
think it does. Write some test code and play around with it until you
understand how it works.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

#3Command Prompt, Inc.
pgsql-general@commandprompt.com
In reply to: Jeff Lu (#7)
Re: Got error on CREATE TABLE AS (+Bug?)

On Sun, 4 Nov 2001, Jeff Lu wrote:

Hi,
I'm getting parser error at or near AS
when try to copy a table using CREATE TABLE AS
Here what I have in the sql statement
CREATE TABLE employee_backup (id text UNIQUE, lastname text, firstname text,
phone text) AS SELECT * FROM employee
Can some body tell me what is wrong with the sql statement?

I don't believe you can specify types or constraints. Also, if you specify
the name of each column in parens, PostgreSQL seems to get confused if you
do a SELECT * following AS, rather than specify each table name as well
(as of 7.1.x, I get ERROR: CREATE TABLE/AS SELECT has mismatched column
count - is this a known bug?)

I believe this should work, though you lose the UNIQUE constraint:

CREATE TABLE employee_backup AS SELECT * FROM employee;

Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com

#4Command Prompt, Inc.
pgsql-general@commandprompt.com
In reply to: Command Prompt, Inc. (#3)
Re: Got error on CREATE TABLE AS (+Bug?)

On Sun, 4 Nov 2001, Command Prompt, Inc. wrote:

I don't believe you can specify types or constraints. Also, if you specify
the name of each column in parens, PostgreSQL seems to get confused if you
do a SELECT * following AS, rather than specify each table name as well
(as of 7.1.x, I get ERROR: CREATE TABLE/AS SELECT has mismatched column
count - is this a known bug?)

Er, make that "column name" above, not "table name". Here's an example of
what I was trying to describe:

lx=# CREATE TABLE abc (a char, b char, c char);
CREATE
lx=# CREATE TABLE xyz (x, y, z) AS SELECT * FROM abc;
ERROR: CREATE TABLE/AS SELECT has mismatched column count
lx=# CREATE TABLE xyz (x, y, z) AS SELECT a, b, c FROM abc;
SELECT

You can explicitly provide each column name in the AS SELECT clause, and
that appears to work properly, but supplying a "*" confuses the backend.

Is this as it should be, or is it a known issue?

Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com

#5Jeff Lu
jklcom@mindspring.com
In reply to: Scott Holmes (#1)
Is there an internal timestamp on a table when it was created?

Hi,

Is there an internal timestamp on a table when it was created?

I want to the program to perform an auto daily backup on certain tables. If
I can check time stamp on the table as when it was created, so I can set the
program to do just one backup a day.

Thanks
-Jeff

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Command Prompt, Inc. (#4)
Re: Got error on CREATE TABLE AS (+Bug?)

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

lx=# CREATE TABLE abc (a char, b char, c char);
CREATE
lx=# CREATE TABLE xyz (x, y, z) AS SELECT * FROM abc;
ERROR: CREATE TABLE/AS SELECT has mismatched column count
lx=# CREATE TABLE xyz (x, y, z) AS SELECT a, b, c FROM abc;
SELECT

Looks like a bug to me too. Will look into it.

regards, tom lane

#7Jeff Lu
jklcom@mindspring.com
In reply to: Jeff Lu (#5)
Got error on CREATE TABLE AS

Hi,

I'm getting parser error at or near AS

when try to copy a table using CREATE TABLE AS

Here what I have in the sql statement

CREATE TABLE employee_backup (id text UNIQUE, lastname text, firstname text,
phone text) AS SELECT * FROM employee

Can some body tell me what is wrong with the sql statement?

Thank you

#8Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tom Lane (#6)
Re: Got error on CREATE TABLE AS (+Bug?)

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

lx=# CREATE TABLE abc (a char, b char, c char);
CREATE
lx=# CREATE TABLE xyz (x, y, z) AS SELECT * FROM abc;
ERROR: CREATE TABLE/AS SELECT has mismatched column count
lx=# CREATE TABLE xyz (x, y, z) AS SELECT a, b, c FROM abc;
SELECT

Looks like a bug to me too. Will look into it.

Fix committed for 7.2.

regards, tom lane

#9Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Tom Lane (#8)
Re: Got error on CREATE TABLE AS (+Bug?)

Yes, it will be fixed in 7.2, which is the next release.

---------------------------------------------------------------------------

Thanks,
will this be fixed on the next release?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Sunday, November 04, 2001 9:02 PM
To: Command Prompt, Inc.; Jeff Lu; Postgresql General
Subject: Re: [GENERAL] Got error on CREATE TABLE AS (+Bug?)

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

lx=# CREATE TABLE abc (a char, b char, c char);
CREATE
lx=# CREATE TABLE xyz (x, y, z) AS SELECT * FROM abc;
ERROR: CREATE TABLE/AS SELECT has mismatched column count
lx=# CREATE TABLE xyz (x, y, z) AS SELECT a, b, c FROM abc;
SELECT

Looks like a bug to me too. Will look into it.

Fix committed for 7.2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
#10Jeff Lu
jklcom@mindspring.com
In reply to: Tom Lane (#8)
Re: Got error on CREATE TABLE AS (+Bug?)

Thanks,
will this be fixed on the next release?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Sunday, November 04, 2001 9:02 PM
To: Command Prompt, Inc.; Jeff Lu; Postgresql General
Subject: Re: [GENERAL] Got error on CREATE TABLE AS (+Bug?)

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:

lx=# CREATE TABLE abc (a char, b char, c char);
CREATE
lx=# CREATE TABLE xyz (x, y, z) AS SELECT * FROM abc;
ERROR: CREATE TABLE/AS SELECT has mismatched column count
lx=# CREATE TABLE xyz (x, y, z) AS SELECT a, b, c FROM abc;
SELECT

Looks like a bug to me too. Will look into it.

Fix committed for 7.2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

#11Jeff Lu
jklcom@mindspring.com
In reply to: Command Prompt, Inc. (#3)
Re: Got error on CREATE TABLE AS (+Bug?)

Yes, I've tried the following and it worked but the unique key contraint is
losed.

CREATE TABLE employee_backup AS SELECT * FROM employee;

-----Original Message-----
From: Command Prompt, Inc. [mailto:pgsql-general@commandprompt.com]
Sent: Sunday, November 04, 2001 3:31 PM
To: Jeff Lu
Cc: Postgresql General
Subject: Re: [GENERAL] Got error on CREATE TABLE AS (+Bug?)

On Sun, 4 Nov 2001, Jeff Lu wrote:

Hi,
I'm getting parser error at or near AS
when try to copy a table using CREATE TABLE AS
Here what I have in the sql statement
CREATE TABLE employee_backup (id text UNIQUE, lastname text, firstname

text,

phone text) AS SELECT * FROM employee
Can some body tell me what is wrong with the sql statement?

I don't believe you can specify types or constraints. Also, if you specify
the name of each column in parens, PostgreSQL seems to get confused if you
do a SELECT * following AS, rather than specify each table name as well
(as of 7.1.x, I get ERROR: CREATE TABLE/AS SELECT has mismatched column
count - is this a known bug?)

I believe this should work, though you lose the UNIQUE constraint:

CREATE TABLE employee_backup AS SELECT * FROM employee;

Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com