Solved

vfp connect to postgres view error

Posted on 2011-03-21
5
778 Views
Last Modified: 2012-08-14
hi,
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


 
0
Comment
Question by:dickywkt
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:pcelba
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
0
 

Author Comment

by:dickywkt
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)

 

0
 
LVL 41

Expert Comment

by:pcelba
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...

0
 

Author Comment

by:dickywkt
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...
0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now