Thread: Use of C function parameters from embedded SQL
I'm in the process of converting some embedded SQL code from ORACLE to
PostgreSQL and I'm running into some snags. I'm wondering if there is an
easy way to use the parameters passed to a C function as variables in a EXEC
SQL command without having to declare the parameter names in a EXEC SQL
DECLARE section.
Here is a section of embedded SQL code that works fine in Oracle (some code
removed for brevity):
void open_database(char *user_name, char*pass_word)
{
EXEC SQL CONNECT :user_name IDENTIFIED BY :pass_word;
}
The only way I could get ecpg to process this code was to include a DECLARE
section, for example:
void open_database(char *user_name, char*pass_word)
{
EXEC SQL BEGIN DECLARE SECTION;
char *user_name;
char *pass_word;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT :user_name IDENTIFIED BY :pass_word;
}
Does anyone know of a workaround to allow use of the function parameters
with having to declare them first?
Thanks in advance,
Dick Brooks
On Thu, Jun 14, 2001 at 02:16:29PM -0500, Dick Brooks wrote:
> Here is a section of embedded SQL code that works fine in Oracle (some code
> removed for brevity):
>
> void open_database(char *user_name, char*pass_word)
> {
> EXEC SQL CONNECT :user_name IDENTIFIED BY :pass_word;
> }
This only work with Pro*C if you run it in full parse mode. That means Pro*C
will parse your complete C file an not just the EXEC SQL parts. I would not
recommend doing this. At first glance it looks like a nice feature but you
can run into numerous sorts of trouble.
> The only way I could get ecpg to process this code was to include a DECLARE
> section, for example:
Yes, that's the standard way of doing this.
> Does anyone know of a workaround to allow use of the function parameters
> with having to declare them first?
No, there is none. The full parse mode has been on my todo list for a long
time but with very low priority. Sorry.
Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!
Hello,
I´m using PostgreSQL 7.1.2 (from Debian). My pg_hba.conf looks like
# default
local all peer sameuser
host all 127.0.0.1 255.0.0.0 ident sameuser
# added myself to cope with the problem
host all 127.0.0.1 255.0.0.0 crypt
To check, whether users can connect via crypt method I wrote a small
python script:
#!/usr/bin/python
def hello():
import sys
from pg import DB
conn = DB('test',user='testuser',passwd='test')
x = conn.query("SELECT * from testtable")
return x
print hello()
/var/log/postgresql.log shows:
Peer authentication failed for user 'testuser'
I can correctly log in on the Linux system with the testuser account
and the script works if I run it from this account *and* deleting
the passwd option from the connection string - but that´s the ident
authenification method. With password option the connection can not
be established.
Moreover I have read the postgresql docs especially auth-methods.html.
I tried to alter the pg_shadow password using
test=# alter user testuser with password 'test' ;
but I´m not sure if this works because of the strange sounding
paragraph in auth-methods.html:
Note that using alternative passwords like this means that one can no
longer use ALTER USER to change one's password. It will still appear
to work but the password one is actually changing is not the password
that the system will end up using.
I also gave those alternate passwords a try and did
/usr/lib/postgresql/bin/pg_passwd /etc/postgresql/passwd
Username: testuser
New password:
Re-enter new password:
and changed the pg_hba.conf line to
host all 127.0.0.1 255.0.0.0 crypt passwd
But nothing changed.
Can anybody help me out this trouble?
Kind regards
Andreas.
Hi all,
Here the latest version and first working release of Ora2Pg a tool to export
Oracle database to PostgreSQL.
It currently dump the database schema (tables, views, sequences,
indexes, grants), with primary, unique and foreign keys into PostgreSQL
syntax without editing the SQL code generated. You can dump only a
particular schema from the Oracle database.
Functions, procedures and triggers with SQL or PLSQL code generated must
be reviewed to match the PostgreSQL syntax. Some usefull recommandation
on porting Oracle to PostgreSQL can be found at
http://techdocs.postgresql.org/
under the "Converting from other Databases to PostgreSQL" Oracle part. I
just
notice one thing more is that the trunc() function in Oracle is the same for
number
or date so be carefull when porting to PostgreSQL to use trunc() for number
and
date_trunc() for date.
I will add more precision in type NUMBER conversion based on length to match
as closest as possible all rich PostgreSQL numerics type. But it seems not to be
urgent as it seems that Oracle DBAs only create number with length 22 (default)
Space seems not to be their problem...
The following need help :
- SQL query converter.
- SQL/PLSQL code converter.
- Extracting/converting data for loading into PostgreSQL.
By this I mean replacing Oracle function by PostgreSQL one's into the SQL code
and reformat some type to match PostgreSQL syntax.
Extract data as text seem not be possible with Oracle so the only way is to
select data from oracle and convert them on the fly by a online program.
Latest version (1.3) will be available under /contrib soon (thanks to Bruce)
but you can find it now at http://www.samse.fr/GPL/ora2pg/
Regards
Gilles DAROLD
tillea@rki.de writes:
> I�m using PostgreSQL 7.1.2 (from Debian). My pg_hba.conf looks like
> # default
> local all peer sameuser
There is no such authentication type as "peer".
regards, tom lane
On Thu, 21 Jun 2001, Tom Lane wrote:
> > I´m using PostgreSQL 7.1.2 (from Debian). My pg_hba.conf looks like
>
> > # default
> > local all peer sameuser
>
> There is no such authentication type as "peer".
Hmm, seems to be a bug in the Debian package.
From /etc/postgresql/pg_hba.conf:
# peer: Authentication is done as for ident, but by obtaining user
# identification from the Unix socket credentials. (This
# service is only supported by a few operating systems. If
# it is not usable in a particular implementation, use of
# this method will cause an error.) Username mapping is
# exactly the same as for ident.
BUT:
If I remove the above line I get
_pg.error: No pg_hba.conf entry for host localhost, user testuser, database test
conn = DB('test')
which worked *with* the line. The the peer authentication type "peer" seems
to exist in the way it is described in the pg_hba.conf comment and is
necessary for the socket authentification via ident.
The same message do I get when trying the crypt athentication as in my
original question.
So it seems the line is evident for my setup.
Kind regards
Andreas.
Andreas Tille <tillea@rki.de> writes:
> On Thu, 21 Jun 2001, Tom Lane wrote:
>> There is no such authentication type as "peer".
> Hmm, seems to be a bug in the Debian package.
>> From /etc/postgresql/pg_hba.conf:
> # peer: Authentication is done as for ident, but by obtaining user
> # identification from the Unix socket credentials. (This
> # service is only supported by a few operating systems. If
> # it is not usable in a particular implementation, use of
> # this method will cause an error.) Username mapping is
> # exactly the same as for ident.
Where did that come from? There is certainly no such thing in any
official Postgres release. Is Debian shipping locally-modified
Postgres? If so I think they need to label it as such.
regards, tom lane
On Friday 22 June 2001 09:44, Tom Lane wrote: > Where did that come from? There is certainly no such thing in any > official Postgres release. Is Debian shipping locally-modified > Postgres? If so I think they need to label it as such. Please see http://non-us.debian.org/debian-non-US/pool/non-US/main/p/postgresql/postgresql_7.1.2-1.diff.gz for details. It's fascinating. For the record, the RPM patches don't go this far. Just enough patching to build and run to FHS standards is all I have done. (see http://www.postgresql.org/ftpsite/binary/v7.1.2/RPMS/rpm-pgsql-7.1.patch) -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
New version applied to current CVS tree. Thanks. > Hi all, > > Here the latest version and first working release of Ora2Pg a tool to export > Oracle database to PostgreSQL. > > It currently dump the database schema (tables, views, sequences, > indexes, grants), with primary, unique and foreign keys into PostgreSQL > syntax without editing the SQL code generated. You can dump only a > particular schema from the Oracle database. > > Functions, procedures and triggers with SQL or PLSQL code generated must > be reviewed to match the PostgreSQL syntax. Some usefull recommandation > on porting Oracle to PostgreSQL can be found at > http://techdocs.postgresql.org/ > under the "Converting from other Databases to PostgreSQL" Oracle part. I > just > notice one thing more is that the trunc() function in Oracle is the same for > number > or date so be carefull when porting to PostgreSQL to use trunc() for number > and > date_trunc() for date. > > I will add more precision in type NUMBER conversion based on length to match > as closest as possible all rich PostgreSQL numerics type. But it seems not to be > > urgent as it seems that Oracle DBAs only create number with length 22 (default) > Space seems not to be their problem... > > The following need help : > > - SQL query converter. > - SQL/PLSQL code converter. > - Extracting/converting data for loading into PostgreSQL. > > By this I mean replacing Oracle function by PostgreSQL one's into the SQL code > and reformat some type to match PostgreSQL syntax. > > Extract data as text seem not be possible with Oracle so the only way is to > select data from oracle and convert them on the fly by a online program. > > Latest version (1.3) will be available under /contrib soon (thanks to Bruce) > but you can find it now at http://www.samse.fr/GPL/ora2pg/ > > Regards > > Gilles DAROLD > > > ---------------------------(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