VFP6: Stored procedure can't be called anymore?

[RedLion]

Hi,

Some strange things are happening, I've once build a program with a VFP database with some stored procedures within it like "newAutoId(<Unique ID>)".

Never had any problems with it but I am know extending the program with some new code and I can't call the function "newAutoId" (even where the new code is calling some old functions that use the call to newAutoId) anymore while the database is still open, and "set database to" doesn't solve the problem either?

Been busy debugging it the whole day and still haven't found a solution!

Anyone an idea what could prevent the function "newAutoId" from being called?
Is there a way to see all global functions?
Is there a way to reference the stored procedure that is stored with the database directly? Like you would do with this. or thisform for class objects?
Any ideas how to go further debugging it? Have not so many ideas any more...

Any help is welcome!

Thanks in advance!

 

[JRB-Bldr]

Have you tried 'Modifying' the procedure's VFP database and looking into the stored procedures?

* --- From the VFP Command Window ---
Modify Database ProcDBC

* --- From the VFP Top Menu ---
* - Database | Edit Stored Procedures...  -
* --- Look for code associated with  newAutoId  ---

If the VFP database is not corrupted and the code is still good and you have
SET DATABASE TO ProcDBC
Then the Stored Procedure show work.

If all is OK, try setting up a similar environment as that the code uses and attempt to execute it from the Command Window.

Are you getting some error message?

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Is there a way to reference the stored procedure that is stored with the database directly?

Yes. Just call it from the command window:

DO NewAutoId WITH 100

or

? NewAutoId(100)

You don't have to qualify it with the name of the database (even if the database is not the current database).

This is the first thing you should. It will tell you if the SP is still working, or if it has been superceded by another function or procedure with the same name.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Has the database been modified with VFP7 or later? Are Database events activated? This at least would hinder VFP ODBC from accessing the DBC.

Otherwise there is no special adressing. I'd rather SET DATABASE TO before calling a newAutoID() procedure. What might ovverrid the stored proc is a function available in some prg setup with SET PROCEDURE. This has precedence over stored procs. Do a set step on before NewAutoID() and then look into the debugger what's really running via SYS(16) in the watch window of the debugger for example.

Bye, Olaf.

 

[Olaf Doschke]

To be more precise: In regard to the HELP on PROCEDURE VFP searches in the file doing the call first, secondly in the current database procedures, but only the current database. After that comes files opend by SET PROCEDURE.

Bye, Olaf.

 

[RedLion]

Thanks for all responses!!!

I have tried all responses, but none of them solved the problem.

When I step through it with the debugger it just throws an exception at the point where newAutoId is called.

Within the old program (windows app) everything goes well.

Now the question from my customer was that he wanted an extra feature where his customers could access the information via the Internet. So I have made a new project with a class as olepublic within a vcx file and build it as a COM object (.dll). But I am testing it just with another .prg file.

In the beginning everything worked very well, but because my customer has some big synchronization process during the week were you don't want anyone else in the database I moved the "open database shared" to a separate method because a webserver loads the COM object once and then keeps it in memory so the destroy (close database) event is never called, and for the other part of the program it wouldn’t possible to lock the database exclusively. So each time a call from the asp.net page to the com object has to start with a opendatabase() method and has to end with a closedatabase() call. And this is the point where the problem raised.

After more debugging I finally found the problem, before I can make a call to a stored procedure from the database I have to use the database first!!!!
So I added the code
*just do nothing else then close and open a table
select 0
use mytable again
use

and then there aren't any problems with calling stored procs anymore....

grrrr....cost me a lot of hours...what a stupid bug...

But when I open a database and then call a stored proc without opening a table from the command window the bug doesn't occur...it's very strange!

Anyone has an idea what is happening?

 

[Olaf Doschke]

Seems like the OLE class hasn't had the database opened. Not the one DBC with the stored procs at least. By opening a table of the DBC the DBC is opened if not yet open, therefore I think you're opening the wrong DBC in your opendatabase() method or openning the DBC there fails due to trying to opening it exclusve. The only thing that your routine using and closing a table does effectively is opening the database.

So would you simply doulbecheck if a DBC is open after the opendatabase() method has been called by checking ADATABSES()? Then check ADATABASES() again after opening and closing a database table?

Bye, Olaf.

 

[Olaf Doschke]

Side information:

Any way you build a COM Server as Single/Multithreaded In-Process (DLL) or Out-Of-Process (EXE), the COM instance is isolated from all the datasessions and does not "inherit" opened databases or see open databases in the current process.

As you get an execption (most probably Error 1 - File 'newAutoID.prg' does not exist) this is a sure sign the COM instance has not opened the database with the procedure, you did not find a bug that the procedures are only accessible after opening a table of the database, this is not the case, but it can help to indirectly open the database.

Bye, Olaf.