Problems with Views 
Author Message
 Problems with Views

I have an app with remote views connected to two external databases through
odbc.

As backround:
During development, I was working with copies of the client's data from two
databases (which I extracted on the clients machine through ODBC.  I then
loaded this into vfp databases and connected through ODBC in my test
environment).  To install the program, I had to load the whole project onto
the clients machine and reset up most of the views and change the field
names which were truncated when I queried the extracts.  An earlier
functioning version of this application exists in a separate directory
although the project has the same name.

All of the queries in the remote views work perfectly.  I then have a number
of local views that combine some of the rvs with my main database.
Yesterday when I set these up, the local views seemed to work ok.  Today
I've got two different sorts of problems that have cropped up.

Although all of the rvs work fine, some of the lvs now won't work and won't
let me modify them.  I get the error that one or more of the rvs can't be
found in the database although the offending views show up in the project
manager and can be browsed.  Is there someway I can add these views to the
db?  In adding the remote views could they have been added to the other
database in the other directory?  How do I prevent this?

Another older lv that worked fine before now reports that it can't find a
field that was added to the main vfp database and also to the view.  I can
sometimes open this view with modify and the offending field name is where
it should be.  Removing this field and readding it doesn't help.  This
worked fine on my machine.  What could I have done.

Other that reconstucting the views (I'm not sure that that will work unless
I figure this out) what could I do to remedy this?

Thanks in advance for your help.



Sun, 23 Feb 2003 23:43:06 GMT  
 Problems with Views
Kevin,

Cannot help you much.  One thing I've learnt though is to create views via a
procedure rather than using the designer.
This enables you to modify the views as many times as you like, transport
them from one db to another and helps with 'accidents of all sorts'

here's a bit of code that may help
one proc is a sample for a remote view whilst the other is for a local view
in both cases the name of the view is the name of the proc
just executing the proc will (re)write the view in the db
*---------------------------------------------------------------------------
function view_delete(v)
 if( indbc( v, 'View' ) )
  delete view ( v )
 endif
endfunc
*---------------------------------------------------------------------------
procedure r_myremoteview()
 =view_delete(program())
 create sql view program() REMOTE Connection bkh as ;
 ;
 SELECT * ;
 FROM BHD.BHBTWF00 Bhbtwf00 ;
 ORDER by Bhbtwf00.KLANTN, Bhbtwf00.JAAR

 =dbsetprop('r_myremoteview.VATNR', 'FIELD', 'DATATYPE', 'I')
 =dbsetprop('r_myremoteview.STATUS', 'FIELD', 'DATATYPE', 'I')
....
** ---- IMPORTANT (for me at least)
 =dbsetprop('r_myremoteview', 'VIEW', 'FetchSize', -1)
 =dbsetprop('r_myremoteview, 'VIEW', 'MaxRecords', -1)
endproc
*-------------------------------------------------------------------------
procedure TaskStatus()

 =view_delete(program())

 create sql view program() as ;
 ;
 select * ;
 from Task ,TaskHistory ;
 where ( TimeToChar(t_DateDue) <= TimeToChar(?TaskStatusUpTo) ) ;
  and ( TimeToChar(t_id) == TimeToChar(th_t_id)) ;
  and ( (TimeToChar(th_t_id)+TimeToChar(th_id)) ;
    IN ( select TimeToChar(th_t_id) + max(TimeToChar(th_id) ) ;
      from TaskHistory ;
      where ( TimeToChar(th_id) <= TimeToChar(?TaskStatusUpTo) ) ;
      group by th_t_id ;
     ) ;
 order by 8

** copy the captions of the base tables
 local i, n, af[1], xx
 select Task
 n = afields(af)
 for i = 1 to n
  xx = dbgetprop(alias()+'.'+af[i,1], 'Field', 'Caption')
  =DBSETPROP(program()+'.'+af[i,1], 'Field', 'Caption', xx)
 endfor

 select TaskHistory
 n = afields(af)
 for i = 1 to n
  xx = dbgetprop(alias()+'.'+af[i,1], 'Field', 'Caption')
  =DBSETPROP(program()+'.'+af[i,1], 'Field', 'Caption', xx)
 endfor

 use in Task
 use in TaskHistory
endproc
*--------------------------------------------------------------------------



Wed, 26 Feb 2003 22:59:22 GMT  
 Problems with Views

Hi
When is it necessary to have UDF TimeToChar considering there are several
intrinsic VFP functions for it?
-Anders

Quote:
> Kevin,

> Cannot help you much.  One thing I've learnt though is to create views via
a
> procedure rather than using the designer.
> This enables you to modify the views as many times as you like, transport
> them from one db to another and helps with 'accidents of all sorts'

> here's a bit of code that may help
> one proc is a sample for a remote view whilst the other is for a local
view
> in both cases the name of the view is the name of the proc
> just executing the proc will (re)write the view in the db

*---------------------------------------------------------------------------
Quote:
> function view_delete(v)
>  if( indbc( v, 'View' ) )
>   delete view ( v )
>  endif
> endfunc

*---------------------------------------------------------------------------
Quote:
> procedure r_myremoteview()
>  =view_delete(program())
>  create sql view program() REMOTE Connection bkh as ;
>  ;
>  SELECT * ;
>  FROM BHD.BHBTWF00 Bhbtwf00 ;
>  ORDER by Bhbtwf00.KLANTN, Bhbtwf00.JAAR

>  =dbsetprop('r_myremoteview.VATNR', 'FIELD', 'DATATYPE', 'I')
>  =dbsetprop('r_myremoteview.STATUS', 'FIELD', 'DATATYPE', 'I')
> ....
> ** ---- IMPORTANT (for me at least)
>  =dbsetprop('r_myremoteview', 'VIEW', 'FetchSize', -1)
>  =dbsetprop('r_myremoteview, 'VIEW', 'MaxRecords', -1)
> endproc
> *-------------------------------------------------------------------------
> procedure TaskStatus()

>  =view_delete(program())

>  create sql view program() as ;
>  ;
>  select * ;
>  from Task ,TaskHistory ;
>  where ( TimeToChar(t_DateDue) <= TimeToChar(?TaskStatusUpTo) ) ;
>   and ( TimeToChar(t_id) == TimeToChar(th_t_id)) ;
>   and ( (TimeToChar(th_t_id)+TimeToChar(th_id)) ;
>     IN ( select TimeToChar(th_t_id) + max(TimeToChar(th_id) ) ;
>       from TaskHistory ;
>       where ( TimeToChar(th_id) <= TimeToChar(?TaskStatusUpTo) ) ;
>       group by th_t_id ;
>      ) ;
>  order by 8

> ** copy the captions of the base tables
>  local i, n, af[1], xx
>  select Task
>  n = afields(af)
>  for i = 1 to n
>   xx = dbgetprop(alias()+'.'+af[i,1], 'Field', 'Caption')
>   =DBSETPROP(program()+'.'+af[i,1], 'Field', 'Caption', xx)
>  endfor

>  select TaskHistory
>  n = afields(af)
>  for i = 1 to n
>   xx = dbgetprop(alias()+'.'+af[i,1], 'Field', 'Caption')
>   =DBSETPROP(program()+'.'+af[i,1], 'Field', 'Caption', xx)
>  endfor

>  use in Task
>  use in TaskHistory
> endproc

*--------------------------------------------------------------------------

- Show quoted text -



Thu, 27 Feb 2003 00:47:08 GMT  
 Problems with Views

Quote:
> I have an app with remote views connected to two external databases
through
> odbc.

> As backround:
> During development, I was working with copies of the client's data from
two
> databases (which I extracted on the clients machine through ODBC.  I then
> loaded this into vfp databases and connected through ODBC in my test
> environment).  To install the program, I had to load the whole project
onto
> the clients machine and reset up most of the views and change the field
> names which were truncated when I queried the extracts.  An earlier
> functioning version of this application exists in a separate directory
> although the project has the same name.

> All of the queries in the remote views work perfectly.  I then have a
number
> of local views that combine some of the rvs with my main database.
> Yesterday when I set these up, the local views seemed to work ok.  Today
> I've got two different sorts of problems that have cropped up.

> Although all of the rvs work fine, some of the lvs now won't work and
won't
> let me modify them.  I get the error that one or more of the rvs can't be
> found in the database although the offending views show up in the project
> manager and can be browsed.  Is there someway I can add these views to the
> db?  In adding the remote views could they have been added to the other
> database in the other directory?  How do I prevent this?

> Another older lv that worked fine before now reports that it can't find a
> field that was added to the main vfp database and also to the view.  I can
> sometimes open this view with modify and the offending field name is where
> it should be.  Removing this field and readding it doesn't help.  This
> worked fine on my machine.  What could I have done.

> Other that reconstucting the views (I'm not sure that that will work
unless
> I figure this out) what could I do to remedy this?

The problem seems a little confusing. You maybe having a few problems
actually. It's kind of hard to tell without knowing what you mean by
extracted the data thru ODBC and loaded it into VFP.  Also, you don't tell
us what is the difference between the time the second problem works and when
it doesn't.

Are you still connecting to your client's data thru ODBC? What is the client
data? VFP? SQL? Are you sure your datasource for the remote views is
available? Is it possible you have two databases open at the same time with
the same view names?



Thu, 27 Feb 2003 10:14:57 GMT  
 Problems with Views
Anders,

You are right.  There's TTOC(..., 1) returning 14 chars whereas TimeToChar()
returns 4.
Having a lot of records to index I have come up with TimeToChar().  (The
Collating sequence must be MACHINE for that index)

function TimeToChar(t) && range 2000 + 136 years
 return bintoc( round(m.t - {01/01/2000 00:00:00},0) )
endfunc


| Hi
| When is it necessary to have UDF TimeToChar considering there are several
| intrinsic VFP functions for it?



Thu, 27 Feb 2003 18:09:03 GMT  
 Problems with Views
I see. But there'll be some of processing overhead to save the four bytes.
BTW, I had to change it tobintoc( round(m.t ,DATETIME(2000,1,1,0,0,0),0) to
make it compatible with both Set Strictdate to 2 and Set Date to <anything
other than MDY>.
I misunderstood the name and thought it was a function to extract the time
from a datetime, but it was a function to turn a datatime value into an
integer. Thanks
-Anders


Quote:
> Anders,

> You are right.  There's TTOC(..., 1) returning 14 chars whereas
TimeToChar()
> returns 4.
> Having a lot of records to index I have come up with TimeToChar().  (The
> Collating sequence must be MACHINE for that index)

> function TimeToChar(t) && range 2000 + 136 years
>  return bintoc( round(m.t - {01/01/2000 00:00:00},0) )
> endfunc



> | Hi
> | When is it necessary to have UDF TimeToChar considering there are
several
> | intrinsic VFP functions for it?



Thu, 27 Feb 2003 20:26:04 GMT  
 Problems with Views

Anders,

Yes, there will be processing overhead. It's a trade off between space and
time.

BTW {^2000/01/01 00:00:00} vs DATETIME(2000,1,1,0,0,0) is strictdate and
goes about twice as fast since DateTime() is a function call

gregory
-------------

| I see. But there'll be some of processing overhead to save the four bytes.
| BTW, I had to change it tobintoc( round(m.t ,DATETIME(2000,1,1,0,0,0),0)
to
| make it compatible with both Set Strictdate to 2 and Set Date to <anything
| other than MDY>.
| I misunderstood the name and thought it was a function to extract the time
| from a datetime, but it was a function to turn a datatime value into an
| integer. Thanks
| -Anders
|


| > Anders,
| >
| > You are right.  There's TTOC(..., 1) returning 14 chars whereas
| TimeToChar()
| > returns 4.
| > Having a lot of records to index I have come up with TimeToChar().  (The
| > Collating sequence must be MACHINE for that index)
| >
| > function TimeToChar(t) && range 2000 + 136 years
| >  return bintoc( round(m.t - {01/01/2000 00:00:00},0) )
| > endfunc
| >


| > | Hi
| > | When is it necessary to have UDF TimeToChar considering there are
| several
| > | intrinsic VFP functions for it?



Fri, 28 Feb 2003 01:00:11 GMT  
 Problems with Views

Yes, but we're talking about a few millionth of a second.
-Anders


Quote:
> Anders,

> Yes, there will be processing overhead. It's a trade off between space and
> time.

> BTW {^2000/01/01 00:00:00} vs DATETIME(2000,1,1,0,0,0) is strictdate and
> goes about twice as fast since DateTime() is a function call

> gregory
> -------------



Fri, 28 Feb 2003 06:41:46 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Problem with view from MSACCESS

2. Refreshing problem with View

3. Problem with view and deleted records

4. Problem with view and 'sendUpdates'

5. Problem with views!!!!

6. Problem with view...

7. Problem with Views

8. Problem with Views

9. Problems with views not saving all changes.

10. Problem with views

11. Problem with Views

12. Problem with view and 'sendUpdates'

 

 
Powered by phpBB® Forum Software