SQL Connect Handle

[RajW]

Does anyone know how to create a SQL connection to the SQL database
from a VFP program? I've tried SQLCONNECT() but maybe I've got the
wrong syntax but that doesn't seem to have enough parameters anyways.

Any help would be great!

Raj

[Ray Collazo]

Raj:

There are two ways it can be done:
1) opening and Using M2M's database container to connect
2) creating the ODBC connection on the computer directly to the
database...

=== For Option 1: ===
You can try this on the VFP Command line, or open it from within a
program:

-> set excl off
-> open data m:\m2mdata\data01\m2mdata
-> conhandle = SQLConnect('MainConnection')
-> ? conhandle

If you see a 1 on the main VFP window, you successfully connected to the
database. You can now run queries like:

-> sqlexec(1,[select * from inmast where fpartno =
'partnumber'],'tempcursor')

=== For Option 2: ===

First you would need to create the ODBC connection on the computer itself To
the database before you can access it via VFPs (or VBs) sqlexec(): (It looks
long but its actually super simple to do once you've done this enough times)

Heres my setup for example: My M2M server is "mrpserv", my SQL database is
"m2mdata01"

1) Open up the Control Panel, go into your Administrative tools, and
double-click Data Sources (ODBC).
2) click Add to add a new User DSN
3) scroll to the bottom, click SQL Server, click Finish to start configuring
the connection...
4) The name can be anything you want (in my case m2mdata01), the Server is
your SQL server (in my case MRPSERV). Click Next.
5) The server will verify the login "With SQL Server Authentication using a
Login ID and Password entered by the User." At the bottom of the screen,
enter sa for the user and type in your sa password. Click Next.
6) Check '[] Change the Default Database to:' then select the database that
your m2mdata resides on, in my case m2mdata01. Click Next.
7) Click Finish.
8) Click the button that says "Test Data Source". It should give a small
screen that Should say "Test completed Successfully!" Click OK, OK, and OK.

NOW you can access the database via VFP's command window or from within a
program:

-> sqlconnect('m2mdata01')
(type in your password)

Or, from within a program you can connect to the database using the syntax:
sqlconnect('database','user','password') )

I hope that helps!

Ray

[RajW]

Thanks Ray!

I have this field I'm putting on the CUST screen that tally's the YTD
Cash Receipts for our Credit and Collections gal and the M2MSelect
statement was refreshing too slowly causing the refresh rate to pause
dramatically when bringing up the window or trying to scroll trough
the customers. So I thought the SQLEXEC would be fast enough, but
there is still a bit of a delay. I hadn't even considered creating a
view in the SQL database until this a.m., still stuck in VFP mode.
With a view, the data will already be there, compiled and ready to be
displayed. And plus I can add another calculation to that view,
Current Unshipped Line Items.

Raj

> Raj- Hide quoted text -
>
> - Show quoted text -

[Raymond Collazo]

Ooo! If ytou can, once you create the view, can you please describe how
you went about to create it? I've heard of SQL views but have not explored
them yet. Thanks!

Ray

[RajW]

Sure thing. I've made a couple of Views (under the live company
database) for my UPS and FedEx integration programs to pull from.
They link SHMAST, SOMAST, SYADDR and maybe something else, oh yes,
SLPN to go to the right salesman together into a live and constatly
updated view, or table. So when something changes in SOMAST, my View
is updated too and is available for immediate "information
dissemination". Also, my UPS tech gave me this cute little trigger
program that whan the UPS info comes back to my database (export from
UPS into a custom table in my live database), it runs this trigger and
updates my M2M tables like SHMAST with UPS info like tracking number,
weight, etc. immediately.

What I'd really like to with the View concept is make a live
"dashboard" type program. A window displaying things like Sales Order
volume, running Invoices and or cash, Labor costs or production
numbers. Stuff that the CEO, at a glance, can see what the blazes is
going on in real time. Anyways, another long term project...

In the meantime, do you know how to activate a browse window in VBA?
I've got my DoCMD() pulling info into a cursor and I want to display
that with a button click. In VFP, i can create the cursor and then
"BROWSE" at the button click and it pulls up a browse window of the
cursor. Do I really have to create a form and fields and import the
cursor into a table and have the new form form and field dispaly
pulling in the info? Or can I just have a simple Browse - bing,
here's your data.

Thanks,
Raj

On Nov 22, 5:04 pm, "Raymond Collazo" <rcoll...@nightscaping.com>
wrote:

> >> - Show quoted text -- Hide quoted text -

[Ray Collazo]

For M2MVFP, the DoCMD() is actually shoving VFP commands to M2Ms VFP
instance to execute: thus if you want to browse the data right after you
grab it, just docmd("browse"), or even go ahead and elaborate more on the
VFP browse function, like docmd("browse fields field1 field2 for field1 =
[somestring]")

BTW, this is where VFPs use of different quotes come in handy: because you
can still pass things to VFP via the docmd() like:

docmd("select * from inmast where fpartno = [" + vbcharstring + "] into
cursor tempcursor")

VB ONLY uses double Quotes to encapsulate a character string, whereas VFP
can use doubles, singles, or even square brackets.

Now if ya wanna show a browse screen that gives the user a chance to
double-click on something, that I'm not certain of just yet, but I know it
Is doable...

[Ray Collazo]

Hello all!

http://www.adtmag.com/article.aspx?id=20382

Yes, VFP is indeed being killed off by Microsoft, in favor of its own
home-grown Access database development environment.

Knowing that the end of the VFP environment is near, I'm more curious about
what will happen to all of the M2MVFP aND SQL users whom have spent
thousands of hours and dollars in customizations for their current M2M
product, especially after 2012 (which is only 5 years away): Will we all
have to upgrade our M2M products Again as if for the first time? What will
the new backend engine be if it won't be VFP?

I'm definitely curious to see what my colleagues here have thought or what
other information you may have picked up concerning this issue.

- Ray

[Brent A. Marcus]

It's VERY important that everyone understand this;

"The company will continue to provide mainstream support for VFP9 until January 12, 2010 and extended support until January 13, 2015, according to Microsoft's support lifecycle page."

It's been known for some time that VFP 9 will be the last FoxPro.

Visual Basic is Microsoft's preferred application development platform, which is a shame due to it being vastly inferior to FoxPro for developing data centric applications.

FoxPro will continue for another 8 to 10 years, which is a long time in this business. But this shouldn't mean much to most Made2Manage users. Most either are on or will be on SQL in the near term. Using ADO this means you can access your data in any way you please - C#, Visual Basic or continue in VFP. At this moment, VFP remains the most comprehensive language when dealing with data and with a true OOP model (VB is procedural with an OOP façade) one of the best for building comprehensive frameworks for reusability of code.

Brent A. Marcus
Director of Information Technologies
Reinhold Industries, Inc.
bma...@reinhold-ind.com
(949) 232-4216 (Cell)