Automating the import of data using Microsoft SQL Management Server 2008 5

[Steve-vfp9user]

Hello

I am using VFP9 SP2 and Windows 7 64 bit together with Microsoft SQL Management Server 2008

I am trying to find a way to automate the process using VFP9 of extracting data from a table within a database that I can convert into a TAB delimited file for import into a VF9 table.

The code has already been written to import the .TXT file but I am looking for some guidance on how to automate the first part. The reason being is that we need to extract up to date information every hour. I can run this process (if it is possible) via Windows Scheduler. The importing of the TXT file into a VFP table can also be updated using a VFP app with WIndows Scheduler.

This is how we do it manually:
Open Microsoft SQL Management Server 2008
At the prompt:
Server type: Database Engine
Server name: .\sqlxxxxxxxxx
Authentication: Windows Authentication
Click on Connect command button

We then click on New Query and enter:
USE OURTABLE
SELECT * from abc.ourdata
Press F5

This works and after a minute or so we have the information we require. I then have to right click on the results and "Save as.." filename.txt

In other automation processes we have used something Mike Lewis posted to create and open Excel files and also to open PDF files and whilst this is not the same procedure I'm sure, this is what we are trying to achieve by automating if that makes sense.

My question is, how do I automate the process to extract the data and create the desired .TXT file (I am able to code the rest)

Thank you

Steve

 

[Steve-vfp9user]

Hi Olaf, I'll check it out

Thank you

Steve

 

[Steve-vfp9user]

I have looked at Olaf's suggestion and created a PRG with this:

lcConnstring = "Driver={SQL Server Native Client 11.0};Server=.\SQLEXPRESS;Database=OURDATA;Trusted_Connection=yes;"
m.lnConn = SQLStringConnect(lcConnstring)
SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata')

Needless to say I have changed the Database=OURDATA to the correct database file name and also SELECT * FROM abc.ourdata to the correct table name.

I have an error that states: "Connection handle is invalid"

Whilst on the point of SELECT * can I assume if I get passed this error I am now able to issue the relevant command so I can insert the required data into whatever format I want (e.g. SELECT * FROM abc.ourdata INTO CURSOR mycursor)

Thank you

Steve

 

[Olaf Doschke]

Well, look back at the sample code you found yourself. The SQLStringConnect has to succeed and result in a positive lnConn Value, before you can do SQLEXec. Something is still wrong, eg the driver {SQL Server Native Client 11.0} is not installed on your computer.

There is no single connection string fitting all needs. You need to know what driver you can use.

Start the ODBC manager from control panel and you'll see what drivers are present. As said it needs to be a 32bit driver.

Bye, Olaf.

 

[Steve-vfp9user]

You will have to give me some time here Olaf. I am not familiar with the terminology you are using and also not heard of some of the commands you mention. I am a new user and whilst I would not expect anyone to give me the answer perhaps a bit of an explanation in newbie terms might help.

I will post back when I have searched some more and appreciate your postings.


Thank you

Steve

 

[Mike Lewis]

The connection string that Olaf gave was just an exampLe. You need to pass a string that is valid for your particular instance of SQL Server.

If you don't know what to put in the connection string, an alternative is to use the the ODBC applet (in Control Panel) to create a DSN. This is a fairly simple point-and-click operation, and should be self-explanatory. Once you've done that, you can use the name of the DSN as the first parameter to a SQLCONNECT(). Note that this is an alternative to using SQLSTRINGCONNECT() with a connection string as the parameter. Choose one or the other.

Regarding your question: "can I assume if I get passed this error I am now able to issue the relevant command so I can insert the required data into whatever format I want (e.g. SELECT * FROM abc.ourdata INTO CURSOR mycursor)".

You can pass any command (as the second parameter to SQLEXEC()) that SQL Server understands. So any SELECT is OK, except that you can't use the INTO CURSOR clause, as that is not standard SQL. You specify the name of receiving cursor in the third parameter to SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Ok, cracked it!

As Olaf kindly mentioned, I checked the ODBC which was 32 bit
I added SQL Server Native Client 10.0 (Because 11 wasn't available on this computer)
I then amended the code slightly to:

lcConnstring = "Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS;Database=OURDATA;Trusted_Connection=yes;"
m.lnConn = SQLStringConnect(lcConnstring)
SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata INTO CURSOR MYCURSOR')
BROWSE

I now have the records required from the Database Table and able to manage it as required.

I am grateful for the guidance to ALL who posted on this thread. Matter resolved

Thank you

Steve

 

[Mike Lewis]

Excellent news, Steve. Glad it's working.

However, at the risk of complicating the issue, you have the following line of code:

SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata INTO CURSOR MYCURSOR')

I can't see how that would work. As I mentioned earlier, the INTO CURSOR clause is not valid in SQL Server, and should produce an error. Possibly the above code is just a transcription error?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

I am no expert Mike but trust me on this one, it works!

Thank you

Steve

 

[Olaf Doschke]

You should check what sqlexec returns, too. If that is negative, you again have an error. Errors don't cause system messages as you are used to, so what seemingly works, may not. You may have a cursor from a previous manual run, not from your code.

See SQLExec help, you determine the cursor name as additional, separate parameter, not via INTO CURSOR. You execute T-SQL, not VFP SQL. INTO CURSOR really is no option there, it errors.

If SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata INTO CURSOR MYCURSOR')<1
   = AERROR(aErrorArray)
   MESSAGEBOX("SQL Error: "+AErrorArray(3))
ENDIF

In general you do

*Prepare a query (or a whole script) resulting in one cursor in lcSQL. 
*Specify the desitred cursor name in lcAliasname. Then do:
If SQLEXEC(m.lnConn, lcSQL, lcAliasname)<1
   = AERROR(aErrorArray)
   * handle the error, eg by logging it
ENDIF

Where lcAliasname is a variable containing the desired cursor name you want sqlexec to generate. If you don't specify that, a cursor named SQLRESULT will be created.

I see no chance, the INTO CURSOR clause is ignored and you get the SQLRESULT cursor anyway.

Bye, Olaf.

 

[Olaf Doschke]

By the way: 0 as return value of SQLExec also is ok, but only when you run asynchronous queries. Typically that's not the case, as it's more complicated to handle this and unneeded, if you need the result to continue with your data processing anyway.

Bye, Olaf.

 

[Steve-vfp9user]

Yes, I posted too soon and apologies to Mike Lewis who is a clearly an expert!

Olaf, I will look at SQLExec now to achieve the desired result. Just one question though, how does lcSQL fit in here? I get variable lcSQL not found

Thank you

Steve

 

[Olaf Doschke]

Read the comments in that code...

Bye, Olaf.

 

[Steve-vfp9user]

Hello

Regarding your last post Olaf, I am not sure what "*Prepare a query (or a whole script) resulting in one cursor in lcSQL." actually means. As mentioned, I am farly new to this and so far, done quite well from the advice given so far.

A pointer would help

Thank you

Steve

 

[Olaf Doschke]

1. "Prepare a query in lcSQL" just means store a query string into the lcSQL variable.
2. "or a whole script" means you can also store a script, several sql commands in lcSQL and execute it
3. "resulting in one cursor" just means the query should only generate one result, also if it's a script (see 2)

Maybe I crammed too much into one sentence, did I? Sorry.

"In general you do" means the general case, anyway, not your case. This code was not using your specific SQL query, so I used variables instead. And the comments just said you have to put something into the variables before that codde can execute. Obviously the code fails without lcSQL and lcAliasname defined and set to a query string and an alias name. And that bit is not specific to SQLExec, is it? You can't run code using variables without creating and setting them at some point, can you? That's true with any code.

Bye, Olaf.

 

[Steve-vfp9user]

Ok, so if I have:

SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata') is m.lnConn the variable for the query string?

Thank you

Steve

 

[Olaf Doschke]

And one more overall thing why I added the "In general you do" code: SQLExec is one of the functions not throwing a system error if something went wrong, because typically what goes wrong is the query you execute, and that's not executed by VFP, but by SQL Server or whatever remote database used. The SQL Server informs VFP via ODBC driver of an error, VFP just returns -1 from SQLExec, if such an ODBC error happend.

It's important in general you handle errors, but more so with SQL Passthrough functions, as they don't cause error messages, even though they fail.

The "in general" part is more focused on the reaction to the return value, handling eventual errors, it's not about using variables instead of a concrete sql query string. lcSQL and lcAliasname just illustrate the general case, as the general case might use variables or parameters. I could have done that a step more general:

Function mySQLExecWithErrorHandling(tnConn,tcSQL,tcAlias)
   If SQLExec(m.tnConn, m.tcSQL, m.tcAliasname)<0
      = AERROR(aErrorArray)
      * handle the error, eg by logging it, or here by throwing an error:
      ERROR "SQLExec reports error:"+aErrorArray[3]
      * more info in aErrorArray
   Endif
Endfunc

Now that's a function you may use instead of SQLExec directly. Using the same parameterization you get what SQLExec does and you always check for errors. But: This is too simple. A real good approach would need more than just a wrapper around SQLExec. It even does not cover all parameterizations possible and does not cover the asynchronous case. Better do a class library around all SQL Passthrough functions. That's nothing you need to do right away, but it's a different thing doing SQL directly, as you can in VFP, or executing it remotely at some sql server. You need error handling with any code, also pure VFP code, but SQL passthrough functions are special about this.

Bye, Olaf.

 

[Olaf Doschke]

>SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata') is m.lnConn the variable for the query string?

Are you sure you understand your own code?

Where is lnConn set in your code?
At the line "m.lnConn = SQLStringConnect(lcConnstring)".

So what is lnConn?
It's the result of the SQLStringConnect() function.

And what is that result?
It's a connection handle.

Is that a query?
No.

What is a query?
A query is short for an SQL query, eg an SQL SELECT, UPDATE, INSERT or other queries.

What is the query in "SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata')"?
It's 'SELECT * FROM abc.ourdata'

Ah. Now everything is clear
Is it?

Bye, Olaf.

 

[Olaf Doschke]

Just to clarify: I don't pick my nose on you. You're welcome. You seem to be a beginner not only with SQLExec, but in general with VFP or even with programming.

I suggest you visit a regional user group or some online tutorials on development in general and with PHP. Tek-Tips is a nice place to ask detail questions, but not knowing some terms makes it hard to communicate. A good places to start with VFP is

http://www.garfieldhudson.com/freevideos.aspx

From your profile I see you also do PHP and Wordpress. A good place to learn PHP basics is

http://www.codecademy.com/.

They offer really fun interactive tutorials on PHP, Ruby, Javascript, Ajax, etc.

Bye, Olaf.

 

[Steve-vfp9user]

You seem to be a beginner not only with SQLExec, but in general with VFP or even with programming.

I think you have more or less summed it up there and the terminology you are mentioning is way over my head. More than happy to learn, small steps springs to mind.

Thank you for your time which I appreciate.

When I have worked this out I'll post back my resolution.


Thank you

Steve

 

[JRB-Bldr]

Just to expand on Olaf's explanation above...

Example SQL Query string:

lcSQLQueryString =  'SELECT * FROM abc.ourdata'
* --- One way that the Query String (or any other) can be used within the SQLEXEC() command ---
[b]nRet[/b] = SQLEXEC(nConnHandle,[b]lcSQLQueryString[/b],'ResultCursor')
* --- Note that 3rd parameter of SQLEXEC() can be defined to be the resultant record set cursor ---
* --- as an alternative to [b]INTO CURSOR ResultCursor[/b]

Note that result value (shown above as nRet) will be:
0 if the command is still executing
1 when it has finished successfully executing
–1 if a error occurs during execution (generally caused by a Query syntax problem)

In addition to the good advice you have received above from others, since you are new to VFP, I'd recommend that you get familiar with the VFP Debugging tools so that you can examine your code execution when needed.

Each of us have our own 'favorite' approaches to Debugging code, but, based on my own preferences, you might want to consider learning about the following:
* Adding code commands like SET STEP ON within your code to Break execution and open the Trace window.
* Or setting Breakpoints on your code 'as is' once the Trace window is already open.
* Using the Trace Window
* Using the Watch Window to examine variable and object values.
* and other means...

Good Luck,
JRB-Bldr