Обсуждение: More on "What am I doing wrong!"

Thanks for the help to date and as a result I've made some progress using
psql. But I'm still no further forward with my C++ application!

I wrote a plpgsql function that executed the insert then the update
functions; I then executed the test function using:

begin transaction ;
select testing() ;
commit ;

This worked as one would expect. Now, this proves that the functions are
themselves syntactically and gramatically sound. But when these functions
are called in the application the first succeeds as it returns the new row
id. This row id is then passed to the update function but still fails.

I know the logic of the approach is sound since it works in psql, so again
I've got a big question mark above my head as to why this doesn't work in
C++ land.

The only thing that comes to mind is the "begin transaction" statement is
the key to psql's success. So how would I implement this via the ODBC.
There is to my knowledge no explicit BeginTransaction method in the API,
although the MSDN does produce results for the ADO and RDO. But I'm not
using them!

There is one thing I have noticed (or failed to mention earlier) is that
when the second (update) function fails the transaction is rolled back.
When the transaction is rolled back I get errors saying that there is a
Function Sequence error. So the implication here is that there was no
transaction in the first place.

According to the documents when the SQL_ATTR_AUTOCOMMIT is turned off the
next statement issued starts a transaction. When I turn off auto commit I
don't get an error.

No wonder I'm bald!

--
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk

On Sat, 18 May 2002, mordicus wrote:

> Hi,
>
> Ok, i think it's perhaps a bug of the odbc driver, i never tryed 'call' with
> pgsql driver ...
>
> in your code, at
> const char * CPatient::szApply = "{? = CALL apply_parent_details(?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?)}" ;
> and
> const char * CSecondParent::szApply = "{? = CALL apply_partner_details(?, ?,
> ?, ?)}" ;
>
> try to juste select the function
>
> select apply_parent_details(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
>
> it should work
>

I've tried that and the first function now faile!

--
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk

On Sat, 18 May 2002, mordicus wrote:

> faile with a message ?

No there is nothing. Only my own error trap.

> in your function CPatient::ApplyData(CDataBase* pData)
> "ret" should not be 0 since it's an integer ?

Ok, I'll change that.

> if (Ok = SQL_SUCCEEDED(SQLBindParameter(pData->GetHandle(), 1,
> SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &nParent, 0, &ret)))
>
> Ok, so, if i understand, with psql when you do
>
> select apply_parent_details(...your params...) it work and
> in your code, when you do the same think it faile ?

Yes, exactly.

> Can you active sql log to see what happend ?

How do I do that?

> I've tested with my own code, and it work well.

Tested what?

> Can you send me the shema of your database ?

Sure. Will a dump() do?

--
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk

On Sat, 18 May 2002, mordicus wrote:

> I found another problem with your plpgsql function in apply_parent_details,
>
> UPDATE "Parents" SET "MTitle" = ptitle, "MForename" = pforename, "MSurname" =
> psurname, "Addr1" = addr1, "Addr2" = addr2, "Addr3" = addr3, "Town" = town,
> "Postcode" = postcode, "TelNo1" = telno1, "TelNo2" =@telno2, "eMail" = email
>
> "TelNo2" =@telno2 ??? should be juste telno2, no ?

Yes you are correct, but this code is never called in this context.
However, all the statements have since been changes and now use $n.

> Another thing, if possible, don't use oid, use sequence instead, more
> reliable.

Ok, I'll change it.

> And when you declare your function, don't use "iscachable" option since this
> type of function is not cacheable, it can cause some probs.

It's not! Well I consider this a prime candidate for caching. Oh well.

> And, last thing, sorry for my bad english :)

:-)

>

--
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk

On Sun, 19 May 2002, mordicus wrote:

> Hello,
>
> Le Dimanche 19 Mai 2002 10:53, vous avez �crit :
> > On Sat, 18 May 2002, mordicus wrote:
> > > faile with a message ?
> >
> > No there is nothing. Only my own error trap.
>
> > > Ok, so, if i understand, with psql when you do
> > >
> > > select apply_parent_details(...your params...) it work and
> > > in your code, when you do the same think it faile ?
> >
> > Yes, exactly.
>
> Very strange :)
>
>
> > > Can you active sql log to see what happend ?
> > How do I do that?
>
> you are working on windows or linux ?
> I'm not on windows actually, but from memory :
>
> on windows, in control panel / ODBC MANAGER when you configure your dsn, you
> can also set sql trace to on, it will create a file named c:\SQL.LOG.

I am not using an ODBC DSN, I'm going direct to the Server.

> of course, you have to modifie your code
>
> sprintf(m_Login.szConnect,
>
"DRIVER={PostgreSQL};DATABASE=gpplus;SERVER=%s;PORT=%d;UID=%s;PWD=%s;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;DEBUG=0;COMMLOG=0;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=1;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_",
> getIPAddr(), getPort(), getUser(), getPassword()) ;
>
> become
>
> sprintf(m_Login.szConnect, "dsn=yourdatabase;UID=%s;PWD=%s;", getUser(),
> getPassword()) ;

Which is why there is a getIPAddr() in the above.

> just for testing.
>
> And on postgresql, you can also activate debug log, but i have to know how you
> start postgresql ? have you compiled it yourself ?
>
> >
> > > I've tested with my own code, and it work well.
> >
> > Tested what?
>
> insert/update via functions with some odbc code ...

Fair enough but have done an atomic transaction in the same manner, where
the return from function A is sent to function B for example?

> > > Can you send me the shema of your database ?
> >
> > Sure. Will a dump() do?
>
> yes, pg_dump -s yourdatabase  -f shema.sql and send me shema.sql :)
> so i can try with your shema and some dummy datas.
>
Ok, I'll do that.
>

--
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk

On Sun, 19 May 2002, mordicus wrote:

>
> ok, it's normal that your select faile since i tell you to change your
> function from '?=exe' to "select ...", after preparing your query, you have
> to bind collumns since it's a simple select. so don't precise an outpout
> param.
>
> it should work

This had occurred to me and I was in the process of doing this but now my
Server has died! Now where did I put that paddle?

--
Regards,


Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk