Обсуждение: question on accessing PostgreSQL with C
Hello,
I'm a newbee in PostgreSQL, Linux world. Currently facing some problem
in building a simple application. I'm trying to write a C program that
get input from user, then update (insert / change) the database, or
retrieve selected data.
Questions:
(1) Where to get more example of communicating with PostgreSQL with C
program?
(2) How to put variables into the SQL?
I use C program to access PostgreSQL with libpq, and use PQexec(
) to send the query. One of the failed example of mine,
:
/* get input from user, a string and an integer */
:
PQexec(conn,"insert into NameList (name,id) values
(%s,%d).......
:
/* I'm trying to put variables of string and integer into the
query */
(3) How to check who are the users in PostgreSQL system?
Please advise.
Thanks & Regards,
LCH
On Sun, 7 Feb 1999, hoelc wrote: > > (2) How to put variables into the SQL? > I use C program to access PostgreSQL with libpq, and use PQexec( > ) to send the query. One of the failed example of mine, > I'd recommend using ecpg that comes with postgresql in the place of libpq direct. It is IMHO orders of magnitude easier than libpg alone. The syntax follows the same as Oracle's Pro*C so you can pretty much use any of their manuals and get a working program. I found an on-line copy of the Pro*c manual at http://www.dal.ca/%7eoracle/oradoc/DOC/api/doc/PC_22/toc.htm With it you end up with something like the following in your code exec sql insert into customer(id,name_first,name_middle,name_last,company, address1,address2,city,state,country,zip,phone_work, phone_home,phone_fax,email,date_entered,billing_terms, confirmation_method) values (:id,:name_first, :name_middle, :name_last, :company,:address1,:address2, :city,:state,:country,:zip,:phone_work, :phone_home,:phone_fax,:email,date('now'), :billing_terms, :confirmation_method); the names preceeded by : are c variables. The ecpg preprocessor replaces this easily readable code with much nastier looking c code that does what you require. The author of the code is very responsive to few problem reports I have sent him and has sent patches, or workarounds to me usually within 24 hours. In the few cases you can't figure out a way to make something happen using ecpg I believe you can mix the libpg and ecpg code in the same program. The only place I could see a need for this is when you'd need the functionality given by Pro*C's Dynamic SQL which ecpg doesn't yet support, but I think its in the works. You can find examples of ecpg code in the source tree postgresql-6.4.2/src/interfaces/ecpg/test > > (3) How to check who are the users in PostgreSQL system? > select * from pg_user; ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 Kansas State University Department of Mathematics ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
On Sat, Feb 06, 1999 at 07:16:14PM -0600, James Thompson wrote: > http://www.dal.ca/%7eoracle/oradoc/DOC/api/doc/PC_22/toc.htm Gotta look at this. > With it you end up with something like the following in your code > > exec sql > insert into customer(id,name_first,name_middle,name_last,company, > address1,address2,city,state,country,zip,phone_work, > phone_home,phone_fax,email,date_entered,billing_terms, > confirmation_method) > values (:id,:name_first, :name_middle, :name_last, > :company,:address1,:address2, > :city,:state,:country,:zip,:phone_work, > :phone_home,:phone_fax,:email,date('now'), > :billing_terms, > :confirmation_method); Xou can even add indicators like in ... values (:id,:name_first, :name_middle:nm_ind, :name_last, ... If nm_ind is set to -1 the name_middle entry will be set to NULL. > The author of the code is very responsive to few problem reports I have > sent him and has sent patches, or workarounds to me usually within 24 > hours. Glad I can help. :-) > In the few cases you can't figure out a way to make something happen using > ecpg I believe you can mix the libpg and ecpg code in the same program. > The only place I could see a need for this is when you'd need the > functionality given by Pro*C's Dynamic SQL which ecpg doesn't yet support, > but I think its in the works. It is. In fact I already submitted a patch for the PREPARE and EXECUTE commands. James, if you'd like to test this I can send you the source until it makes its way into CVS. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael.Meskes@gmx.net | Use PostgreSQL!
Hello, Thanks a lot. I have a PostgreSQL-6.2.1 ( in RedHat Linux 5.0), and it doesn't has "ecpg". I have downloaded "ecpg" from internet, but don't know how to install it into my PostgreSQL system. Please advise. Thank you very much. Regards.....LCH Michael Meskes wrote: > On Sat, Feb 06, 1999 at 07:16:14PM -0600, James Thompson wrote: > > http://www.dal.ca/%7eoracle/oradoc/DOC/api/doc/PC_22/toc.htm > > Gotta look at this. > > > With it you end up with something like the following in your code > > > > exec sql > > insert into customer(id,name_first,name_middle,name_last,company, > > address1,address2,city,state,country,zip,phone_work, > > phone_home,phone_fax,email,date_entered,billing_terms, > > confirmation_method) > > values (:id,:name_first, :name_middle, :name_last, > > :company,:address1,:address2, > > :city,:state,:country,:zip,:phone_work, > > :phone_home,:phone_fax,:email,date('now'), > > :billing_terms, > > :confirmation_method); > > Xou can even add indicators like in > > ... values (:id,:name_first, :name_middle:nm_ind, :name_last, ... > > If nm_ind is set to -1 the name_middle entry will be set to NULL. > > > The author of the code is very responsive to few problem reports I have > > sent him and has sent patches, or workarounds to me usually within 24 > > hours. > > Glad I can help. :-) > > > In the few cases you can't figure out a way to make something happen using > > ecpg I believe you can mix the libpg and ecpg code in the same program. > > The only place I could see a need for this is when you'd need the > > functionality given by Pro*C's Dynamic SQL which ecpg doesn't yet support, > > but I think its in the works. > > It is. In fact I already submitted a patch for the PREPARE and EXECUTE > commands. James, if you'd like to test this I can send you the source until > it makes its way into CVS. > > Michael > -- > Michael Meskes | Go SF 49ers! > Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! > Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! > Email: Michael.Meskes@gmx.net | Use PostgreSQL!
On Wed, 10 Feb 1999, hoelc wrote: > I have a PostgreSQL-6.2.1 ( in RedHat Linux 5.0), and it doesn't has "ecpg". > I have downloaded "ecpg" from internet, but don't know how to install it into my > PostgreSQL system. Please advise. Hmmm, Did you get the version from the link on the postgresql web site? If so don't install it. I can't remember the version number but I believe it is way behind the current version bundled with postgresql 6.4.2 which is up around .50 I think. To get any newer versions you need to go to the postgresql CVS code and I haven't tried that yet. Not much help am I? ;-) Seriously though, is there some reason you need to run the 6.2.1 code? If not then I'd just rpm -e the current postgresql, grab the latest and greatest 6.4.2 source code and compile away. I don't recall having any troubles building postgresql 6.4.2 on my mutated RedHat 5.1 system. I'm thinking quite a few gotchas were probably fixed between 6.2.x and 6.4.x. Take care, James ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 Kansas State University Department of Mathematics ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
On Wed, Feb 10, 1999 at 11:31:15AM +0800, hoelc wrote: > I have a PostgreSQL-6.2.1 ( in RedHat Linux 5.0), and it doesn't has "ecpg". That's correct. Ecpg is bundled with PostgreSQL since 6.3. > I have downloaded "ecpg" from internet, but don't know how to install it into my > PostgreSQL system. Please advise. Which version of ecpg? You should be able to compile ecpg standalon without much of a problem. Just make sure all programs you develop are linked with -lecpg -lpq. Of course both libraries must be accessible. Also ecpg come with some additional include files that have to be installed in a place where cpp finds them. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael.Meskes@gmx.net | Use PostgreSQL!
On Tue, 9 Feb 1999, James Thompson wrote: > Seriously though, is there some reason you need to run the 6.2.1 code? > If not then I'd just rpm -e the current postgresql, grab the latest and > greatest 6.4.2 source code and compile away. I don't recall having any > troubles building postgresql 6.4.2 on my mutated RedHat 5.1 system. I'm > thinking quite a few gotchas were probably fixed between 6.2.x and 6.4.x. It's even easier than that. 6.4.2 is available as an rpm. Download that and install it over 6.2.1 using rpm -U postgre.... Rich Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. 2404 SW 22nd Street Troutdale, OR 97060-1247 U.S.A. + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) rshepard@appl-ecosys.com
Re: [INTERFACES] question on accessing PostgreSQL with C
От
markh@clockworkweb.com (Mark Himsley)
Дата:
On Tue, 9 Feb 1999 22:26:04 -0800 (PST), you wrote: > It's even easier than that. 6.4.2 is available as an rpm. Download that >and install it over 6.2.1 using rpm -U postgre.... Where from? I have not seen an RPM of 6.4 announced any ware. -- Mark Himsley. Technical Director Clockwork Web. +44 171 471 0770 http://www.clockworkweb.com
On Wed, 10 Feb 1999, Mark Himsley wrote: > Where from? > I have not seen an RPM of 6.4 announced any ware. Mark, I don't recall offhand; it might be ftp://contrib.redhat.com. Anyway, if you load http://www.filewatcher.org you can use the left-hand (file name) search window to look for postgre*6.4.2*rpm and it will show you just where it is. That's how I found it a few weeks ago. Rich Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. 2404 SW 22nd Street Troutdale, OR 97060-1247 U.S.A. + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) rshepard@appl-ecosys.com
Hello,
I've just upgrated my PostgreSQL system to version Postgres-6.4.2
Now it has the *ecpg
But I'm facing problem in using it, when I compile the C program with
cc -o tryecpg tryecpg.c -lecpg -lpq
the compiler can't recognize the " exec sql ", and give error massage
Parse error before 'sql'
What went wrong ? Please advise.
Thanks for giving me the address for the Oracle Pre*C/C++ programmer's guide, it
is a good reference. But, it doesn't exactly reflect the ecpg, right? Is there
any reference and example program for how to use ecpg?
Thank you very much.
Regards,
LCH
Michael Meskes wrote:
> On Sat, Feb 06, 1999 at 07:16:14PM -0600, James Thompson wrote:
> > http://www.dal.ca/%7eoracle/oradoc/DOC/api/doc/PC_22/toc.htm
>
> Gotta look at this.
>
> > With it you end up with something like the following in your code
> >
> > exec sql
> > insert into customer(id,name_first,name_middle,name_last,company,
> > address1,address2,city,state,country,zip,phone_work,
> > phone_home,phone_fax,email,date_entered,billing_terms,
> > confirmation_method)
> > values (:id,:name_first, :name_middle, :name_last,
> > :company,:address1,:address2,
> > :city,:state,:country,:zip,:phone_work,
> > :phone_home,:phone_fax,:email,date('now'),
> > :billing_terms,
> > :confirmation_method);
>
> Xou can even add indicators like in
>
> ... values (:id,:name_first, :name_middle:nm_ind, :name_last, ...
>
> If nm_ind is set to -1 the name_middle entry will be set to NULL.
>
> > The author of the code is very responsive to few problem reports I have
> > sent him and has sent patches, or workarounds to me usually within 24
> > hours.
>
> Glad I can help. :-)
>
> > In the few cases you can't figure out a way to make something happen using
> > ecpg I believe you can mix the libpg and ecpg code in the same program.
> > The only place I could see a need for this is when you'd need the
> > functionality given by Pro*C's Dynamic SQL which ecpg doesn't yet support,
> > but I think its in the works.
>
> It is. In fact I already submitted a patch for the PREPARE and EXECUTE
> commands. James, if you'd like to test this I can send you the source until
> it makes its way into CVS.
>
> Michael
> --
> Michael Meskes | Go SF 49ers!
> Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
> Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
> Email: Michael.Meskes@gmx.net | Use PostgreSQL!
On Fri, Feb 12, 1999 at 12:42:34AM +0800, hoelc wrote: > Hello, > I've just upgrated my PostgreSQL system to version Postgres-6.4.2 > Now it has the *ecpg > But I'm facing problem in using it, when I compile the C program with > cc -o tryecpg tryecpg.c -lecpg -lpq > the compiler can't recognize the " exec sql ", and give error massage > Parse error before 'sql' The exec sql should be there after running ecpg. ecpg is a preprocessor. You have to feed you're source (should be named file.pgc BTW) through ecpg before running cc. That is: ecpg tryecpg.pgc cc -o tryecpg tryecpg.c -lecpg -lpq > What went wrong ? Please advise. If this was not the problem we need more details. > Thanks for giving me the address for the Oracle Pre*C/C++ programmer's guide, it > is a good reference. But, it doesn't exactly reflect the ecpg, right? Is there > any reference and example program for how to use ecpg? The reference is pretty outdated but the source comes with three examples. Oracle's Pro*C manual however is a good reference for the syntac of embedded SQL, although it does not comply with the standard on some parts. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael.Meskes@gmx.net | Use PostgreSQL!
From a previous email
> > > exec sql
> > > insert into customer(id,name_first,name_middle,name_last,company,
> > > address1,address2,city,state,country,zip,phone_work,
> > > phone_home,phone_fax,email,date_entered,billing_terms,
> > > confirmation_method)
> > > values (:id,:name_first, :name_middle, :name_last,
> > > :company,:address1,:address2,
> > > :city,:state,:country,:zip,:phone_work,
> > > :phone_home,:phone_fax,:email,date('now'),
> > > :billing_terms,
> > > :confirmation_method);
> >
> > Xou can even add indicators like in
> >
> > ... values (:id,:name_first, :name_middle:nm_ind, :name_last, ...
> >
I tried the following (screwed up spacing due to pine autowrap :-)
exec sql
insert into
customer(uid,id,name_first,name_middle,name_last,company,
address1,address2,city,state,country,zip,phone_work,
phone_home,phone_fax,email,date_entered,billing_terms,
confirmation_method)
values (:uid,:uid:id_n,:name_first:name_first_n,
:name_middle:name_middle_n, :name_last:name_last_n,
:company:company_n,:address1:address1_n,
:address2:address2_n,:city:city_n,:state:state_n,
:country:country_n,:zip:zip_n,:phone_work:phone_work_n,
:phone_home:phone_home_n,:phone_fax:phone_fax_n,:email:email_n,
date('now'),:billing_terms:billing_terms_n,
:confirmation_method:confirmation_method_n);
Adding the indicators to the above statement causes ecpg(from the 6.4.2
distro) to seg fault while processing the statement. I defined each
indicator as the original var name with _n added to the end. I tried
type int and long. The statement preprocesses fine without the indicator
vars.
I dont know the proper method for looking into core files but by trial and
error I got gdb to produce (is this correct way to do this?)
[jamest@calvin obelib]$ gdb ecpg core
GNU gdb 4.17
Copyright 1998 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you
are welcome to change it and/or distribute copies of it under certain
conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for
details.
This GDB was configured as "i386-redhat-linux"...
Core was generated by `ecpg customer.ec'.
Program terminated with signal 11, Segmentation fault.
find_solib: Can't read pathname for load map: Input/output error
#0 0x8054d81 in ECPGdump_a_type ()
(gdb) bt
#0 0x8054d81 in ECPGdump_a_type ()
#1 0x8049973 in dump_variables ()
#2 0x8049941 in dump_variables ()
#3 0x8049941 in dump_variables ()
#4 0x8049941 in dump_variables ()
#5 0x8049941 in dump_variables ()
#6 0x8049941 in dump_variables ()
#7 0x8049941 in dump_variables ()
#8 0x8049941 in dump_variables ()
#9 0x8049941 in dump_variables ()
#10 0x8049941 in dump_variables ()
#11 0x8049941 in dump_variables ()
#12 0x8049941 in dump_variables ()
#13 0x8049941 in dump_variables ()
#14 0x8049941 in dump_variables ()
#15 0x8049941 in dump_variables ()
#16 0x8049941 in dump_variables ()
#17 0x8049941 in dump_variables ()
#18 0x8049fe7 in output_statement ()
#19 0x804b91b in yyparse ()
#20 0x8055475 in main ()
Does anyone have a working example of using indicator vars with ecpg?
Thanks.
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561
Kansas State University Department of Mathematics
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
On Tue, Feb 16, 1999 at 09:14:10PM -0600, James Thompson wrote:
> exec sql
> insert into
> customer(uid,id,name_first,name_middle,name_last,company,
>
> address1,address2,city,state,country,zip,phone_work,
>
> phone_home,phone_fax,email,date_entered,billing_terms,
> confirmation_method)
> values (:uid,:uid:id_n,:name_first:name_first_n,
> :name_middle:name_middle_n, :name_last:name_last_n,
> :company:company_n,:address1:address1_n,
> :address2:address2_n,:city:city_n,:state:state_n,
> :country:country_n,:zip:zip_n,:phone_work:phone_work_n,
>
> :phone_home:phone_home_n,:phone_fax:phone_fax_n,:email:email_n,
> date('now'),:billing_terms:billing_terms_n,
> :confirmation_method:confirmation_method_n);
>
>
> Adding the indicators to the above statement causes ecpg(from the 6.4.2
> distro) to seg fault while processing the statement. I defined each
> indicator as the original var name with _n added to the end. I tried
> type int and long. The statement preprocesses fine without the indicator
> vars.
Strange. Please try the attached test file. I defined all your variables as
int and it works with ecpg versions 2.4.4 (PG 6.4.1), 2.4.9 and 2.5.0.
> I dont know the proper method for looking into core files but by trial and
> error I got gdb to produce (is this correct way to do this?)
Yes.
> [jamest@calvin obelib]$ gdb ecpg core
> GNU gdb 4.17
> ...
> #0 0x8054d81 in ECPGdump_a_type ()
> (gdb) bt
> ...
Could you please check where in ECPGdump_a_type it dumps core? The function
is a pretty short one.
Michael
--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net | Use PostgreSQL!
Вложения
On Wed, 17 Feb 1999, Michael Meskes wrote: > > Strange. Please try the attached test file. I defined all your variables as > int and it works with ecpg versions 2.4.4 (PG 6.4.1), 2.4.9 and 2.5.0. > > > I dont know the proper method for looking into core files but by trial and > > error I got gdb to produce (is this correct way to do this?) > Yes the test files worked. Until I added the other variables used in the insert statement. I put the following in the sql declare section (too lazy to type them all). char *uid,*id,*name_first,*name_middle,*name_last; And then ecpg bombs with the seg fault. > > Could you please check where in ECPGdump_a_type it dumps core? The function > is a pretty short one. > Sure, if I can figure out how. Can I do that via gdb? Or do I just need to use puts throughout the function? I'd also be willing to try the cvs versions if someone could tell me where to get them, I cant find cvs download instructions on the postgresql site. ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 Kansas State University Department of Mathematics ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<