vfp connect to postgres view error

Posted on 2011-03-21
Medium Priority
Last Modified: 2012-08-14
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

Question by:dickywkt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 42

Expert Comment

ID: 35181322
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

Author Comment

ID: 35182922
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)


LVL 42

Expert Comment

ID: 35183054
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...


Author Comment

ID: 35185900
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...
LVL 42

Accepted Solution

pcelba earned 2000 total points
ID: 35188042
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.

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question