Link to home
Start Free TrialLog in
Avatar of tkrems
tkrems

asked on

Problems with OleDbDataReader VFPOLEDB

Hello experts,

can anyone tell me what's wrong with the following?

//***************************************************************************************//
// read data from database via OleDbDataReader 'rs' into DataTable 'table'
String connStr = "Provider=VFPOLEDB;Data Source=K:/Faktura/Data;Collating Sequence=general;";

OleDbConnection con = new OleDbConnection(connStr);
con.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT Artikel.zusaetze2, Artikel.art_bez1, Artikel.hinweis, " +
"Artikel.picture, CAST(NULL AS MEMO) AS pictures, Artikel.werbetext, Artikel.zusaetze5, " +
"Artikel.zusaetze3, Artikel.bemerkung, Artikel.zusaetze4, " +
"CAST(('A_' + Artikel.art_nummer) AS CHARACTER(50)) AS art_nummer, " +
"Artikel.min_verkf, " +
"ALLTRIM(CAST(((SELECT STR(Lager.bestand) FROM Lager WHERE Lager.art_nummer = Artikel.art_nummer) + ' ' + ARTIKEL.Einheit) AS CHARACTER(50))) AS bestand " +
"FROM ARTIKEL " +
"WHERE  Artikel.zusaetze1 = ( 'JA' ) AND  DELETED() = ( .F. )" +
"UNION ALL " +
"SELECT MASCH.zusaetze2, MASCH.fabrikat + ' ' + MASCH.gerate_typ AS art_bez1, MASCH.Hinweis, MASCH.picture, MASCH.pictures, MASCH.werbetext, CAST(YEAR(MASCH.baujahr) AS CHARACTER(40)) AS zusaetze5, MASCH.zusaetze3, MASCH.bemerkung, MASCH.zusaetze4, CAST(('M_' + MASCH.gerate_nr) AS CHARACTER(50)) AS art_nummer, MASCH.minverkf AS min_verkf, CAST(NULL AS CHARACTER(50)) AS bestand FROM MASCH WHERE MASCH.zusaetze1 = 'JA' AND DELETED() = .F.";

/*
"SELECT Artikel.zusaetze2, Artikel.art_bez1, Artikel.hinweis, " +
"Artikel.picture, CAST(.NULL. AS M) AS pictures, Artikel.werbetext, " +
"Artikel.zusaetze5, Artikel.zusaetze3, Artikel.bemerkung, " +
"Artikel.zusaetze4, " +
"CAST(("A_"+Artikel.art_nummer) AS CHARACTER(50)) AS art_nummer, " +
"Artikel.min_verkf, " +
"ALLTRIM(CAST((STR(Lager.bestand)+" "+Artikel.einheit) AS CHARACTER(50))) AS bestand " +
"FROM " +
"ARTIKEL " +
"LEFT OUTER JOIN LAGER " +
"ON  (Artikel.art_nummer + Artikel.art_bez1) = (Lager.art_nummer + Lager.art_bez1) " +
"WHERE Artikel.zusaetze1 = ("JA") " +
"AND DELETED() = (.F.)"
*/

cmd.CommandType = CommandType.Text;

rs = cmd.ExecuteReader();
rs.Close();
rs = cmd.ExecuteReader();
table.Load(rs);     // 'table' = 'abzubildende Artikel'CommandBehavior.CloseConnection
rs.Close();

//***************************************************************************************//

Why does the 'outer join'-query work in the FoxPro sql-editor but not in my code? In my code the result is always zero records.

Why do I have to run 'cmd.ExecuteReader();' twice in order to get the expected result? Otherwise the result is always zero records.

The Foxpro data source is free tables, not a database.

Thanks in advance

Thomas
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Are you saying that it if you only have ExecuteReader in there once just like below, it doesn't work:
cmd.CommandType = CommandType.Text;
rs = cmd.ExecuteReader();
table.Load(rs);     // 'table' = 'abzubildende Artikel'CommandBehavior.CloseConnection
rs.Close();

Open in new window

Or were you closing the recordset prior to loading it into the table?
Avatar of tkrems
tkrems

ASKER

Yes, if I have ExecuteReader only once, it doesn't work. The Result is zero records, and that's definitely wrong. No, the recordset is not closed prior to loading it into the table. The join query doesn't work, too. Maybe a FoxPro issue because of two tables in the queries? I have no idea what's going on there.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ALLTRIM(CAST(((SELECT STR(Lager.bestand) FROM L....

SELECT-SQL returns a result set, not a value, you can't make such sub queries in foxpro. I know it's possible in T-SQL to select a single field or expression and work with it as a scalar, but that doesn't work in vfp.

You can always move that into a normal join and do expressions on the joined fields.

Bye, Olaf.
Olaf wrote:
that doesn't work in vfp.
tkrems wrote:
Why does the 'outer join'-query work in the FoxPro sql-editor but not in my (P.C. note: C#) code?

Olaf, why don't you believe tkrems? When did you test it the last time?

You are right up to certain VFP version only.

VFP 9 supports correlated subselects and subqueries. If it would return more rows then error 1860 is generated in this case.
pavel,

I just took a glimpse, yes, I didn't test.

But there is something embedded similar to

cast (select str(field) from table as c(50))

And that doesn't work, and I tested it this way. It gives a syntax error. You can't do a subselect within a cast, which expects a single scalar value and not a result set. Do you see that now?

Bye, Olaf.

PS: It would help pretty formatting the  foxpro query to see what it's supposed to do and rewrite it. Others are also right about DELETED(), this is something you might be able to use in queries about single tables, but not here. As you just filter for DELETED() = .F. you can simply skip that, it's a default condition anyway, unless you don't connect with a parameter saying you also want to be able to see deleted records not yet purged by a PACK.
Of course, subselects must be enclosed in parentheses otherwise syntax and other various errors can be reported. Even T-SQL requires these parentheses (but I did not test it now :-).

And tkrems follows this rule.

So you can do the subselect inside the CAST in VFP if it returns scalar value.

Just try:
cast ((select str(field) from table WHERE RECNO()=1) as c(50))  && The WHERE condition is used to ensure the scalar value on output.
Ok, accepted. I still would never use that. The where clause used inside is just filtering for the record related to the current article, which is the main table selected from. So this can be turned to a simple join and would run faster already.

Bye, Olaf.
Who knows how correlated subqueries and subselects are handled in VFP... I don't see any major difference between this one and join. Both of them need same index for optimization and the speed difference can be marginal.
Avatar of tkrems

ASKER

Sorry for my late answer.

Thanks a lot pcelba. You are perfectly right!

Thomas
Avatar of tkrems

ASKER

Thank you very much!