Re: Serious errors with 'Create view' command

I take your point.

However the REASON I thought it should work on 'opening' the database was
because that is PRECISELY the text that appeared INSIDE the stored procedures
duing one of my attempts. I was not aware of having typed this myself and I
had assumed (wrongly, it seems) that something in-built in Fox must have
created that entry itself. I display the 'offending' text for your interest:-

"PROCEDURE dbc_OpenData(cDatabaseName, lExclusive, lNoupdate, lValidate)
*Immediately after DBC is opened.
Create Sql View combinedview;
AS Select inventory.inumbr, inventory.divno, inventory.deptno,
inventory.subdeptno, inventory.classno ;
FROM inventory ;
WHERE (?mydivno <= 0 Or divno = ?mydivno);
AND (?mydeptno <= 0 Or deptno = ?mydeptno);
AND (?mysubdeptno <= 0 Or subdeptno = ?mysubdeptno);
AND (?myclassno <= 0 Or classno = ?myclassno)

ENDPROC"

You'll notice the words "*Immediately after DBC is opened."

I appreciate your help in all these things but it's pretty damned hard to
'learn how to use the product' when it does NOT perform in the manner
described in the manual, the help pages, nor in line with the design of the
product! Why should I have asssumed that an SQL statement which works
PERFECTLY in in VFP7 wiould NOT work when using one of VFP7's tools to create
a view?

It looks as though, with the help of all you guys that I may have got a
solution to the problem. Tom showed the 'code' that was generated by VFP9
when he used my 'code' to Create a View. It's significantly different to what
I asked but it IS functional! If I paste 'his' code (from VFP9) into a new
Create View statement in VFP7, it appears to work correctly! Clearly, if I do
something similar in future, I'll need to perform the same trick; build it in
VFP7, run it through VFP9 and repaste it into VFP7 again! Hardly a
recommendation of the product, though!

Again, I thank you for your help and interest!

Ian


"Dan Freeman" wrote:

You should take the time to learn how the product works before whining about
how bad it is.

Stored procedures are nothing more than procedures that are stored in the
database. What on EARTH makes you think they should run automatically when
the database is opened? There's no database on earth that behaves that way!

Dan


Barley Man wrote:
Thanks for trying anders but I am using VFP7 which has a version of
ViewSql that is read only!

I can't use the technique of writing 16 possible views as I can't
seem to find a way of persuading them to 'fill' the 'grid' that I am
using in a form to display the selected SKUs!

I was persuaded by people on this Forum to USE this technique of a
View 'driving' a grid against my better judgement! If there is a way
of Creating a form with a 'grid that can be 'fed' by multiple 'views'
I am damned if I can see how to do it!

What on EARTH does the option of 'Edit Stored procedures' do? It sesm
that I should be able to paste my WORKING code into that 'section'
and it should regenerate the view each time I open the database. If
it IS supposed to do that, why DOESN'T it?

The more I use Visual Fox, the less I am impressed with it as a
product! Microsoft have taken a RELIABLE and WORKABLE version of Fox
and made it increasingly unstable, non-intuitive and unusable in my
opinion!

"AA" wrote:

In VFP9 the view sql window is readwrite. And in VFP9 the brackets
VFP inserts are different from what you showed.

A possible solution is to let the user create ad hoc views in a
temporary database. That way you aren't forced into creating a
single view that can handle any possible combination of 1, 2 ,3 or 4
columns. If you want classes=25 create the view
Create view Combinedview AS SELECT * FROM Inventory WHERE
classes=25. It' also possible to create 16 views to cover all
contingences and then always open one with USE viewX ALIAS
Combinedview -Anders

"Barley Man" <BarleyMan@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i
meddelandet
news:B18419CD-0642-487F-BAA5-7814AB25481E@xxxxxxxxxxxxxxxx
Hi Tom

I can't get the view designer to create the coding that I need,
either! It doesn't come out with the same brackets. How are you
suggesting that I cut and paste it? It's the 'text' in the view
designer 'sql view' that's WRONG in
the first place and thus cutting and pasting that would just repeat
the problem. To where are you suggesting tyhat I 'paste' the
cut-out anyway? I can't paste it INTO the view designer sql view
window as that's Read Only!

There is the facility to 'edit stored procedures' but I can't seem
to make that do ANYTHING!

ian



"tom knauf" wrote:

Hi,

I would create the view in the viewdesigner and cut/paste the code
generated
by the viewdesigner (right click - view sql)

HTH
Tom


"Barley Man" <BarleyMan@xxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im
Newsbeitrag
news:A4DB29BB-1787-4E90-83A6-2209790A74EA@xxxxxxxxxxxxxxxx
I have used the following 'create view' statment:-

Create Sql View combinedview;
AS Select inventory.inumbr, inventory.divno, inventory.deptno,
inventory.subdeptno, inventory.classno, ;
inventory.istyl;
FROM inventory ;
WHERE (?mydivno <= 0 Or divno = ?mydivno);
AND (?mydeptno <= 0 Or deptno = ?mydeptno);
AND (?mysubdeptno <= 0 Or subdeptno = ?mysubdeptno);
AND (?myclassno <= 0 Or classno = ?myclassno)

However, when I 'inspect' (modify) the created View by looking at
the sql,
the 'view' created is NOT the same as the one I requtested and
appears as
follows:-

SELECT Inventory.inumbr, Inventory.divno, Inventory.deptno,;
Inventory.subdeptno, Inventory.classno, Inventory.istyl;
FROM drilldown2!inventory;
WHERE ?mydivno <= 0;
OR (Inventory.divno = ?mydivno;
AND ?mydeptno <= 0);
OR (Inventory.deptno = ?mydeptno;
AND ?mysubdeptno <= 0);
OR (Inventory.subdeptno = ?mysubdeptno;
AND ?myclassno <= 0);
OR (Inventory.classno = ?myclassno)

The change is not immediately obvious to see but it IS there. The
positioning (and thus the effects) of the brackets have changed
SUBSTANTIALLY
and the routine simply fails to perform properly. I need the EXACT
functionality of the statement contained within the 'create
view'instructions
but I simply can't get it to 'transfer' into the database as a
'view'!

It does not appear to be a simple error in the 'view sql' routine
as the
view BEHAVES EXACTLY in line with the version being displayed by
'view sql'
and that's NOT what I want, nor what I requested!

What on EARTH am I supposed to do about THAT?

Ian



.

Relevant Pages

  • Re: Serious errors with Create view command
    ... the database is opened? ... There's no database on earth that behaves that way! ... What on EARTH does the option of 'Edit Stored procedures' do? ... I can't paste it INTO the view designer sql view ...
    (microsoft.public.fox.helpwanted)
  • Re: Transaction Oriented Architecture (TOA)
    ... OOP is not required to wrap SQL. ... of stored procedures to act as an API for application programmers ... writing code against the database. ... With a well-defined API in place, ...
    (comp.object)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: Get all stored procedures
    ... For SQLSERVER, we can use the following SQL statement to query the SP ... objects of a certain database: ... Microsoft Online Support ... | Thread-Topic: Get all stored procedures ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cannot Open SQL Server Table in Access.ADP File.
    ... I dont think that SQL Server will take 'veiw dependencies' into effect; ... Generate Script Wizard did not work on my original database, ... After fixing some of my tables and a few stored procedures and views, ...
    (microsoft.public.access.adp.sqlserver)