Re: Where to find syntax for ODBC commands

Note: When creating the function in postgres, declared the parameters as in out .
ln_SQLResults = SQLEXEC (Handle, "{CALL DeuJob.UpdateAccounts (?tn_MachineID,?(at)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(at)harte-hanks(dot)com<mailto:Barry_Bell(at)harte-hanks(dot)com>

From: Christine Jorgensen [mailto:222mooney(at)gmail(dot)com]
Sent: Tuesday, June 05, 2012 11:47 AM
To: Barry Bell
Cc: pgsql-odbc(at)postgresql(dot)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,?(at)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(at)harte-hanks(dot)com<mailto:Barry_Bell(at)harte-hanks(dot)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<tel:954-429-3771%20x267> Fax: 954-281-1464<tel:954-281-1464> email Barry_Bell(at)harte-hanks(dot)com<mailto:Barry_Bell(at)harte-hanks(dot)com>

From: pgsql-odbc-owner(at)postgresql(dot)org<mailto:pgsql-odbc-owner(at)postgresql(dot)org> [mailto:pgsql-odbc-owner(at)postgresql(dot)org<mailto:pgsql-odbc-owner(at)postgresql(dot)org>] On Behalf Of Christine Jorgensen
Sent: Tuesday, June 05, 2012 9:30 AM
To: pgsql-odbc(at)postgresql(dot)org<mailto:pgsql-odbc(at)postgresql(dot)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