Queries not showing some records in the BDE

I posted this problem in the programming newsgroup. Lysander (thanks friend!)
answered it and asked me to post in this newsgroup instead.

The original post is:
--------------------------------------------------------------------------------
---------------------
 Dear friends,
 I have a big dBASE 2.613 app that uses Firebird (1.5.5).... We are close to
production time, but we have discovered that some queries are not retrieving
records (simple queries). I suspected the BDE was the problem because other
tools, like IBExpert, were retrieving all records in queries. So, as a test, I
connected the program to the data installing the Firebird open source ODBC
driver & using that entry in the DBE. All the data was correctly shown this
way.
The problem is that I cannot use this ODBC driver in the BDE, because, I
believe, it is not mature enough (stored procedures that return values are not
working in dBASE). I am very worried beacuse I cannot go into producction with
this problem. Has anyone of you had this kind of problem? Can some tweaking be
done in the BDE so as to fix this?
Thanks for any help

Lysander's answer next:
--------------------------------------------------------------------------------
------------------

I use the IBPhoenix ODBC driver 1.02.0070 and it works PERFECTLY also
with stored proc objects.

Versions 1.02.0067 and 1.02.0069 have some problems when stored procs
are called (0067) or with recursions (0069). 0070 works fine.

The problem is that there is a bug in dBase when using the StoredProc
component together with dynamic input parameters. This bug came only
with 7.5 and higher versions, but I found a solution.


Please install the driver 1.02.0070, and post an example of the stored
proc you are using (The DDL from IBExpert). I think we can work out a
solution for you, if I can look at the code. Also post an example of how
you are using/calling the stored proc from within dBase.


Post this in the NG "SQLServers" please, because it belongs there.

--------------------------------------------------------------------------------
----------------

Jaime Escobal Wrote:

Lysander,

Thanks for your kind help. I installed IBPhoenix ODBC driver 1.02.0070. Now
queries are working correctly. all data is being shown in the dBASE program.
But, as I told you, the program breaks when I try to execute an stored procedure
that returns values. 

I have more than 300 stored procedures in that application. On some of them I
return values. For example, when I add an invoice header to the invoices table,
I create the invoice number inside the stored procedure & return that value
to the dBASE program. That works correctly using the BDE native drivers, but the
problem with this driver is that it is not returning all records in some
queries. 

With the ODBC driver, all data is returned correctly in queries, but the program
breaks when a stored procedure returns any value.

I´ll prepare an example of a stored procedure that returns a value to the dBASE
program. I hope we can fix this.... 

Thanks for your help again and have a nice day

Jaime Escobal

Lysander,
I have attached to this mail a very simple form that retrieves a value from a
stored procedure (I have attached the stored procedure too). Whe using BDE's
INTRBASE native driver, the procedure returns the value correctly. but when
using IBPhoenix's open source ODBC driver, the program breaks when it tries to
retrieve the parameter's return value.

in order to run this little program, you have to set de database name &
login string for your database. the stored procedure receives a string parameter
and returns an integer value from a very simple select query.... you can change
this table for another one that does the same... this is a very simple program
but shows the error.

I have a big problem here. 

1. When I use BDE's INTRBASE native driver, my stored procedures work correcty
(returning the expected values to dBASE), but some queries in dbase are not
working correctly (all records are not returned).. this is a BIG issue, we
cannot use this driver in production if it is buggy.

2. When using IBPhoenix open source ODBC driver, all the queries work correctly,
but  stored procedures that return value(s) to dbase do not work. When I try to
access the returned value(s), the program simply breaks. 

I have more than 300 stored procedures, so you can imagine the problem.  I hope
we can find a fix for this.

Thanks a lot for any help

Lysander,
I have attached to this mail a very simple form that retrieves a value from a
stored procedure (I have attached the stored procedure too). Whe using BDE's
INTRBASE native driver, the procedure returns the value correctly. but when
using IBPhoenix's open source ODBC driver, the program breaks when it tries to
retrieve the parameter's return value.

in order to run this little program, you have to set de database name &
login string for your database. the stored procedure receives a string parameter
and returns an integer value from a very simple select query.... you can change
this table for another one that does the same... this is a very simple program
but shows the error.

I have a big problem here. 

1. When I use BDE's INTRBASE native driver, my stored procedures work correcty
(returning the expected values to dBASE), but some queries in dbase are not
working correctly (all records are not returned).. this is a BIG issue, we
cannot use this driver in production if it is buggy.

2. When using IBPhoenix open source ODBC driver, all the queries work correctly,
but  stored procedures that return value(s) to dbase do not work. When I try to
access the returned value(s), the program simply breaks. 

I have more than 300 stored procedures, so you can imagine the problem.  I hope
we can find a fix for this.

Thanks a lot for any help

Lysander:

Thanks friend for the help. I worked!. I used the rowset values returned by the
stored procedure instead of the output parameters. But I had to install version
2 of the ODBC. (didn´t work with the driver's version you told me) After this
two changes all worked flawlessly. Now I have to fix the DBL source code on many
forms..  :(

As for the other tips... be sure that I'll apply them!

I did not use the SUSPEND clause in my stored procedures beacuse I wouldn't
return a rowset to the program. I still do not have a good grasp of its use...
do you know where can I get more information about this?

I am very grateful for you help. Just curious. Can I use the latest versions of
firebird  with dBASE (versions 2.0.x, 2.1.x) when using the ODBC driver?

Have a nice day
Jaime Escobal


P.S.
If you ever come to Peru, let me know... you have a friend here