vfp connect to postgres view error

I am using database postgres 9.01 and front end with vfp9. By odbc version 9.0.2 I have a problem using the SUM in view.  It already come up with Fatal Error C000005...(it is not everytime, but usually, for example, the first time it run without problem, but run again will occur such error)

My test:
1.My PCs are windows XP.
2. I have try vfp8 and 9 also have such error.
3. if use odbc 8.04, it have no such error, but it always  hang when 'use'  or 'require' a view in runnig a application. (but if I insert a susp before the 'use', and resume, then it can run without problem.  

I also try to use spt instead of view.  but the sum result have not show the value.  
ie.  use spt to sqlexec command: select sum(qty) as amt from govu
the result only show a blank in column amt, list the table property, the column amt is N(0,2)...
(in vfp6 it should be store in a memo field)

Can any one can help and or have any other way to get the sum value ?

Thanks in advance

Who is Participating?
pcelbaConnect With a Mentor Commented:
SPT is not easy to use after mass data updates in a grid...

I am doing it in two ways:
1) Mark each updated line by a flag and pass all changes to the server after "Save" button click
2) Call SPT update in the AfterRowColChange method

Both ways require more coding than simple view but it is your decision to use Postgre which has more incompatibilities than e.g. SQL Server Express.
My observation: Various Postgre versions have various incompatibilities...

Sum works for me in VFP 9, SPT, Postgre 8.4, and driver version 8.03.04. Did you try to create the log file from the SPT command (Global options in ODBC).

Could you post a script which will create and populate values in govu table to test the behavior here?

You may also try:

select CAST(sum(qty) AS numeric(10,2)) as amt from govu
dickywktAuthor Commented:
Thanks for you help.

Select CAST(sum(qty) AS numeric(10,2)) as amt from govu
is work!

So my situration come to the View usage...

Do you have any idea? Otherwise I have to change my code to use spt.

Other than the view use SUM.  In use odbc 8.3.04, it always  hang when 'use'  or 'require' a view in runnig a application.  It this, the view have no sum, it may only get some records.  (of case, it doesn't a seriour problem since odbc 9 work fine, but I am only strange for it)


I cannot say why it hangs on SUM in your version. Views have always been tricky to me. E.g. updates crashed depending on data even on MS SQL Server. I am rather using SPT which gives me more control over data and better error handling.

Simply test everything and use what is working for you... Microsoft does not help in this case, Postgre authors could be more flexible but who knows...

dickywktAuthor Commented:
the situation are:

1. following occur in with odbc 9 only, if odbc 8 no such problem:
view are work almost normal (in requery and update) other than included 'SUM' which may go crash  with c000005
(as I m

2. following occur in with odbc 8 only, if odbc 9 no such problem:
request a view or tableupdate a view make vfp freeze and need to kill, but in tableupdate case, the data can successfully updated to the table.

Anyway, if it have no solution to the 'view' right now, I will start to rewite my code to spt.

It is right,  views are ticky but it is simply especially when handle the data entry in a grid, or I need to used to the spt codeing...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.