SQL code in separate file when reading data from an ODBC source?

[mamo]

Dear Eviews team,

I use EVIEWS 10+

SQL query code can become very long when retrieving data from an odbc source through the wfopen(type-odbc) or the respective import command and selecting the data in an SQL query.

Is there a way to "outsource" the SQL query commands in a separate file when opening/importing data from a ODBC source, and to call them from there?

For instance

Code: Select all

wfopen(type=odbc, link) DSN=MYDSN; MYQUERY.SQL @freq U 1 @smpl @all

where MYDSN refers to a system DSN, and MYQUERY.SQL could be an ascii-file containing the SQL-query.

I know that the approach above does not work. It creates the error message

Table 'MYQUERY.SQL' does not exist in WFOPEN(...

But is there another approach to achieve this?

Best, mamo

[EViews Steve]

The WFOPEN command doesn't have a way of reading parameters from a text file, but you can always read the text file into a variable first, and then use the variable in place of the actual sql string, like this:

Code: Select all

'load all sql text files into mysql workfile as tables wfcreate(wf=mysql) u 1 'repeat next two lines for each sql txt file you have shell(out=sql1) type c:\files\sql1.txt 'run the DOS TYPE command to send file contents to new sql1 table object %sql1 = sql1(1,1) 'store the first cell of new table as variable %sql1 'statusline %sql1 'close mysql workfile now that we've read in all the text wfclose 'now use %sql1 in WFOPEN wfopen(type=odbc, link, wf=test1) DSN=MYDSN %sql1

By the way, there are some bugs/issues with EViews 10 when dealing with untitled (un-named) workfiles during import, so make sure you always name your workfiles by using the "wf=name" option in the WFOPEN or WFCREATE calls (see above). Giving your workfiles a pre-defined name will avoid the untitled workfile handling that could cause problems (by default, EViews won't allow two untitled workfiles to co-exist at the same time).

Steve

[mamo]

Dear Steve,

many thanks for the swift reply.

One reason for sourcing out SQL code in an external text file such as sql1.txt is the wish to make a longer SQL query more easily readable by structuring it across several lines. Then, with the solution you suggest, lines in sql1.txt would become rows in the table sql1. This requires collecting all lines of table sql1 into the string variable %sql1 sequentially in a for loop which is cumbersome. (Btw, a for-loop would also be required when collecting a SQL query stored as text object in a workfile page into a string variable.)

I would therefore like to ask the Eviews team to consider the following features as possible improvements in future version of Eviews:

1) Allow for reading text spreading over several lines in a text object into a string in one swift, and/or
provide more flexibility in capturing textual output of the shell command so that the user can choose between capturing it into i) a string variable, ii) into a text object, or, iii) as it is currently the case, as a table.

2) Implement a way of reading parameters from a text/ascii file in wfopen/import/pagecreate, etc.

Best, mamo

[EViews Gareth]

With regards to 1), I'm not sure I fully follow, but the following might help...

http://www.eviews.com/help/helpintro.ht ... 23ww178415

http://www.eviews.com/help/helpintro.ht ... 23ww819507

[mamo]

Indeed, @wread comes close to what I have in mind.

Unfortunately, however, @wread seems to be buggy - at least in version Eviews 10 March 2018 build, which renders this command largely inappropriate for my purposes:

1) @wread encloses lines in quotes which is not wanted, and it does so inconsistently. See example below: lines read from text1.txt are enclosed in double quotes, not so lines read from text2.txt. The double quotes cannot be easily removed by @replace, for instance, because this would also remove those double quotes which have been inserted on purpose in the text file to be read in.

2) @wread seems to skip lines with one single character at the beginning of a row. See text2.txt in the example below

I have not been aware of @wread since, at least in Eviews 10 March 2018 build, it seems that @wread has not been documented in the off-line help of Eviews 10.

Best, mamo

Code: Select all

' @wread encloses lines in quotes which is not wanted if @isobject("txt1") then delete txt1 endif ' Generate a text file with some typical SQL code text txt1 txt1.append Select VAR1 VAR2 txt1.append from MYDB txt1.append where VAR1 IN (X) txt1.save text1.txt string str1 =@wread("text1.txt") show str1 ' @wread is buggy: it skips sinlge characters at the beginning of a line! if @isobject("txt2") then delete txt2 endif text txt2 txt2.append H txt2.append W txt2.append Hello txt2.append World txt2.save text2.txt string str2 =@wread("text2.txt") show str2

[mamo]

... and furthermore, I also found that @wread automatically removes commas from the text it reads in, and it seems that this unwanted behaviour cannot be switched off. This renders @wread useless as a command for reading in structural code, such as sql code.

So, I again repeat my request to the Eviews team to consider the following features as possible improvements in future version of Eviews:

1) Allow for reading text spreading over several lines in a text object into a string in one swift, and/or
provide more flexibility in capturing textual output of the shell command so that the user can choose between capturing it into i) a string variable, ii) into a text object, or, iii) as it is currently the case, as a table.

2) Implement a way of reading parameters from a text/ascii file in wfopen/import/pagecreate, etc

best, mamo

[EViews Matt]

Hello,

As you've discovered, @wread has both some buggy behavior and some features that are not documented. The good news is that in an upcoming patch to both EViews 10 and 11 we'll add the ability to read an external file into a string in a "raw" mode with no processing. That will preserve whatever formatting you've included in your external file. This mode will be triggered by passing the string "RAW" as the second argument (currently undocumented) to @wread, e.g., @wread("sql1.txt", "RAW").

The second parameter of @wread actually controls how the function splits up the content of the external file into "fields", which are then simply concatenated to form the final string. The function uses one of (1) tabs, (2) commas, or (3) carriage returns as the delimiter between fields. If that second parameter is omitted, the function attempts to autodetect which delimiter to use based on the presence of unquoted tabs or unquoted commas in the file. If no such tabs or commas are found, then carriage returns are used. This is why unquoted commas seem to disappear, they're being discarded as delimiters. You may manually specify the delimiter by passing one of "T" or "TAB" for tabs, ",", "C", or "COMMA" for commas, or "CR" or "NL" for carriage returns as the second argument to @wread.

EViews' @wread's handling of quotes and single character lines is certainly buggy. In an upcoming patch to EViews 11 we'll fix this behavior to be consistent with the @wjoin/@wsplit family of string handling functions. I believe your specific issue will be solved by the additional of the "RAW" option, but these additional fixes should make @wread generally easier to use.

[mamo]

Hello,
this is good news, many thanks!
Best, mamo