Skip site navigation (1) Skip section navigation (2)

Re: Where to find syntax for ODBC commands

From: Christine Jorgensen <222mooney(at)gmail(dot)com>
To: Barry Bell <Barry_Bell(at)harte-hanks(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Where to find syntax for ODBC commands
Date: 2012-06-05 15:46:43
Message-ID: CAK_0VL2WDTJkQZLtMdxDfqUNg5_esLkrcfXm-UXUuHzWp1kdVA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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****
>

In response to

Responses

pgsql-odbc by date

Next:From: Barry BellDate: 2012-06-05 15:57:10
Subject: Re: Where to find syntax for ODBC commands
Previous:From: Adrian KlaverDate: 2012-06-05 13:46:38
Subject: Re: Where to find syntax for ODBC commands

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group