DBQuery direct to file

[JRL]

Would it be possible to have a database function that evaluates an SQL statement and writes the result directly to a file? Perhaps to a csv format.

Or is there already a way to do that? Perhaps with VBScript?

[jpuziano]

Hi Marcus,

I whole heartedly second this request (in v12 if at all possible).

I have queries that return around 3000 rows. The query returns the results in 3 seconds but I have no way of showing that to a user in an acceptable timeframe.

- I cannot just display the results inside an MDL> box as the results are in an array. I would have to first convert the array into a single variable and for large result sets, this takes a prohibitively long time (I assume because of limited memory on our older machines).

- I cannot load the result set into a data grid control on a dialog as we do not have access to one yet... though one does exist in Delphi.

- My fastest option is to write a csv file on the user's hard drive and then use ExecuteFile>results.csv and the results open in Excel. This is fast enough on our fastest machines (2.83 GHz, 3.46 GB) it all happens in about 6 seconds... but on our older machines (1GHz, 1 GB) it slows to a crawl and takes over 15 minutes. The bottleneck is in the code that loops through the results array and builds the file.

- If we had a version of the query that could write the csv file directly to disk very quickly, that would help immensely and be much appreciated.

JRL and I have both found that converting the query results array into a single variable takes a huge ammount of time as opposed to writing a csv file to disk so that approach is out... unless we were to get an MS function that could quickly convert the results array into a single variable or... if something like an MDL> box or a field on a dialog could be enhanced to just accept the query results array as input directly and show it... but then that's basically a datagrid contol...

If anyone has any suggestions on how to improve the speed in the above process, please post.

Thanks and take care

[sarver311]

I also have issues with this. I have a "slight" workaround that I use that makes it easier to get my results into one variable.

Essentially i let the SQL query and the server do the leg work of building my variable for me. Unfortunately it is limited in that I only can use it for a query that is asking for one field but maybe someone can build on this.

Let's say you want to query a name from a customers table. Normally you would use an sql statement like the following for your dbquery command.

Code: Select all

select name from customers.

With this code you would put the following

Code: Select all

DECLARE @List varchar(2000) SELECT @List = COALESCE(@List + '%CRLF%', '') + Cast(name As varchar(100)) FROM customers SELECT @List As 'List'

This query returns 1 variable that already adds the %CRLF% after each record. I am sure there would be a way to modify it for multi field queries but I don't know how to do that.

I'm no expert but since all the computation is server side this "should" be quicker than building your result via loop on the ms script.

Oh yah, I guess that query is also limited by what type of Sql server you are running. Forgot to mention that I use Sql 2005. However, the concept still stands. There are methods in most DB's to process your outputs before it's returned so maybe that is something to go on. What kind do you use Jpuziano?

[jpuziano]

Hi sarver311,

So if I could somehow make the server return the whole result set in one row containing one column... then I could just refer to that one array variable (row 1, column 1) inside Macro Scheduler.

Theoretically, it should then be possible to pop that one array variable into an MDL> box and display the results... quickly.

I use Oracle... not sure if this is possible... and if it is limited to only one column that I can return results for, then its not going to help me, but it is a clever approach nonetheless.

Thanks for sharing this idea and take care

[sarver311]

Exactly, when my results are returned it is dbreply_1_1 and that is it.

I did some testing and was able to configure the statement to work with more than one field. Do you have a sample of one of your queries you are running? Maybe we can get it to work after all.

I don't do much with oracle but i think this query will work. The worst that can happen is it won't work

[jpuziano]

Hi sarver311,

There certainly seems to be a lot of ways to possibly achieve this in oracle... I found the following links for starters:

http://www.sqlbook.com/SQL/Create-comma ... st-27.aspx

http://stackoverflow.com/questions/4689 ... -in-oracle

http://stackoverflow.com/questions/6544 ... -text-form

Let's say the query is:

select f_name, l_name from customers where f_name like 'j%';

And let's say that query returns these results in the usual row-column array format so the following array variables in MS would contain these values:

dbreply_1_1 = jim
dbreply_1_2 = smith
dbreply_2_1 = john
dbreply_2_2 = doe

The goal is to re-write the query so that the results all come back in one array variable dbreply_1_1 and are in this form:

"jim","smith"CRLF
"john","doe"CRLF

Note: If anyome else out there knows how to do this, please jump right in.

[sarver311]

Ok I tested this out on my end and it seems to work. I should give you the output

John,Smith%CRLF%

Code: Select all

DECLARE @List varchar(2000) SELECT @List = COALESCE(@List + '%CRLF%', '') + Cast(f_name As varchar(50)) + ',' + Cast(l_name as varchar(50)) FROM customers where f_name like 'j%' SELECT @List As 'List'

I would like to point out a potential issue you might encounter. You'll note the @list is a field that supports 2000 characters which is the field that ends up storing all the data from your query. You mentioned something about 3000 records being returned in your previous post so that does make me concerned this variable will not contain all your data. I am not to familiar with Oracle data types but I found the the clob data type seems to be the one that stores huge amounts of text in Oracle Db's so you may need to use that instead.

It may not be an issue at all but I just wanted to point that out in case you run into issues with that query that could be a potential source of issues.

Let me know what happens, I got my fingers crossed

[adroege]

Here is another technique that I started using many years ago.

It uses VBScript to format the results into HTML, and then display in the browser. The final display can be as pretty as you want, and include logo images, colors, etc...... just up to your HTML coding skills. This example just shows the basics.


[code]
//**************************************************************
//*** Display Database Query in HTML format ***
//*** ***
//**************************************************************


// ****************************************
// ** Read DataBase **
// ****************************************
VBSTART
Function DBAccess (dba,sql,html_flag)

Dim result
Dim set1
Dim row1
Dim rc,rowcount

'open database
set db = CreateObject("ADODB.Connection")
db.Open "Provider=Advantage OLE DB Provider;" & _
"Data source=" & dba & ";" & _
"ServerType=ADS_LOCAL_SERVER|ADS_REMOTE_SERVER;" & _
"TableType=ADS_ADT"
set rs = CreateObject("ADODB.Recordset")
' change to a static cursor instead of default "forward only"
' so that the recordcount property works properly
'rs.CursorType = adOpenStatic
rs.CursorType = 3
'RecordCount only works when CursorLocation set to Client (3)
rs.CursorLocation = 3

'this was the previous was to run the query
'but it would not return the recordcount
'set rs = db.Execute(sql)
rs.Open sql,db,3,3

if html_flag = "T" then
set1 = ""
else
set1 = ""
end if
rowcount = 0
' if the result set was created, continue
' (without this test, rs.EOF returns an error)
if (rs.State = 1) Then
if rs.EOF then
'result = MsgBox ("Your query returned no results", 65, "MsgBox Example")
else
rc = rs.RecordCount
fc = rs.Fields.Count - 1
do until rs.EOF
rowcount = rowcount + 1
if html_flag = "T" then
row1 = ""
else
row1 = ""
end if
for i = 0 To fc
if html_flag = "T" then
row1 = row1 & "" & trim(rs.Fields(i).Value) & ""
Else
row1 = row1 & trim(rs.Fields(i).Value)
end if
next
if html_flag = "T" then
row1 = row1 & ""
end if
set1 = set1 & row1
if rowcount "
end if

DBAccess = set1
End Function

VBEND

Let>Alias=J:\Path\To\Data\

Let>myTitle=My Title For the Query Results
Let>Query1=Select entitynum,entityrole,casemarker,entityid,classtype from entities;

VBEval>DBAccess("%Alias%","%Query1%","T"),myHTML

IfFileExists>c:\searchresults.html
DeleteFile>c:\searchresults.html
Endif
WriteLn>c:\searchresults.html,result,%myTitle%
//Optional - Load an image at the top of the page
WriteLn>c:\searchresults.html,result,
WriteLn>c:\searchresults.html,result,%myTitle%
WriteLn>c:\searchresults.html,result,%myHTML%

//Launch the default browser to display the finished page
ExecuteFile>c:\searchresults.html

Label>End


[/code]

[jpuziano]

Thanks sarver311 and adroege for your suggestions on this, much appreciated.

In this post...

VAREXPLICIT=1 and Array Access
http://www.mjtnet.com/forum/viewtopic.php?p=28192

Marcus said...

The longer term plan is to work towards creating real array structures to avoid this problem and add further capabilities.

...so I am now hopeful that we may benefit from those real array structures down the road - see post at the above link which contains detail related to this Enhancement Suggestion.

Thanks again for all posts on this topic.

If you have had to deal with this issue and have advice or a workaround, please jump in... even if its just to affirm that this is an issue for you and that a new DBQueryToFile> command would be of value.

Thanks again and take care

[Marcus Tettmar]

Would it be possible to have a database function that evaluates an SQL statement and writes the result directly to a file? Perhaps to a csv format.

Or is there already a way to do that? Perhaps with VBScript?

Here's a VBScript function called SQLToCSV:

Code: Select all

VBSTART
  Sub SQLToCSV(connection_string,sql_string,output_file)
  
    'Declare variables
    Dim outFile, objConnection, objRecordSet, strSQL, intCount
    'Create File for output
    set outFile = CreateObject("Scripting.FileSystemObject").CreateTextFile(output_file)
    'Create DB connection
    Set objConnection = CreateObject("ADODB.Connection")

    'Open connection using _YOUR_ Connection String
    objConnection.open connection_string

    'execute the SQL and get a recordset back
    Set objRecordSet = objConnection.Execute(sql_string)

    'loop through the field names to output header row
	for intcount = 0 to objRecordSet.fields.count -1
		if intcount <> objRecordSet.fields.count-1 then
			outFile.write """" & objRecordSet.fields(intcount).name & ""","
		else
			outFile.write """" & objRecordSet.fields(intcount).name & """"
		end if
	next
	outFile.writeline ""

    'loop through recordset to output all data rows
	do while not objRecordSet.eof
		for intcount = 0 to objRecordSet.fields.count - 1
			if intcount <> objRecordSet.fields.count - 1 then
				outFile.write """" & objRecordSet.fields(intcount).value & ""","
			else
				outFile.write """" & objRecordSet.fields(intcount).value & """"
			end if
		next
		outFile.writeline ""
		objRecordSet.movenext
	loop

    'Clean up
    objRecordSet.Close
    set objRecordSet = Nothing
    objConnection.Close
    set objConnection = Nothing
    outFile.Close
    set outFile = Nothing
  End Sub
VBEND

Let>con=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\example\mydb.mdb;Persist Security Info=False;
Let>qry=Select * from Query1
Let>file=%SCRIPT_DIR%\out.csv

VBRun>SQLToCSV,con,qry,file

As you can see SQLToCSV takes a connection string, the SQL query and finally a file name for the CSV file to generate.

[jpuziano]

Hi Marcus,

Your VBScript function SQLToCSV works and is extremely fast.

Thanks for this... very much appreciated.

JP

[JRL]

I use SQLtoCSV often. Recently had a need to append to an existing file. Instead of renaming and copying files to accomplish this I rewrote the VBScript. Thought others might find this useful so here it is.

The differences between this and Marcus' SQLtoCSV VBScript are:

1) Does not write the headers. Since its appending the headers should already exist.

2) Does not delete an existing file.

3) Does not create a file if the file does not already exist. I have not tested this but the line
set outFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(output_file, 8, False)
has "False" at the end which according to this page means a file will not be created. If you want a file to be created remove "False" and replace it with "True"

Otherwise, AppendSQLToCSV requires the same parameters as SQLToCSV

Code: Select all

VBStart
  Sub AppendSQLToCSV(connection_string,sql_string,output_file)
  
    'Declare variables
    Dim outFile, objConnection, objRecordSet, intCount
    'Open File for output
    set outFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(output_file, 8, False)
    'Create DB connection
    Set objConnection = CreateObject("ADODB.Connection")

    'Open connection using _YOUR_ Connection String
    objConnection.open connection_string

    'execute the SQL and get a recordset back
    Set objRecordSet = objConnection.Execute(sql_string)

    'loop through recordset to output all data rows
    do while not objRecordSet.eof
        for intcount = 0 to objRecordSet.fields.count - 1
            if intcount <> objRecordSet.fields.count - 1 then
                outFile.write """" & objRecordSet.fields(intcount).value & ""","
            else
                outFile.write """" & objRecordSet.fields(intcount).value & """"
            end if
        next
        outFile.writeline ""
        objRecordSet.movenext
    loop

    'Clean up
    objRecordSet.Close
    set objRecordSet = Nothing
    objConnection.Close
    set objConnection = Nothing
    outFile.Close
    set outFile = Nothing
  End Sub
VBEND

Let>con=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\example\mydb.mdb;Persist Security Info=False;
Let>qry=Select * from Query1
Let>file=%SCRIPT_DIR%\out.csv

VBRun>AppendSQLToCSV,con,qry,file

[jpuziano]

Hi JRL,

I use SQLtoCSV a lot as well and I think being able to append instead of re-writing the entire file will come in handy... should be some speed advantages, for huge files at least.

You know, there was a time when I was going to ask Marcus for SQLtoCSV and SQLtoHTML to be made into native Macro Scheduler functions... I may have posted that request... but maybe not because these VBScript functions have performed so well.

Hey Marcus - if you ever experiment and find native function versions of SQLtoCSV, SQLtoHTML and AppendSQLToCSV run significantly faster than the VBScript versions we have now, then please consider adding them. Not just for the speed... but I find its a significant advantage to be able to have a compiled macro NOT rely on VBScript to be working on the target PC... its just less that can go wrong. I've deployed compiled macros on some messed up machines over the years and not having to rely on VBScript means less issues for me, less things that might need to be fixed.

Thanks again for sharing JRL and for all you do here!