Creating a query 3

[realm174]

Hi!

I do know how to create a query from the command window, and I also know how to view the SQL, however, what I'm trying to do is to "call" that "Create Query" command from whithin a program. I'm probably missing the obvious, but I'm running out of hair to pull at this point...

The only other option I can think of is to manually create the query with a bunch of GETs, but I'd rather stay away from that way if I can... I like the interactive interface, and that's what I'm trying to call from the program...

Any help will be highly appreciated

Thanks in advance.

 

[JRB-Bldr]

If you use Foxpro's Query Builder you can save it as a separate "file" which will have the .QPR extension.

To programatically execute it you then:
Do MyQuery.qpr

OR, as is more typical, just build the code directly into the program and execute it
SELECT <whatever> FROM <wherever> WHERE <criteria> etc.

Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[my2Cents]

Try this:

KEYBOARD &quot;Create Query&quot; + &quot;{ENTER}&quot;

works on my computer )

Later

My2Cents

 

[realm174]

Thanks for the responses. I can't get to my computer right now (repartitionning, which will go on for a few hours thanks to Partition Magic) however:

jrbbldr: Yeah, I guess it will come to build the code to display/select all the fields... I know I could done it by save it from the builder, but the end-user does not have access to the foxpro interface, and they will need to create their own queries...

my2cents: thanks! I never used &quot;keyboard&quot; before, but from what I understands, it &quot;stuffs&quot; the keyboard buffer with what you give it. Would it not, then, act as if I was pressing those keys from within the application? If so, that'd create a mess as I'm using letters as menu options.. I don't even want to try to think where this would take them !!

I guess what I really would want is to be able to call the query builder from within the application... Here's a thought.. is there a way, from within the application, to &quot;stuff&quot; the keyboard to bring up the foxpro menus?

thanks!

 

[rgbean]

I don't believe the Query builder is available in the Runtime environment. While there were some freeware variants of this capability back on the old Compuserve Forum, I don't seem to have any in my personal archive.

If you loaded the sample code, there is one in the Sample files - the Laser Disk application.

The following are all standalone products (and in some cases can be integrated with VFP), to do Ad Hoc Queries and reporting - they work with FP2.x Free tables as well as VFP tables.
Crystal Reports -

http://www.crystaldecisions.com/products/crystalreports/default.asp

FoxFire -

http://www.micromegasystems.com/foxfire/foxfire.htm

Stonefield's Query -

http://www.stonefieldquery.com/devedition.html

Rick

 

[JRB-Bldr]

Also, depending on how &quot;talented&quot; your users were and what capabilities you provided to them via a user input screen, you could allow them to input the primary elements into screen fields.

Example:
Fields: ______________________ (separated by commas)
Table(s): ____________________ (separated by commas)
Selection Criteria: __________
Display Order: _______________
Where to send result: ________
etc.

And then build the user's own SQL statement within your program using macro substitution.

Example:
SELECT &fields ;
FROM &tables ;
WHERE &conditions ;
ORDER BY &order ;
INTO CURSOR result

< and so on.... >

You could then save off the variable contents into a file for retrieval and subsequent execution.

The BIG assumption for this approach is that the user understands what is needed and how to put things together to get their desired result.

Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[realm174]

Well, a couple of months later, I'm still working on this.. I managed to create a procedure similar to the query builder, and I can build my expression without much of a problem. From within the Foxpro environment, I can run it just fine. However, from the standalone application, it won't run, telling me that feature is not available?? Any ideas?

Thanks!

 

[JRB-Bldr]

I'd suggest that you give us an example of:
1. How you build the expression and do you save it into a memory variable?
2. How you attempt to execute the newly created query statement (both from the Command window and from the application).

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[realm174]

Hi there!

I do save the &quot;string&quot; into a memory variable called m_expr

That variable contains the following:

SELECT OPENDATE, CUSTID, CUSTNAME, REPNAME, CALLTYPE, RESOTYPE, CLOSEDATE FROM CALL WHERE CLOSEDATE-OPENDATE > 14

And I call it like this:

&m_expr to file c:\report.txt noconsole

This works perfectly in the foxpro environment, but not in the standalone exe. I get the &quot;Feature not available&quot; error message.

I don't know if this matter, but this is FPD26a.

Thanks!

 

[rob444]

I just tested something like that and it worked just fine.
No problems at all.
(pc: windows xp pro)

Rob.

 

[realm174]

Thanks Rob... It works from an EXE? It doesn't work here under XP-Home, NT4 or 98. I'm puzzled now...

Realm174

 

[realm174]

ok, looks like if I save the query in a qpr file and then call the qpr file, it works from the standalone exe... I don't get it.. but at least it's working...

Realm174

 

[TheRambler]

try calling it like this:

(m_expr) to file c:\report.txt noconsole

 

[rob444]

Yes it worked from the EXE file, NOT running within the Foxpro environment.

Rob.

 

[realm174]

Ok, well, I figured out why it isn't working when I do the &m_expr... Directly from the help:

&quot;Feature not Available&quot; error in executable.
This error is a result of the following situations:
1. Using commands that are not supported in a run-time
version of FoxPro.
2. Using macro substitution.

My issue would be #2...

Thanks guys!

 

[rob444]

The macro substitution works in a running-alone exe.
I used macro substitution in my testprogram and I've used macro substitution many times before in running-alone and standalone exe's.

So that is not the reason of your not getting it to run.

If you want - send me a smal sample of the database and send me the code in a zip and I'll try it on my pc.

Rob.

r.brioul@hccnet.nl

 

[rob444]

Never mind sending me the files.
I've created your database with some records that should give a result when your query runs.

I've created m_expr and saved it to m_expr.mem
Then I used the following program:

RESTORE FROM M_EXPR
&M_EXPR TO FILE D:\REPORT.TXT
RETURN

I compiled this program to EXE and it run just fine outside of the foxpro environment.

Perhaps you use a wrong name for the EXE?
If you call it &quot;CALL.EXE&quot; the dosbox on XP will not do anything because it is a batchfile command and more.

Rob.

 

[realm174]

Hi Rob!!

Buddy, you sure are working hard on this one for me, and I really do appreciate.

The application is called CallStat. I got caught by that &quot;call&quot; before, and figured it out quickly...

I am emailing you a portion of the table, along with the prg. Let me know where I messed up...

Thanks!

Realm174

 

[rob444]

Having seen your code and database and having compiled the prog to a compact exe everything worked fine.

When you compile the same file to standalone exe you get the error you mentionned.

So do not compile this program to standalone exe but to a compact exe and use it with the runtime then everything just works fine.

Rob.