Access violation C5 error on Visual FoxPro SQLEXEC() call after error

Driver: psqlodbc-8_01_0104.zip ( I think)

Platform: Visual FoxPro 09.00.0000.3504 for Windows [Nov 4 2005 17:39:44]

To reproduce:

1. Ran my application
2. ODBC driver returs error from Postgres
3. Sometimes after that my application crashes with C00000005 error
Line where crash occurs contains SQLEXEC() call.
4. Sometimes the crash does not occur in first run. In this case I ran
my application server times.

Included logs in www.eetasoft.ee/odbcc5.zip (307110 bytes)

11.12.2005 11:32 3ÿ486ÿ098 mylog_1484.log
After that application crashes.


11.12.2005 11:29 3ÿ481ÿ599 mylog_188.log
Application finishes normally.

Note.

Driver version 103 fixes some of this type crashes. However, this particular
crash occurs in 103 and 104 versions of driver.

Any idea how to fix this ?

Andrus.

eetasoft@online.ee





---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> Driver version 103 fixes some of this type crashes. However, this particular
> crash occurs in 103 and 104 versions of driver.
>
> Any idea how to fix this ?

I take a look at the problem maybe today in the evening or tomorrow.

Luf

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> > Driver version 103 fixes some of this type crashes. However, this particular
> > crash occurs in 103 and 104 versions of driver.
> >
> > Any idea how to fix this ?
>
> I take a look at the problem maybe today in the evening or tomorrow.

I think I see the problem. I have some solutions in my mind but
I have time in the evening at first.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

Ludek,

> I think I see the problem. I have some solutions in my mind but
> I have time in the evening at first.

Thank you.
If you need I can try to create repro for this.

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> > I think I see the problem. I have some solutions in my mind but
> > I have time in the evening at first.
>
> Thank you.
> If you need I can try to create repro for this.

It would be nice. I think I don't need it. But test is test and it's
better to test the solution.
So If you have time for it, I want it :-)

The problem is reproducible this way:
1) bind X params
2) call ExecDirect - it fails (error, e.g. non-existing column)
3) call SQLCancel
4) bind Y params (where Y < X)
5) call ExecDirect - this may return SQL_SUCCESS but it returns
SQL_NEED_DATA right now

I know you're working with FoxPro. This is mainly for other developers.
You can take an idea how to create minimal repro ...

It doesn't care if the test fail with C000005. Mylog has to contain
second ExecDirect which returning SQL_NEED_DATA.

Thanks

Luf

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> > > I think I see the problem. I have some solutions in my mind but
> > > I have time in the evening at first.
> >
> > Thank you.
> > If you need I can try to create repro for this.
>
> It would be nice. I think I don't need it. But test is test and it's
> better to test the solution.
> So If you have time for it, I want it :-)
>
> The problem is reproducible this way:
> 1) bind X params
> 2) call ExecDirect - it fails (error, e.g. non-existing column)

Please, could you create two programs? One fails in this step
(return error) and one doesn't fail?
It could be interesting where it differs.

> 3) call SQLCancel
> 4) bind Y params (where Y < X)
> 5) call ExecDirect - this may return SQL_SUCCESS but it returns
> SQL_NEED_DATA right now

I'm not sure if my solution is good idea. I don't see the difference
between failure and non-failure process.

Thanks a lot

Luf

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

--OBd5C1Lgu00Gd/Tn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Sun, Dec 11, 2005 at 11:50:33AM +0200, Andrus Moor napsal(a):
> Driver: psqlodbc-8_01_0104.zip ( I think)
>
> Platform: Visual FoxPro 09.00.0000.3504 for Windows [Nov 4 2005 17:39:44]
>
> Any idea how to fix this ?

I tried to fix it but this seems to me as VFP bad behaviour. I'm
againist add this code to official release unless we make new option
in configure DSN dialog.

Dave I propose to rollback your patch for the first Andrus's problem
as it breaks ODBC specification (if I understand this well):
| After SQLExecute or SQLExecDirect returns SQL_NEED_DATA and before
| data has been sent for all data-at-execution parameters,
| an application can call SQLCancel to cancel the statement execution.
| After the statement has been canceled, the application can call
| SQLExecute or SQLExecDirect again.

I think bind parameters can't be destroyed in SQLCancel.

I attach patch which may solve Andrus's problem and change a little
Dave's patch. I see application may call FreeStmt(SQL_CLOSE) and
FreeStmt(SQL_RESET_PARAMS). But VFP doesn't call it.

Andrus, I created bug in bug tracker. I attached compiled DLLs there
so you can try it. Link:
http://pgfoundry.org/tracker/index.php?func=detail&aid=10004 81&group_id=1000125&atid=538

Regards,

Luf

--OBd5C1Lgu00Gd/Tn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="psqlodbc-cancel_vfp_hack.diff"

diff -c psqlodbc.orig\execute.c psqlodbc\execute.c
*** psqlodbc.orig\execute.c Tue Dec 06 21:53:30 2005
--- psqlodbc\execute.c Wed Dec 14 01:18:40 2005
***************
*** 683,688 ****
--- 711,717 ----
ConnectionClass *conn;
RETCODE result;
ConnInfo *ci;
+ STMT_Status old_status;

#ifdef WIN32
HMODULE hmodule;
***************
*** 719,724 ****
--- 748,754 ----
* force method calls the driver manager's function on behalf of
* the application.
*/
+ old_status = stmt->status;

#ifdef WIN32
if (ci->drivers.cancel_as_freestmt)
***************
*** 733,739 ****
result = PGAPI_FreeStmt(hstmt, SQL_CLOSE);
#endif

! mylog("PGAPI_Cancel: PGAPI_FreeStmt returned %d\n", result);

SC_clear_error(hstmt);
return SQL_SUCCESS;
--- 763,789 ----
result = PGAPI_FreeStmt(hstmt, SQL_CLOSE);
#endif

! mylog("PGAPI_Cancel: PGAPI_FreeStmt(SQL_CLOSE) returned %d\n", result);
!
! #ifdef WIN32
! /*
! * Brute force method calls SQLFreeStmt(SQL_RESET_PARAMS) as it seems
! * Microsoft Vistual FoxPro 9 needs it
! */
! if (old_status != STMT_FINISHED)
! {
! if (ci->drivers.cancel_as_freestmt)
! {
! hmodule = GetModuleHandle("ODBC32");
! addr = GetProcAddress(hmodule, "SQLFreeStmt");
! result = addr((char *) (stmt->phstmt) - 96, SQL_RESET_PARAMS);
! }
! else
! result = PGAPI_FreeStmt(hstmt, SQL_RESET_PARAMS);
!
! mylog("PGAPI_Cancel: PGAPI_FreeStmt(SQL_RESET_PARAMS) returned %d\n", result);
! }
! #endif

SC_clear_error(hstmt);
return SQL_SUCCESS;
***************
*** 741,750 ****

/* In the middle of SQLParamData/SQLPutData, so cancel that. */

! /* Free all previous data-at-exec buffers */
! mylog("%s: Clearing parameters et al.\n", func);

! SC_free_params(stmt, STMT_FREE_PARAMS_ALL);
stmt->data_at_exec = -1;
stmt->current_exec_param = -1;
stmt->put_data = FALSE;
--- 791,812 ----

/* In the middle of SQLParamData/SQLPutData, so cancel that. */

! #ifdef WIN32
! /*
! * Brute force method calls SQLFreeStmt(SQL_RESET_PARAMS) as it seems
! * Microsoft Vistual FoxPro 9 needs it
! */
! if (ci->drivers.cancel_as_freestmt)
! {
! hmodule = GetModuleHandle("ODBC32");
! addr = GetProcAddress(hmodule, "SQLFreeStmt");
! result = addr((char *) (stmt->phstmt) - 96, SQL_RESET_PARAMS);
! }
! else
! result = PGAPI_FreeStmt(hstmt, SQL_RESET_PARAMS);

! mylog("PGAPI_Cancel: PGAPI_FreeStmt(SQL_RESET_PARAMS) returned %d\n", result);
! #endif
stmt->data_at_exec = -1;
stmt->current_exec_param = -1;
stmt->put_data = FALSE;

--OBd5C1Lgu00Gd/Tn
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--OBd5C1Lgu00Gd/Tn--

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> Dave I propose to rollback your patch for the first Andrus's problem
> as it breaks ODBC specification (if I understand this well):
> | After SQLExecute or SQLExecDirect returns SQL_NEED_DATA and before
> | data has been sent for all data-at-execution parameters,
> | an application can call SQLCancel to cancel the statement execution.
> | After the statement has been canceled, the application can call
> | SQLExecute or SQLExecDirect again.
>
> I think bind parameters can't be destroyed in SQLCancel.
>
> I attach patch which may solve Andrus's problem and change a little
> Dave's patch. I see application may call FreeStmt(SQL_CLOSE) and
> FreeStmt(SQL_RESET_PARAMS). But VFP doesn't call it.

Ludek, thank you.

It's very sad that Visual FoxPro cannot be used with Postgres ODBC driver
release versions.

I havent seen this problem with MySQL, Oracle and Microsoft SQL ODBC
drivers.
So it seems that those drivers do not follow ODBC driver specifications?
As you wrote it may be possible to study MySQL ODBC driver source code.

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> > I think bind parameters can't be destroyed in SQLCancel.
> >
> > I attach patch which may solve Andrus's problem and change a little
> > Dave's patch. I see application may call FreeStmt(SQL_CLOSE) and
> > FreeStmt(SQL_RESET_PARAMS). But VFP doesn't call it.
>
> Ludek, thank you.

Does it work ok? I'm sorry but I was unable to reproduce it here.

> It's very sad that Visual FoxPro cannot be used with Postgres ODBC driver
> release versions.

Don't worry. I'm planning release VFP version with each release version
and development snapshot til we add support for it to the code.

> I havent seen this problem with MySQL, Oracle and Microsoft SQL ODBC
> drivers.
> So it seems that those drivers do not follow ODBC driver specifications?
> As you wrote it may be possible to study MySQL ODBC driver source code.

I took a look at it. It unset (not free) all parameters in
FreeStmt(SQL_CLOSE). But I'm not sure if it is the way we want.
There is only this about it:
| SQL_ CLOSE: Closes the cursor associated with StatementHandle
| (if one was defined) and discards all pending results. The application
| can reopen this cursor later by executing a SELECT statement again
| with the same or different parameter values. If no cursor is open,
| this option has no effect for the application. SQLCloseCursor can also
| be called to close a cursor.
And this is for SQLCancel:
| In ODBC 2.x, if an application calls SQLCancel when no processing
| is being done on the statement, SQLCancel has the same effect as
| SQLFreeStmt with the SQL_CLOSE option; this behavior is defined only
| for completeness and applications should call SQLFreeStmt or
| SQLCloseCursor to close cursors.
I see no freeing parameters at all. It is even for ODBC 2.x.
We don't need rebind parameters now in some cases and I think it is
good behaviour. I vote to include this only as soon as we create new
option for it.

But Dave could have another opinion from mine ...

I create test suite for this problem. If I have time I run it againist
another DB. But in this case I have less time for psqlodbc development.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

>> > I attach patch which may solve Andrus's problem and change a little
>> > Dave's patch. I see application may call FreeStmt(SQL_CLOSE) and
>> > FreeStmt(SQL_RESET_PARAMS). But VFP doesn't call it.
>>
> Does it work ok?

I installed those new dlls to my system. I'm unable to reproduce the crash
after it.
So it seems working.

> I'm sorry but I was unable to reproduce it here.

I was able reproduce crash in two different XP Pro computers. If you want we
can work to make repro to work in your computer. However, since this patch
seems to fix it there may be no reason for this.

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> > Does it work ok?
>
> I installed those new dlls to my system. I'm unable to reproduce the crash
> after it.
> So it seems working.
>
> > I'm sorry but I was unable to reproduce it here.
>
> I was able reproduce crash in two different XP Pro computers. If you want we

I don't say the problem is somewhere else. I see the problem in mylog
output you sent me. I can't only test it with you repro here.

> can work to make repro to work in your computer. However, since this patch
> seems to fix it there may be no reason for this.

There is no reason to waste our time with this.

I'm sorry that I asked you. I read private from you after mailing list
message.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

=20

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Ludek Finstrle
> Sent: 14 December 2005 01:11
> To: Andrus Moor
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Access violation C5 error on Visual=20
> FoxPro SQLEXEC() call after error
>=20
> Sun, Dec 11, 2005 at 11:50:33AM +0200, Andrus Moor napsal(a):
> > Driver: psqlodbc-8_01_0104.zip ( I think)
> >=20
> > Platform: Visual FoxPro 09.00.0000.3504 for Windows [Nov 4=20
> 2005 17:39:44]
> >=20
> > Any idea how to fix this ?
>=20
> I tried to fix it but this seems to me as VFP bad behaviour. I'm
> againist add this code to official release unless we make new option
> in configure DSN dialog.

We need to be reducing the number of options, not increasing them if at
all possible!

> Dave I propose to rollback your patch for the first Andrus's problem
> as it breaks ODBC specification (if I understand this well):
> | After SQLExecute or SQLExecDirect returns SQL_NEED_DATA and before
> | data has been sent for all data-at-execution parameters,
> | an application can call SQLCancel to cancel the statement execution.
> | After the statement has been canceled, the application can call
> | SQLExecute or SQLExecDirect again.
>=20
> I think bind parameters can't be destroyed in SQLCancel.

Yeah, after re-reading it, I think you're right. :-(

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

>> I tried to fix it but this seems to me as VFP bad behaviour. I'm
>> againist add this code to official release unless we make new option
>> in configure DSN dialog.
>
> We need to be reducing the number of options, not increasing them if at
> all possible!

>> I think bind parameters can't be destroyed in SQLCancel.
>
> Yeah, after re-reading it, I think you're right. :-(

Any other ODBC driver works with Visual FoxPro without needing special
configuration.
Can we find a way to implement this in pgodbc also ?

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> >> I think bind parameters can't be destroyed in SQLCancel.
> >
> > Yeah, after re-reading it, I think you're right. :-(
>
> Any other ODBC driver works with Visual FoxPro without needing special
> configuration.
> Can we find a way to implement this in pgodbc also ?

I have one solution in my mind and Hiroshi Inoue point me that the code
is close ready. I'll try it over weekend.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> We need to be reducing the number of options, not increasing them if at
> all possible!

I don't agree as administrator :-) It's better for users to just click
some options instead of studying special commands.

But you're right in this case. There is anothor way to solve the
problem and I'm going to test it.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

=20

> -----Original Message-----
> From: Ludek Finstrle [mailto:luf@pzkagis.cz]=20
> Sent: 16 December 2005 16:00
> To: Dave Page
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Access violation C5 error on Visual=20
> FoxPro SQLEXEC() call after error
>=20
> > We need to be reducing the number of options, not=20
> increasing them if at
> > all possible!
>=20
> I don't agree as administrator :-) It's better for users to just click
> some options instead of studying special commands.

I think you misunderstand - I want to avoid having any options/commands.
Zero configuration if possible.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

> > > We need to be reducing the number of options, not
> > increasing them if at
> > > all possible!
> >
> > I don't agree as administrator :-) It's better for users to just click
> > some options instead of studying special commands.
>
> I think you misunderstand - I want to avoid having any options/commands.
> Zero configuration if possible.

I understand you well. I hate zero configurations. If man knows what he
makes it's better to have options. If I want easiely change something
(e.g. client encoding) it's better when driver support combo-box with
encoding in opposite to study command (SET client_encoding).

For this reason I like Linux. If I want I can set it to fit my idea.
With zero configuration it is impossible.

I don't want create flame. I like when people around me know my opinion.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Access violation C5 error on Visual FoxPro SQLEXEC() call after error

--8GpibOaaTibBMecb
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

> > Can we find a way to implement this in pgodbc also ?
>
> I have one solution in my mind and Hiroshi Inoue point me that the code
> is close ready. I'll try it over weekend.

I think I found the solution. I remove Dave's patch and this solution
works with first Andrus repro app. So I hope it's good enough.
I sent it in another thread. I don't know if Hiroshi is monitoring
all posts.
Please review and comment attached patch.

Thanks to Hiroshi for kicking me

Luf

--8GpibOaaTibBMecb
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="psqlodbc-limit_bind_param.diff"

diff -c psqlodbc.orig\execute.c psqlodbc\execute.c
*** psqlodbc.orig\execute.c Tue Dec 06 21:53:30 2005
--- psqlodbc\execute.c Fri Dec 16 18:11:16 2005
***************
*** 542,547 ****
--- 542,548 ----
UInt4 offset = apdopts->param_offset_ptr ? *apdopts->param_offset_ptr : 0;
Int4 bind_size = apdopts->param_bind_type;
Int4 current_row = stmt->exec_current_row < 0 ? 0 : stmt->exec_current_row;
+ SWORD param_count;

/*
* Increment the number of currently processed rows
***************
*** 549,555 ****
if (ipdopts->param_processed_ptr)
(*ipdopts->param_processed_ptr)++;
stmt->data_at_exec = -1;
! for (i = 0; i < apdopts->allocated; i++)
{
Int4 *pcVal = apdopts->parameters[i].used;

--- 550,559 ----
if (ipdopts->param_processed_ptr)
(*ipdopts->param_processed_ptr)++;
stmt->data_at_exec = -1;
! /* Check bind parameters count */
! if (SQL_SUCCESS != PGAPI_NumParams(stmt, ¶m_count))
! return SQL_ERROR;
! for (i = 0; i < param_count; i++)
{
Int4 *pcVal = apdopts->parameters[i].used;

***************
*** 740,750 ****
}

/* In the middle of SQLParamData/SQLPutData, so cancel that. */
-
- /* Free all previous data-at-exec buffers */
- mylog("%s: Clearing parameters et al.\n", func);
-
- SC_free_params(stmt, STMT_FREE_PARAMS_ALL);
stmt->data_at_exec = -1;
stmt->current_exec_param = -1;
stmt->put_data = FALSE;
--- 744,749 ----

--8GpibOaaTibBMecb
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--8GpibOaaTibBMecb--