ODBC Machine DSN

[mboldin]

I wanted to be sure I understand the ODBC teminology. The manual says use

wfopen(type="ODBC") if for DSN Source
and wfopen(type="DSN") is for DSN file

I assume DSN Source is what Microsoft calls a User DSN and others call a Machine DSN and it is used without quotes

wfopen(type="ODBC") MyODBC "select * from table1;"

I seem to be able to substitute a File DSN here
wfopen(type="ODBC") "C:\FileDSN.dsn" "select * from table1 where year(date) >= 2010;"
or use
wfopen(type="DSN") "C:\FileDSN.dsn" "select * from table1 where year(date) >= 2010;"
but type="DSN" will not read a Machine DSN (without quotes)

Is this right?

I mainly ask because I am trying to use the same basic syntax with import to bring date via ODBC into an existing workfile
and had some unexpected results and I was not sure if it was from how dates were lining up or the ODBC vs DSN syntax was creating a problem.

[EViews Steve]

The "type=DSN" option is just a way to tell EViews that you want to use a file based DSN somewhere on your filesystem, without having to specify the file extension (which is a minor convenience I know):

Code: Select all

wfopen(type=dsn) c:\files\mydsn_file wfopen c:\files\mydsn_file.dsn

But frankly, you can use the "type=ODBC" option just as easily and this will look for DSNs under "User", "System", and a file based DSN as well.

Code: Select all

wfopen(type=odbc) mydsn_user wfopen(type=odbc) mydsn_system wfopen(type=odbc) "c:\files\mydsn_file"

One important note: If you are using "System" DSNs, those are specific to 32-bit or 64-bit ODBC. Meaning you will have to run the proper ODBCAD32.EXE program to manage these (on a 64-bit OS,the 32-bit version of this ODBC management program is under "c:\Windows\SysWow64\ODBCAD32.exe") and you'll have to run it with Admin rights to create/edit these.) So if you're using EViews 32-bit on a 64-bit OS, you'll have to create the System DSN using "c:\Windows\SysWow64\ODBCAD32.exe" instead of the normal "c:\Windows\System32\ODBCAD32.exe".

"User" DSNs seem to be usable by 32-bit and 64-bit clients, but they're only available for the currently logged in user and they will still require you to install the proper 32-bit or 64-bit ODBC drivers.

Steve

[mboldin]

I found some unexpected results -- not hard to fix but maybe my experience will help someone else
This command

Code: Select all

wfopen(type="ODBC") dsnx "select * from stateindex"

creates exactly what I want and properly figures out I have a dated panel (cross sectional time series set) with State as the ID label and date = date
But if I screen to only pull in one state and a date range

Code: Select all

wfopen(type="ODBC") dsnx "select * from stateindex where state='PA' and year(date) > 2000"

the wf is undated

Using
select date,*
fixes this
and surprisingly
where state in ('PA','NJ','DE') and year(date) > 2000"
needs nothing extra, while
where state in ('PA') and year(date) > 2000"
needs date,*

If I run from the command window I will get a dialog box to fix anything in the assumed structure.

One lesson might be to not fully trust wfopen() with potentially complex data structures and us a two step process where wfcreate is used to set the structure, and import runs the ODBC loading step. This requires know the exact date ranges however. Maybe in future EVIEWS versions, wfopen will have options to set the structure ???

[EViews Gareth]

Might be better/easier to use WFOPEN followed by a PAGESTRUCT

[EViews Steve]

Also, you can try specify the ID column using the @ID option:

Code: Select all

wfopen(type="ODBC") dsnx "select * from stateindex where state='PA' and year(date) > 2000" @id date