CREATE SQL VIEW REMOTE (gets all records, Why?)

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Michael Nei » Wed, 05 Feb 2003 14:48:43



To create a view of a subset of remote data stored on a Linux server
using MySQL, I use the following VFP 7 sample command:

OPEN DATABASE MyData
CREATE SQL VIEW MyView REMOTE CONNECTION MyRemote as ;
SELECT title FROM titles WHERE title=?xTitle Limit 10

(FYI the titles table has 1.5 million records and lots of fields)

The system then stalls (waits)...
When I examine a server process list (using the tool in MySQLFront),
the server is busy processing the folling request:

SELECT * FROM titles <--- Yikes!

Obviously, this step is going to take a while to download this HUGE
file. My question is why does VFP need to get all the records in the
table when I am only CREATING the view? Is it the fact that "Limit 10"
is not native to VFP? I can't use "TOP 10" because MySQL does not
understand that. None of that should matter anyway because the first
command VFP sends (by itself) is "SELECT * FROM titles"

I am wondering if VFP and MySQL are not compatible in some way (can
anyone verify this situation on their system?). I have tried to solve
this for months, and it seems odd that I am the only one out there who
seems to be having an issue with such a basic operation. Help!

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by tOmputO » Wed, 05 Feb 2003 22:45:15


Read this section in VFP7.0
Search for : 'Speeding Up Data Retrieval'

Tom

Quote:>-----Original Message-----
>To create a view of a subset of remote data stored on a
Linux server
>using MySQL, I use the following VFP 7 sample command:

>OPEN DATABASE MyData
>CREATE SQL VIEW MyView REMOTE CONNECTION MyRemote as ;
>SELECT title FROM titles WHERE title=?xTitle Limit 10

>(FYI the titles table has 1.5 million records and lots
of fields)

>The system then stalls (waits)...
>When I examine a server process list (using the tool in
MySQLFront),
>the server is busy processing the folling request:

>SELECT * FROM titles <--- Yikes!

>Obviously, this step is going to take a while to
download this HUGE
>file. My question is why does VFP need to get all the
records in the
>table when I am only CREATING the view? Is it the fact
that "Limit 10"
>is not native to VFP? I can't use "TOP 10" because MySQL
does not
>understand that. None of that should matter anyway
because the first
>command VFP sends (by itself) is "SELECT * FROM titles"

>I am wondering if VFP and MySQL are not compatible in
some way (can
>anyone verify this situation on their system?). I have
tried to solve
>this for months, and it seems odd that I am the only one
out there who
>seems to be having an issue with such a basic operation.
Help!
>.


 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Rush Stron » Thu, 06 Feb 2003 03:08:49


TITLE is a reserved word in Fox.  Does the same behavior occur if you SELECT
on a different field?

 - Rush


Quote:> To create a view of a subset of remote data stored on a Linux server
> using MySQL, I use the following VFP 7 sample command:

> OPEN DATABASE MyData
> CREATE SQL VIEW MyView REMOTE CONNECTION MyRemote as ;
> SELECT title FROM titles WHERE title=?xTitle Limit 10

> (FYI the titles table has 1.5 million records and lots of fields)

> The system then stalls (waits)...
> When I examine a server process list (using the tool in MySQLFront),
> the server is busy processing the folling request:

> SELECT * FROM titles <--- Yikes!

> Obviously, this step is going to take a while to download this HUGE
> file. My question is why does VFP need to get all the records in the
> table when I am only CREATING the view? Is it the fact that "Limit 10"
> is not native to VFP? I can't use "TOP 10" because MySQL does not
> understand that. None of that should matter anyway because the first
> command VFP sends (by itself) is "SELECT * FROM titles"

> I am wondering if VFP and MySQL are not compatible in some way (can
> anyone verify this situation on their system?). I have tried to solve
> this for months, and it seems odd that I am the only one out there who
> seems to be having an issue with such a basic operation. Help!

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Anders Altber » Thu, 06 Feb 2003 08:18:45


MySQL is a client/server database so the query will not be processed by VFP
at all. VFP doesn't need any reocords at all. VFP is waiting for MySQL to
finsh it's job. If you remove LIMIT 10, how many rows do you expect on the
average?
Have you tried sending the same query by way of SQLEXEC().

-Anders


Quote:> To create a view of a subset of remote data stored on a Linux server
> using MySQL, I use the following VFP 7 sample command:

> OPEN DATABASE MyData
> CREATE SQL VIEW MyView REMOTE CONNECTION MyRemote as ;
> SELECT title FROM titles WHERE title=?xTitle Limit 10

> (FYI the titles table has 1.5 million records and lots of fields)

> The system then stalls (waits)...
> When I examine a server process list (using the tool in MySQLFront),
> the server is busy processing the folling request:

> SELECT * FROM titles <--- Yikes!

> Obviously, this step is going to take a while to download this HUGE
> file. My question is why does VFP need to get all the records in the
> table when I am only CREATING the view? Is it the fact that "Limit 10"
> is not native to VFP? I can't use "TOP 10" because MySQL does not
> understand that. None of that should matter anyway because the first
> command VFP sends (by itself) is "SELECT * FROM titles"

> I am wondering if VFP and MySQL are not compatible in some way (can
> anyone verify this situation on their system?). I have tried to solve
> this for months, and it seems odd that I am the only one out there who
> seems to be having an issue with such a basic operation. Help!

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Remus Rusan » Thu, 06 Feb 2003 12:04:05


The select * from titles is the result of VFP requesting the metadata for
the select result in order to create the view. Normaly, the ODBC driver
knows how to trasnform this request in an operation that don't execute the
actual statment on the server. E.g the SQL Server driver will wrap this in a
call that looks like this:





Probably what happens is that MySQL's ODBC driver sends the actual statement
for execution to the server.
You can hack around this problem by having a database with an empty table
connected to the server when you create the view in VFP. Once the view is
created into the DBC, you can attach the true database to the server.

HTH,
Remus


Quote:> To create a view of a subset of remote data stored on a Linux server
> using MySQL, I use the following VFP 7 sample command:

> OPEN DATABASE MyData
> CREATE SQL VIEW MyView REMOTE CONNECTION MyRemote as ;
> SELECT title FROM titles WHERE title=?xTitle Limit 10

> (FYI the titles table has 1.5 million records and lots of fields)

> The system then stalls (waits)...
> When I examine a server process list (using the tool in MySQLFront),
> the server is busy processing the folling request:

> SELECT * FROM titles <--- Yikes!

> Obviously, this step is going to take a while to download this HUGE
> file. My question is why does VFP need to get all the records in the
> table when I am only CREATING the view? Is it the fact that "Limit 10"
> is not native to VFP? I can't use "TOP 10" because MySQL does not
> understand that. None of that should matter anyway because the first
> command VFP sends (by itself) is "SELECT * FROM titles"

> I am wondering if VFP and MySQL are not compatible in some way (can
> anyone verify this situation on their system?). I have tried to solve
> this for months, and it seems odd that I am the only one out there who
> seems to be having an issue with such a basic operation. Help!

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Igor Korolyo » Thu, 06 Feb 2003 20:11:06


Hi, Remus!
You wrote  on Tue, 4 Feb 2003 19:04:05 -0800:

 RR> The select * from titles is the result of VFP requesting the metadata
 RR> for the select result in order to create the view. Normaly, the ODBC
 RR> driver knows how to trasnform this request in an operation that don't
 RR> execute the actual statment on the server. E.g the SQL Server driver
 RR> will wrap this in a call that looks like this:





 RR> Probably what happens is that MySQL's ODBC driver sends the actual
 RR> statement for execution to the server.
 RR> You can hack around this problem by having a database with an empty
 RR> table connected to the server when you create the view in VFP. Once the
 RR> view is created into the DBC, you can attach the true database to the
 RR> server.

Or maybe set connection object to something wrong? Then after defining all
the view properties set it back to valid??? Don't test, but maybe this will
help you.

WBR, Igor

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Remus Rusan » Fri, 07 Feb 2003 02:05:14


If you set the connection to something wrong, VFP won't be able to get the
metadata for the object. That means it can't know the number, name and type
of columns in the result set, nor the primary key needed for updates. Using
DBSETPROP you could set the properties of the view, but is not a trivia. I
think is much easier to conect to a server having an empty table with
identical structure, let VFP define the view, then change to the connection
to the real server. Specially if you have to define more views...
It worth a shot to check if there are any updates to the ODBC driver for
mySQL, too.

HTH,
Remus


> Hi, Remus!
> You wrote  on Tue, 4 Feb 2003 19:04:05 -0800:

>  RR> The select * from titles is the result of VFP requesting the metadata
>  RR> for the select result in order to create the view. Normaly, the ODBC
>  RR> driver knows how to trasnform this request in an operation that don't
>  RR> execute the actual statment on the server. E.g the SQL Server driver
>  RR> will wrap this in a call that looks like this:





>  RR> Probably what happens is that MySQL's ODBC driver sends the actual
>  RR> statement for execution to the server.
>  RR> You can hack around this problem by having a database with an empty
>  RR> table connected to the server when you create the view in VFP. Once
the
>  RR> view is created into the DBC, you can attach the true database to the
>  RR> server.

> Or maybe set connection object to something wrong? Then after defining all
> the view properties set it back to valid??? Don't test, but maybe this
will
> help you.

> WBR, Igor

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Michael Nei » Fri, 07 Feb 2003 03:23:19


Thank you Remus!

Finally, someone who fully understands the problem (the suggestions
from others were not related to the problem, but thanks for trying to
help).

I suspected that the problem was in the OBDC driver for MySQL. I
installed the latest OBDC version still in development, but it's no
better. Upon further testing I found the following:

CREATE SQL VIEW test REMOTE CONNECTION MyRemote as ;
SELECT title FROM titles Limit 1

works (i.e. instant results), but the "Limit 1" cannot be removed via
dbsetprop("SQL") because that control is read only. And, you cannot
paramitize with  LIMIT ?nLimit". So, whenever you USE the view only
one record is returned.

Furthermore, to make the view useful, it at least needs a WHERE
clause.
It's when I change the SELECT to;

SELECT title FROM titles WHERE title LIKE ?xTitle LIMIT 1

that is goes off getting all records (The LIMIT 1 is now ignored)

I could create the temp table as you suggest, however this table is
always open to hundreds of online users. I am using SQLExec() as the
workaround (messy), but I really wanted to take advantage of the
dozens od features that views offer (automated updating, etc.).

Should I be mentioneing this to the MyOBDC driver development team?

Michael Neill


> The select * from titles is the result of VFP requesting the metadata for
> the select result in order to create the view. Normaly, the ODBC driver
> knows how to trasnform this request in an operation that don't execute the
> actual statment on the server. E.g the SQL Server driver will wrap this in a
> call that looks like this:





> Probably what happens is that MySQL's ODBC driver sends the actual statement
> for execution to the server.
> You can hack around this problem by having a database with an empty table
> connected to the server when you create the view in VFP. Once the view is
> created into the DBC, you can attach the true database to the server.

> HTH,
> Remus



> > To create a view of a subset of remote data stored on a Linux server
> > using MySQL, I use the following VFP 7 sample command:

> > OPEN DATABASE MyData
> > CREATE SQL VIEW MyView REMOTE CONNECTION MyRemote as ;
> > SELECT title FROM titles WHERE title=?xTitle Limit 10

> > (FYI the titles table has 1.5 million records and lots of fields)

> > The system then stalls (waits)...
> > When I examine a server process list (using the tool in MySQLFront),
> > the server is busy processing the folling request:

> > SELECT * FROM titles <--- Yikes!

> > Obviously, this step is going to take a while to download this HUGE
> > file. My question is why does VFP need to get all the records in the
> > table when I am only CREATING the view? Is it the fact that "Limit 10"
> > is not native to VFP? I can't use "TOP 10" because MySQL does not
> > understand that. None of that should matter anyway because the first
> > command VFP sends (by itself) is "SELECT * FROM titles"

> > I am wondering if VFP and MySQL are not compatible in some way (can
> > anyone verify this situation on their system?). I have tried to solve
> > this for months, and it seems odd that I am the only one out there who
> > seems to be having an issue with such a basic operation. Help!

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Remus Rusan » Fri, 07 Feb 2003 05:04:22


Quote:> Furthermore, to make the view useful, it at least needs a WHERE
> clause.
> It's when I change the SELECT to;

> SELECT title FROM titles WHERE title LIKE ?xTitle LIMIT 1

> that is goes off getting all records (The LIMIT 1 is now ignored)

Actually, I made some investigation using SQL Server and the profiler and it
turns out that there are different strategies if the view has a WHERE clause
or not. If the view has no where clause, it will try to get the metadata by
adding a WHERE 0=1 clause at the end of the statement:

CREATE sql view v_test REMOTE CONNECTION connect1 SHARE as select * from
sysobjects limit 1

Profiler output:
SQL:BatchCompleted: select * from sysobjects limit 1 WHERE 1=0
(the 'limit 1' will be a syntax error on T-SQL , but that's irelevant).

If the SQL statement has aWHERE clause,  it will use a different approach:

CREATE sql view v_test REMOTE CONNECTION connect1 SHARE as select * from
sysobjects where name like ?m.name limit 1

Profiler output:
SQL:BatchCompleted SET FMTONLY ON select * from sysobjects SET FMTONLY OFF




As you see, it notifies the server about it's intention with a SET FMTONLY
ON, telling that it only need to parse the batch, not execute it. Then it
will prepare the query.

The interesting part is when you turn on ODBC tracing as well, to see what
is VFP requesting from the driver:
vfp7            2f6c-34f4 ENTER SQLPrepare
  HSTMT               01431FD8
  UCHAR *             0x00F14538 [      -3] "select * from sysobjects where
name like ? LIMIT 1\ 0"
  SDWORD                    -3

vfp7            2f6c-34f4 EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
  HSTMT               01431FD8
  UCHAR *             0x00F14538 [      -3] "select * from sysobjects where
name like ? LIMIT 1\ 0"
  SDWORD                    -3

vfp7            2f6c-34f4 ENTER SQLNumResultCols
  HSTMT               01431FD8
  SWORD *             0x0012F45C

vfp7            2f6c-34f4 EXIT  SQLNumResultCols  with return code 0
(SQL_SUCCESS)
  HSTMT               01431FD8
  SWORD *             0x0012F45C (25)
...

As you see, VFP only request the preparation of the "select * from
sysobjects where name like ? LIMIT 1\ 0" statement. Is the SQL Server's ODBC
driver that does the SET FMTONLY ON/OFF stuff. That's what the mySQL's
driver is doing wrong. About the ? LIMIT1\ 0 stuff, that's a funny
interpretation from VFPs SQL parser part... However, once the view is
defined, it will send the correct statement when you USE the view.

Quote:> I could create the temp table as you suggest, however this table is
> always open to hundreds of online users.

Can you start mySQL on a different machine, with an empty database, design
your views on a connection to this machine, and then simply change the
connection in the dbc to poin to the live data? I think is always better to
do our development and testing work an a dedicated server, not on the live
data.

Quote:>I am using SQLExec() as the
> workaround (messy), but I really wanted to take advantage of the
> dozens od features that views offer (automated updating, etc.).

I was about to say that you can use CURSORSETPROP() to achieve the same,
then I remembered about REQUERY() accepting only views... Yes, views have
features unavailable with SQLExec().

Quote:> Should I be mentioneing this to the MyOBDC driver development team?

Sure! Even if our conclusion turn out to be totally bogus, is always better
to worn the devs of a possible problem.

My recommendation is to develop your views on a separate server, with few
records.

HTH,
Remus

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Michael Nei » Fri, 07 Feb 2003 13:03:48


Remus, thank you so much for your efforts! Everything makes sense (I
am not going crazy after all). I will use the concept of a duplicate
"empty" table and then change the connection as a work around. If I
learn more, I'll add this this thread.

Michael Neill

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Anders Altber » Fri, 07 Feb 2003 22:56:16



Quote:> Remus, thank you so much for your efforts! Everything makes sense (I
> am not going crazy after all). I will use the concept of a duplicate
> "empty" table and then change the connection as a work around. If I
> learn more, I'll add this this thread.

> Michael Neill

Unless the ease of reading the same data back with REQUERY('theview') after
the TableUpdate() is important, don't overlook the tip about making a
SQLEXEC result cursor updatable with CursorSetProp. You set the Buffering,
KeyFieldList, UpdatableFieldList, UpdateNameList, Tables, and SendUpdates
properties, and optionally the WhereType, and you're all set. These are the
same as the settings you can make in the Update Criteria tab of the View
Designer. Quite a few VFP programmers prefer SPT to remote views.
To get a syntax model, open an updatable remote view and call CursorGetProp
for the same properties.

Is there an OLEDB driver for MySql? The CursorAdapter class in VFP8 can make
good use of one.

-Anders

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Michael Nei » Sat, 08 Feb 2003 06:04:59


I appreciate your suggestion to make the SQLExec() cursors updatable.
That will help a lot.

Here is a sample of exisitng code when using SQLExec().
Rather than repeat all the redundant code required to manage an
SQLExec() request, I built a simple function that processes the SELECT
statement.
The problem is that I cannot use paramatized values (?xtitle) and
often the
statement has to be broken up because there seems to be limit to the
number of
characters you can assign in a statement/string.

  SendSQL(";
  Select saleslog.*,;
  contact.name as company,contact.city ;
  from saleslog left join contact on saleslog.acct=contact.san ;
  where datecreated  > date_sub(now(), interval 14 day) ;
  order by datecreated desc ;
  limit "+STR(nToList);
  ,"TmpSalesLog")

FUNCTION SendSQL(cSelect,cTable)
  && Processes an sql statement
  Local nErr
  Local xErr
  INKEY(.1) && else any screen updates don't seem to happen until this
finishes
  IF TYPE("cTable")="C" && return into a custom named cursor
    nErr=SQLEXEC(hPubStock,cSelect,cTable)  
  Else && use the default cursor name
    nErr=SQLEXEC(hPubStock,cSelect)  
  Endif
  DO WHILE nerr = 0 && until all the results are in
    nErr = sqlexec(hPubStock)
  Enddo
  If nErr<1
    aError(xErr)
    MessageBox("Problem with SQLExec"+Chr(13)+xErr(2))
  Endif
RETURN (nErr > -1)

If there is anything obvious here to improve my approach, I am always
open to comments.

Michael Neill

 
 
 

CREATE SQL VIEW REMOTE (gets all records, Why?)

Post by Remus Rusan » Sat, 08 Feb 2003 12:28:39


One reason why I found the REQUERY important is because is the only way to
refresh the views w/o closing and opening the workarea. The problem is in
the Grids, as they notice the fact that the workarea is closed and loose
they formating and RecordSource. I know you can set the RecordSource to
empty string, close and reopen the workarea and set it back to the old
value, but I find a simple  REQUERY a zillion times more elegant... And,
franckly, I consider shameful that REQUERY does not work with SQLExec...

Regards,
Remus


Quote:> I appreciate your suggestion to make the SQLExec() cursors updatable.
> That will help a lot.

> Here is a sample of exisitng code when using SQLExec().
> Rather than repeat all the redundant code required to manage an
> SQLExec() request, I built a simple function that processes the SELECT
> statement.
> The problem is that I cannot use paramatized values (?xtitle) and
> often the
> statement has to be broken up because there seems to be limit to the
> number of
> characters you can assign in a statement/string.

>   SendSQL(";
>   Select saleslog.*,;
>   contact.name as company,contact.city ;
>   from saleslog left join contact on saleslog.acct=contact.san ;
>   where datecreated  > date_sub(now(), interval 14 day) ;
>   order by datecreated desc ;
>   limit "+STR(nToList);
>   ,"TmpSalesLog")

> FUNCTION SendSQL(cSelect,cTable)
>   && Processes an sql statement
>   Local nErr
>   Local xErr
>   INKEY(.1) && else any screen updates don't seem to happen until this
> finishes
>   IF TYPE("cTable")="C" && return into a custom named cursor
>     nErr=SQLEXEC(hPubStock,cSelect,cTable)
>   Else && use the default cursor name
>     nErr=SQLEXEC(hPubStock,cSelect)
>   Endif
>   DO WHILE nerr = 0 && until all the results are in
>     nErr = sqlexec(hPubStock)
>   Enddo
>   If nErr<1
>     aError(xErr)
>     MessageBox("Problem with SQLExec"+Chr(13)+xErr(2))
>   Endif
> RETURN (nErr > -1)

> If there is anything obvious here to improve my approach, I am always
> open to comments.

> Michael Neill

 
 
 

1. Remote View prompting for parameters on CREATE SQL VIEW

If I try to execute the following, I am prompted for the values of
nMainSeg, nMainSt and nMainLen:

CREATE SQL VIEW "V_TEST" REMOTE CONNECT "conDataset" SHARE AS ;
select cDivCode ;
 FROM gldiv,glcharm  ;
 WHERE Gldiv.esegno = ?m.nMainSeg ;
  AND GLCharM.ePType =  1 ;
  AND cDivCode = UPPER( SUBSTRING( GLCharM.cAcNo, ?m.nMainSt,
?m.nMainLen)) ;
  AND  GLCharM.eAcType IN (  1, 2, 3) ;
  GROUP  BY cDivCode

If I remove the GROUP BY, I am not prompted (as expected). Also, if i
try to trick it by STORE 1 TO m.nMainSeg, m.nMainSt, m.nMainLen before
executing this, then I get a connectivity error indicating "Argument
datatype float is invalid for argument 3 of substring function" -
somehow nMainLen is getting converted to a float when VFP passing it
to SQL Server...

Any ideas on how to get around this issue?

Thanks in advance,

Mark
/\/\/

/\/\/


2. Procedures being run in an Unix process

3. Remote view prompting for parameters on CREATE SQL VIEW

4. SQL 2000 Logical filenames + database restores

5. Creating a View using a remote view to Oracle

6. Function NLS_INITCAP doesn't capitalize accented characters properly

7. SELECT query in Access97 to SQL 6.5 returning wrong records - why Why WHY

8. Increase Share Memory on Linux

9. Creating a local view using CREATE SQL VIEW

10. Create View on Remote ( Linked ) Server ?

11. HELP!!!!! creating views on remote servers

12. Create View on Remote ( Linked ) Server ?