Re: Where to find syntax for ODBC commands

Barry,

I tried the syntax below for the call to DeuJob.Unduplicate:
ln_SQLResults = SQLEXEC (Handle,"select * from
DeuJob.Unduplicate(4,?p_DupJobs)”, "deu_cur_job")
It keeps asking me to enter a value for p_DupJobs. Apparently it does not
recognize it as a returned value.

Christine

On Tue, Jun 5, 2012 at 11:57 AM, Barry Bell <Barry_Bell(at)harte-hanks(dot)com>wrote:

> ** **
>
> 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****
>
> ** **
>
> *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>
> 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(at)harte-hanks(dot)com****
>
> ****
>
> *From:* 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
> *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****
>
> ** **
>