Put ODBC database in registry and link to series?

[jsheldon]

I think these may be related problems, but maybe not.
1) Can a SQL server database that is accessed through Open Foreign Data As Workfile (ODBC) be put into the EViews database registry for faster/easier access?
2) Is there any way to link a series (or group) created through an ODBC query (see 1) to its source query so that it can be updated from within EViews without having to start all over with Open Foreign Data as Workfile?
Thanks!

[EViews Steve]

Unfortunately, EViews 6 currently does not support putting an ODBC data source in the EViews Database registry. And because of that, you cannot have a workfile that automatically refreshes linked data from an ODBC data source. However, there is a workaround and that make the refresh process easier.

An EViews program can automate the entire task of refreshing your data in your workfile.

Here's a quick example:

Code: Select all

wfopen d:\test.wf1 pageload(type=odbc, page=newpg) mydsn "select * from tableName" copy(overwrite) newpg\c* mainpg\c* pagedelete newpg

The wfopen command opens a pre-existing workfile.

The pageload operation is doing the "Open Foreign Data" operation. "mydsn" is the name of my ODBC DSN and the following query within double quotes is self-evident. If you wish, you can just specify a table name instead of a query (e.g. pageload(type=odbc, page=newpg) whitebox tableName). The table will be loaded into a new page named "newpg". (Note: For this program to work correctly, you cannot already have a page named "newpg" in the workfile. If you do, this command creates it as "newpg1" which will make the next copy command copy from the wrong (presumably older) page.)

The copy operation takes several options (such as overwrite or merge) to control how the actual data copy is performed (see command reference for more detail). In my example, I'm performing an overwrite operation for all objects whose name starts with the prefix "c" and copies them with the same name into my main page named "mainpg".

The final step is to delete the newly created page (newpg).

Anytime you need to refresh your data, you can load up this program and run it. Or you can schedule it to run using Windows Scheduler to kick off EViews with the name of this program file to automatically run it (e.g. eviews6.exe c:\refresh.prg) . In this case you'll probably want to save the workfile (wfsave) and then shutdown EViews (exit) in the last line of the program.

[Andrew Scott]

Is there a way to do the above import, but rather than specify a dsn and an sql command string, use an MSQuery *.dqy file?

[EViews Steve]

Sorry, but no. DQY files are not supported by EViews.

[Dhaksha]

Thank you for all the helpful info.

Is there a way to accomplish the same results but by using the Server name and database name instead of using an ODBC connection.

[EViews Steve]

The DSN works for all ODBC data sources, but we do make special allowances for 3 specific database vendors, namely, SQL Server, Oracle, and DB2.

With those vendors you can do the following:

Code: Select all

wfload(type=sqlserver, server=servername, user=username, password=pwd) DatabaseName "select * from TableName"

You can change type to oracle or db2 if you have a different database.

Steve

[Dhaksha]

Thank you Steve.

I need to connect to KDB from EVIEWS. Any known workarounds?

There is a KDB+ ODBC driver available for download from the vendor (http://kx.com/q/w32/) but it does not store the user and password infomation. However the ODBC driver supports q.



This is some infomation that I downloaded from the kx site.
----------------------------------------------------------------------------------------
2008.07.13 multiple open connections. also: "DSN=host:port;UID=..;PWD=..;"
[we can use odbc.k to load other databases into kdb+.]

this file is about the kdb+ odbc driver. odbc is deprecated.
if you can please use http://kx.com/q/c/readme.txt e.g.
from java use c.java
from .net use c.cs

we might still use odbc to get data into old excel/vb's.
1. install: http://kx.com/q/c/odbc/odbc.exe
2. connect: "DRIVER=kdb+;DBQ=host:port;UID=usr;PWD=pwd;"
3. if you make a fileDSN, PWD has to added manually. odbcad32 deletes it.

e.g. >q sp.q -p 5001

the default language for odbc is sql. to use q we execute("q)..");

from excel: (getexternaldata/newdatabasequery)
=SQL.REQUEST("DRIVER=kdb+;DBQ=localhost:5001;UID=usr;PWD=pwd;",,,"select*from t")

from vb: (add-ins/visual data manager/file/opendatabase/odbc)
r=new adodb.recordset
r.Open "select*from t","DRIVER=kdb+;DBQ=localhost:5001;UID=usr;PWD=pwd;"

or
connQ.Open"Provider=MSDASQL.1;DRIVER=kdb+;DBQ=localhost:5001;UID=usr;PWD=pwd;"

[EViews Steve]

Actually, after reading your copied text from the kx site, it looks like it is possible to store the username and password in the DSN. However, you need to manually edit the DSN text file after you create it using ODBC Admin because apparently the ODBC wizard doesn't work properly.

Create your KDB+ ODBC DSN using the wizard as a File Type. Once saved, go to that file and open it in notepad. Add your server, user id, and password so your text file looks something like this:

[ODBC]
DRIVER=kdb+;DBQ=localhost:5001;UID=usr;PWD=pwd

(make sure you change your DBQ to point to your database:port, and change UID and PWD to your username and password)

Save this edited DSN, making sure you don't change the extension.

Then run EViews and use the name of your DSN (should be the same as the name of your text file) in your wfopen call (or wfsave, etc).

Also, there was a bug reported in using EViews with KDB+ that we fixed around July 2009. If your build of EViews is older than that, please download the latest patch update from our website to get the fix.

Steve

[EViews Steve]

Actually, one change -- the odbc dsn text file might have to look like this instead:

[ODBC]
DRIVER=kdb+
DBQ=localhost:5001
UID=usr
PWD=pwd

And when you use the DSN in EViews, specify the full path to it like this:

wfopen(type=odbc) d:\dsns\myconnection.dsn "select * from tablename"

Steve