DB Query and Fetching

[tchaithonov]

Hi Guys,

I am trying to fetch some data from a db to my wf given a list of variable names from a group. The problem I have is with the lack of db query function via command line. Let me give you a short example of what I am trying to accomplish:

Code: Select all

' group name: group1, containing !n variables for !i = 1 to !n !j = 0 %var = group1.@seriesname(!i) %rawvar = left(%var, @len(%var) - 3) ' I have a suffix for the transformed variables (%var) in the group that is 3-char long while !j <> 1 ' ALGORITHM: if @...(%rawvar) = 1 then 'something similar to @isobject, but for database fetch(link) db::{%rawvar} !j = 1 else @uiedit(%var, "Please enter the correct raw variable name.", 24) !j = 0 endif wend next

I think this could be done in either of the two ways I have in mind: 1. the "if" case above, or 2. some sort of error handling, something along the line as:

Code: Select all

'ALGORITHM fetch(link) db:{%rawvar} if NOT_FOUND then @uiedit(%var, "...) endif

My psuedo codes above may be wrong; if you find something funny with the while loop or the for loop, forgive me. But I just really want to see how I could do the query in a program just as the gui alternative. Please let me know. Thanks.

Tchaithonov

[EViews Gareth]

There is no command line query, but you could use @lasterrnum or @lasterrstr to test whether the fetch failed. Of course you would want to use setmaxerrs to set the max error count first

[javiersan]

Hi Gareth,

@lasterrnum and @lasterrstr are not mentioned in the program documentation, are they?

Javier

[EViews Gareth]

They're in the EViews 7.1 documentation.

[EViews Chris]

There's no general command line version of querying a database, but you can probably do what you're after with the @wlookup() function (which was added in version 7).

Here's an example of breaking up a list of object names into ones that exist in a database and ones that don't.

Code: Select all

%dbname = "USECON" %seriesnames = "ADR Z123 AFSN" %foundnames = @wlookup( @wcross(%dbname + "::", %seriesnames)) %notfoundnames = @wnotin(%seriesnames, %foundnames)

Note that groupname.@members gives you the space delimited list of series names in the group, so you could use this as %seriesnames to look for group members that exist in a database.

[tchaithonov]

Thanks Chris. I will try that tomorrow.

[javiersan]

Hi Chris,

I don't understand how to use your code above, could you expand the explanation a bit more please?

Thanks,

Javier

[EViews Chris]

Here's a bit more explanation.

Say that my EViews database is called 'USECON' and it contains the objects 'ADR' and 'AFSN' (but not 'Z123').

If I run the code above, then the results will be:

%foundnames = "ADR AFSN"
%notfoundnames = "Z123"

How this works is that the @wcross function will create the string:

"USECON::ADR USECON::Z123 USECON::AFSN"

Then the @wlookup function will go through each item in the string looking for objects that match the specified 'name pattern'. Since there are no wildcard characters (* or ?) in the names here, this becomes a simple test for whether each name in the string exists in the database. The output of @wlookup will contain all the object names that were found in the database.

The last step is to use @wnotin to get a list of all names that were not found in the database. I added this to show how you could go about doing additional processing on the items that were not found (eg. to look in a second database and see if they can be found in there instead).

You can use the %foundnames and %notfoundnames strings in a variety of ways. For example, you could fetch all the objects that were found in the database using:

Code: Select all

fetch(d={%dbname}) {%foundnames}

You could also use either of the strings in a for loop to do something a bit more complicated. For example, if we wanted to fetch the series found in the database with a suffix '_fetched' added to the end of the name, we could do the following:

Code: Select all

for %obname {%foundnames} copy {%dbname}::{%obname} {%obname}_fetched next

I hope that makes things a bit clearer.

[javiersan]

Thanks Chris, the code is clear now. I'm however a bit puzzled that it works! I suppose that the bit %foundnames = @wlookup(@wcross(%dbname + "::", %seriesnames))
only works if the %dbname is in the database registry.

Thanks,

Javier

[EViews Chris]

Yes, I assumed that dbname is the database registry or that you have already 'dbopen'ed the database (which will also allow you to refer objects in the database by 'dbname::seriesname'.

The full pathname of the (unopened) database file might also work since we support this in most circumstances.