Re: Where to find syntax for ODBC commands - Mailing list pgsql-odbc

 

Note: When creating the function in postgres, declared the parameters as in out .

ln_SQLResults = SQLEXEC (Handle,  "{CALL DeuJob.UpdateAccounts (?tn_MachineID,?@ll_Status)}")

should change to (assuming tn_machineid and ll_Status are foxpro variables (must be declared private or public)

In_SQLResults = SQLEXEC (Handle,"select * from DeuJob.UpdateAccounts(?tn_MachineID,?ll_Status) “,”CCoutputcursor” )

(You will find 2 fields in the ccoutputcursor called tn_machineid and ll_status

ln_SQLResults = SQLEXEC (Handle, "{CALL DeuJob.Unduplicate ({resultset 4, p_DupJobs})}", 'deu_cur_job')
where p_DupJobs is an Oracle TABLE OF VARCHAR2

should change to (not sure what “Resultset 4” does, but no need for result set or “Table of varhchar2), in fact, the oracle “Table” function in used or needed in Postgres

 

ln_SQLResults = SQLEXEC (Handle,"select * from DeuJob.Unduplicate(4,?p_DupJobs)”, "deu_cur_job")

Be sure to declare the function parameters as “In out” and as type “Any”(Postgres is strong typed and numeric, integer etc may need different definition unless you declare the parameter as “any type”

 

 

 

Thanks

Barry Bell, IT Department

Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell@harte-hanks.com

 

From: Christine Jorgensen [mailto:222mooney@gmail.com]
Sent: Tuesday, June 05, 2012 11:47 AM
To: Barry Bell
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Where to find syntax for ODBC commands

 

Thank you, Barry.
The current code is
ln_SQLResults = SQLEXEC (Handle, "{CALL DeuJob.Unduplicate ({resultset 4, p_DupJobs})}", 'deu_cur_job')
where p_DupJobs is an Oracle TABLE OF VARCHAR2
The procedure returns a maximum of 4 entries into the deu_cur_job cursor.

So the new code should be:
ln_SQLResults = SQLEXEC (Handle,"select * from DeuJob.Unduplicate", "deu_cur_job")
Is that correct?

and

ln_SQLResults = SQLEXEC (Handle,  "{CALL DeuJob.UpdateAccounts (?tn_MachineID,?@ll_Status)}")

should change to

n_SQLResults = SQLEXEC (Handle,"select * from DeuJob.UpdateAccounts", "tn_MachineID, ll_Status")

 

I will play with this and see what happens.

How do you figure this stuff out?  The psqlodbc documentation does not mention any of this.

Thank you,
Christine

On Tue, Jun 5, 2012 at 10:41 AM, Barry Bell <Barry_Bell@harte-hanks.com> wrote:

WE are also migrating from Oracle to Postgres using Foxpro (Nice to see we are not the only ones).

The {call package_pkg(@?in1,@?in2) { will need some modification to work in Postgres (but will work)

 

1.       Postgress only has “functions” not producedures so the above will be replced with  select * from  package_pkg(?in1,?in2)

2.       Postgress will not return an out variable like foxpro will.  The “select * package_pkg(?in1,?in2)” will return a cursor with 2 column in1, in2 with the “returned” values.

 

So you will call “select * from package_pkg(?in1,?in2)  and it will return 2 column, in1 and in2

 

We have written a Foxpro function that translates this so the old code is still compatible

 

Also, There are few ODBC setting that needs to be changed, otherwise the function/procedures will return memos instead of characters, we use the below connection string:

Driver={PostgreSQL ANSI};Server=server;Port=5432;Database=db;Uid=user;Pwd=pwd;BI=2;TextAsLongVarchar=1;UnknownSizes=2;UseServerSidePrepare=1;

 

Attaching “;BI=2;TextAsLongVarchar=1;UnknownSizes=2;UseServerSidePrepare=1;” at the end of your connection string will make thing easier.

 

 

Thanks

Barry Bell, IT Department

Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell@harte-hanks.com

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Christine Jorgensen
Sent: Tuesday, June 05, 2012 9:30 AM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Where to find syntax for ODBC commands

 

I am new to PostgreSQL.  I have a FoxPro application that currently accesses an Oracle database using the Microsoft ODBC Driver for Oracle.  I am trying to convert it to access PostgreSql.
So far I have installed the PostgreSQL drivers and am using PostgreSQL ANSI.  I have connected to the database and was able to insert data into a table and select data from another table using the FoxPro EXECSQL.  I am now trying to execute a stored procedure in the PostgreSQL database and am having trouble with the syntax for the parameters, which is different from the Oracle ODBC driver.
Is there documentation somewhere that describes the PostgreSQL ODBC syntax?

Thanks,
Christine