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

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator

mamo
Posts: 191
Joined: Wed Nov 07, 2012 9:11 am

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

Postby mamo » Thu Apr 11, 2019 2:46 am

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
EViews Developer
Posts: 788
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

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

Postby EViews Steve » Thu Apr 11, 2019 7:07 am

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
Posts: 191
Joined: Wed Nov 07, 2012 9:11 am

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

Postby mamo » Fri Apr 12, 2019 12:06 am

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
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13306
Joined: Tue Sep 16, 2008 5:38 pm

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

Postby EViews Gareth » Fri Apr 12, 2019 7:18 am

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
Follow us on Twitter @IHSEViews

mamo
Posts: 191
Joined: Wed Nov 07, 2012 9:11 am

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

Postby mamo » Fri Apr 12, 2019 8:14 am

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
Posts: 191
Joined: Wed Nov 07, 2012 9:11 am

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

Postby mamo » Tue Apr 23, 2019 6:31 am

... 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
EViews Developer
Posts: 560
Joined: Thu Apr 25, 2013 7:48 pm

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

Postby EViews Matt » Tue Apr 23, 2019 11:27 am

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
Posts: 191
Joined: Wed Nov 07, 2012 9:11 am

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

Postby mamo » Wed Apr 24, 2019 1:31 am

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


Return to “Data Manipulation”

Who is online

Users browsing this forum: Google [Bot] and 20 guests