VFP 9 and VFP 8 2

[asergeev]

Hi,
I'm trying to use VFP 9, but I have problems with the same code that runs fine in VFP8.

This is the code:

RELEASE ALL
CLOSE DATABASES
CLOSE TABLES
CLEAR

LOCAL lctmp AS STRING, lnConnHandle AS INTEGER, lctblname AS STRING

* Creating a connection string
lctmp='Description=ABC Conversion;DRIVER=SQL Server;SERVER=ABC156;UID="sa";PWD="sa";'
lctmp=lctmp+'APP=Microsoft Open Database Connectivity;WSID=ABC156;DATABASE=ABCRTL;Trusted_Connection="Yes"'

* Connecting to Database
lnConnHandle=SQLSTRINGCONNECT(lctmp)

IF lnConnHandle < 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
RETURN
ENDIF

* Creating the list of all tables in database
SQLEXEC(lnConnHandle,"select * from SYSOBJECTS where TYPE = 'U' order by NAME ","c_sqltbls")

---------

VFP 8 runs without problem. In VFP9 I'm getting error, because SQLEXEC doesn't create the return cursor.

Also I have an application that uses updatable remote views against MS SQL. It runs fine in VFP8, but VFP9 could not update views.

Am I missing something?

Thanks,
AGS

 

[mspratt]

Hi AGS,

Have you tried SET ENGINEBEHAVIOR 80?

Regards,

Mike

 

[Mike Lewis]

Mike,

No, SET ENGINEBEHAVIOR won't affect this. That command is relevant to VFP code; AGS's code is running against a back end.

AGS,

The only thing I know that has changed in this area is the nature of the connection handle returned by SQLSTRINGCONNECT(). It now returns a statement handle rather than a connection handle. But as far as I know, that has no impact on your code.

When you say SQLEXEC() isn't returning a cursor, do you mean that it's returning an empty cursor, or it is not returning anything? If the latter, is there an error code being returned? If the former, the most likely cause is that you are logged into the wrong database -- one without any tables. Is that possible.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[asergeev]

Mike,
Database has tables and code runs fine in VFP8. This code is actually the part of the program for creating remote views from the backend database. Program reads tables from MS SQL database and creates DBC with remote views against SQL tables. I have now 133 tables in a database. In VFP9 program doesn't create cursor and I'm getting error when program selects the resulting cursor. In VFP 8 I don't have any problems with creating cursor and DBC.

Thanks,
Andrei.

 

[Mike Lewis]

Ags,

What happens if you execute:

select * from SYSOBJECTS where TYPE = 'U' order by NAME

in the Query Analyzer?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[asergeev]

Mike,
Yes, I tested this query in QA and it run fine and produced the set of tables and tables’ properties.
The same result I have in VFP 8, but not in VFP 9.
I also should say that for the last year several times I was trying to use VFP 9 with VFP 7 -8 code and in all these cases I came across some problems. And it's not only me, even I has been working with FoxPro for the last 10 years started from FP 2.5, several my VFP programmers have the same problems, especially in VFP 9 - SQL area. But I couldn't find anybody in groups how is complaining about this. This is why, could it be something wrong with us or not a lot people use VFP 9.

Thanks,
Andrei.

 

[Mike Lewis]

Andrei,

I can assure you that there are a lot of people running VFP 9.0, and the code you are running is completely straightforawd. I do this sort of thing all the time, including that specific query on Sysobjects.

Let's get this clear. You've said twice that in VFP 9.0, it's not creating a cursor. You're not saying that it's creating an empty cursor, right?

In that case, the next thing to check is the reply from SQLEXEC(). If that is < 0, then it is an error reply, and you need to check the error code.

Could you do that and report back.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[asergeev]

Mike,
It returns actually 0. It is still executing. In Tracer I don't see any errors. But why does it take time to execute this query in 9 and no-time in 8?
Could be version 9 installation? or something else that I missing?

Thanks,
Andrei.

 

[robsuttonjr]

You should always have error checking as I know from experience in working with foxpro and sql. Do this:

change this:
* Creating the list of all tables in database
SQLEXEC(lnConnHandle,"select * from SYSOBJECTS where TYPE = 'U' order by NAME ","c_sqltbls")

to this:
* Creating the list of all tables in database
lnSqlStatus = SQLEXEC(lnConnHandle,"select * from SYSOBJECTS where TYPE = 'U' order by NAME ","c_sqltbls")
IF lnSqlStatus < 0
MESSAGEBOX("Unable to complete query"+MESSAGE())
ENDIF

Also for any other SQLEXEC commands use the same pattern. lnSqlStatus = SQLEXEC(....)
IF..




Regards,

Rob

 

[Mike Lewis]

Andrei,

I really think you're getting too hung up on this being a 8.0 / 9.0 version issue. You should try to focus on why this particular query is returning an empty cursor.

Also, I'm doing my best to help solve this, but it doesn't help that you are being vague in the information you are providing. For example, you now say "In Tracer I don't see any errors". What is "tracer"? Do you mean the Trace window in the debugger? If so, why would you expect to see an error there? I specifically asked you to check the reply from SQLExec() which is what tells you if the server is reporting an error.

Faced with this sort of problem, the best approach is to eliminate variables until it starts to work. You could try removing the WHERE and ORDER clauses in the query to see if that makes a difference. Then, try running another query, on another table (a very simple query on a small table) using the same connection.

Doing that will provide more information to help locate the problem. Perhaps you could give it a try and report back.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[wgcs]

Andrei,

I also should say that for the last year several times I was trying to use VFP 9 with VFP 7 -8 code and in all these cases I came across some problems.

I also am working on converting from code designed in VFP6 (which was running well in VFP8) to run on VFP9, and I'm seeing similar small inconsistancies. The reason no one is complaining, I think, is that most of the changes I've encountered have been where VFP is becoming more standards-oriented (in how it handles SQL, like always returning rows when there is GROUP BY, even if the source had no rows!), and more consistent with Windows (in how it deals with ODBC). They are growing pains, and somewhat annoying, but necessary.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[asergeev]

Mike and Bill,
Thank you for help. What is the most important that I undestood from your postings that what I'm experiencing is my local problem and I need to deal with it looking at VFP9 installation, ODBC, etc.
Yesterday night I put VFP 9 on a fresh laptop with SQL 2005. I created my database from a script in SQL 2005 and run above mentioned code in VFP9. It run without problems. This why I asked my IT peson to look at configuration on some developers PCs and reinstall VFP9.
Bill, I agree with you that any upgrade is usually growing pains and experience it from the first hands modifiying FP-VFP7 Query Tool generator to comply with MS SQL syntax.

Thanks once more.
Andrei.

 

[Mike Lewis]

Andrei,

Delighted to hear that the problem is solved.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)