Hi,

In regards to the linear hash ODBC driver, that is available as a seperate product to plug into OI 32 bit. What are the practical applications for accessing the LH data, with the ODBC driver, for use by M$ tools like Access, Excel and Word ?

To my (very limited) knowledge of ODBC drivers, I don't _think_ one could just use the 32 bit ODBC driver and access the LH data directly from these M$ products. You usually need another layer between the driver and the M$ tool, right ?

Leading on from my assumption, one would need either OI 32 bit and then run the M$ tool, parsing the LH data, or have some other tool/software between the ODBC driver and the M$ tool/s (like VB, Delphi,etc).

Is there a url to show the functionality of the 32 bit LH ODBC driver, please ?

Peter

Peter,

The ODBC driver lets you take front end tools (like Crystal Reports) and use the ODBC interface to extract data from Open Insight data, as if it had been stored in columnar x-y style tables.

So think of it as a pipeline. No features. Not much configuration. Just a pipeline between something that speaks ODBC and your LH data. When you go to Open an ODBC data source (registered in Windows) you will see Revelation LH Data among the data source options.

The ODBC driver has been demonstrated in several places by Mike Ruane - the first prototype demo was at the Las Vegas Conference last July.

Steve

The Sprezzatura Group

World Leaders in all things RevSoft

Hi Steve,

] The ODBC driver lets you take front end tools (like Crystal Reports)

] and use the ODBC interface to extract data from Open Insight data,

] as if it had been stored in columnar x-y style tables.

When you say "ODBC interface", I assume you mean what I have sometimes used in OI, under "Client/Server Workspace | Connection". If the OI 32 bit ODBC driver was purchased and installed (usually only a .DLL anyway), then yes (following on from my assumption), I could use it as a Developer, but what would be required for an 'end user' to use the ODBC driver ? It would appear there would need to be forms/code added to the OI application, so the user has a nice interface, to see the data. Or, can it be used like:

LH_Data ODBC_Driver Front end tool

I'm also assuming the driver let's a user do read-only requests.

Can you use it with M$ Word, Excel, Access ? What would be required to do this, obviously some VBA scripting or similar, to make the connect ? From the Access help file:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"In Microsoft Access, you can import, export, or link data from a number of different database, spreadsheet, and text file formats. To do this, Access uses either a built-in driver or an ODBC driver. This topic provides reference information about:

ODBC drivers

You can use ODBC drivers to connect to Microsoft SQL Server databases, Microsoft FoxPro databases, and data from other programs that provide 32-bit drivers in compliance with ODBC Level 1 to access their data files. Microsoft Access automatically installs the Microsoft SQL Server ODBC driver (Sqlsrv32.dll), the FoxPro ODBC driver (vfpodbc.dll), the Oracle ODBC driver (msorcl32.dll), and some ODBC support files"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

] as if it had been stored in columnar x-y style tables.

How is the MV data represented, as ODBC technology is suited usually to 'true" RDB tables, with just rows and columns, but, as we know, the Revsoft LH data is more powerful than that. Are the MV columns displayed to the end user as multiple rows, or just one row per record, with the MV columns represented somehow ? (MV and SMV delimiters removed).

] So think of it as a pipeline. No features. Not much configuration.

] Just a pipeline between something that speaks ODBC and your LH data.

] When you go to Open an ODBC data source (registered in Windows) you

] will see Revelation LH Data among the data source options.

Okay, just like the other drivers are now.

] The ODBC driver has been demonstrated in several places by Mike

] Ruane - the first prototype demo was at the Las Vegas Conference

] last July.

It would be great if there was more information on it, I'm trying to help a user decide between Revelation Reporter and the ODBC. He thinks the ODBC driver is the 'answer' to his problem, and is only looing at the purchase price. But if he has to pay someone to either add extras to the OI application, or pay for someone to setup Excel and Access ODBC connectivity for him, then his outlay will change considerably.

Many thanks (neighbour ),

Peter

Peter,

Some of your questions seem to be focused on doing something in OI. Rather we are talking about 'doing something in ..', we are pulling data out of OI into the active reporting system rather than pushing data from OI. So with the ODBC driver installed, and enabled for various OI tables we can then open say Crystal Reports, and now select table, row / column for reports. That is, there is no vb work to do (well that's been done in the driver), just select, order, manipulate the data as you would do with any other report in Crystal Reports.

I guess you could do something similar to generate graphic reports in Excel. Again, no deep programming for a simple report.

Note: the driver at present is one-way. The action is all at the 'other' end, so being a runtime or a development system has not much relevance. (I havnt yet played with the ODBC driver but I from memory you would still need to set up ODBC environment and deploy that environment to Runtime system. - some weeks since I read the manual.)

The licence is on a concurrent users basis. So you could have, say a 10 user OI runtime with x2 ODBC licences. - well how many users are doing reports all at the same time! Of cause the costing is such that if you want more than x1 licence you may as well jump straight to the 5 user.

Now Sprez or RevSoft, if I have told it wrong, please step in and correct.

Assuming you have the ODBC connectivity installed in your Office suite (it's so configurable these days you might not ) then you can just report on OI data straight out of Excel, Word etc etc. It just becomes another Data Source.

The Sprezzatura Group

World Leaders in all things RevSoft

Hi,

] Assuming you have the ODBC connectivity installed in your Office

] suite (it's so configurable these days you might not ) then you

] can just report on OI data straight out of Excel, Word etc etc. It

] just becomes another Data Source.

Thanks, that's basically answered my question. As a client has Excel and Access, he is trying to decide between Rev Reporter and the ODBC. At first it seemed like it was only ODBC/Crystal, which would mean a lot of money for him, but now from what you have said, the client can simply install the ODBC driver, and use his current reporting tools (Excel & Access) to run reports on the LH data.

That gives him a much better idea of how to choose the right product, thanks. Possibly RevSoft could place a small article about the use of the ODBC driver, because the only official information I can find is at http://www.revelation.com/WebSite/vipweb.nsf/85255e6f0052055f852558ac00697645/e8c264f124e5444b85256c8d006759af?OpenDocument , which states up the top "The ODBC driver requires OpenInsight 4.1 or Above." , which gives someone (dumb old me !!) the impression that you need OI to use the ODBC driver. But from what you have stated, you don't, which is actually what I would have expected.

Thanks,

Peter

Apologies - in trying to help we've actually obfuscated. The ODBC driver uses an OI license WHILST active. If there is no OI there already it won't work. It does not include a lanpack/bump/deployment. But if there's no OI there why is there OI data there? No it DOES need an OI 4.1+ so it WON'T work against older OIs nor AREV.

The Sprezzatura Group

World Leaders in all things RevSoft

Hi,

] The ODBC driver uses an OI license WHILST active. If there is no OI

] there already it won't work.

Okay, obviously done for licensing reasons, or to access the OEngine to control locking,etc. Thanks for clearing that up.

So, userA, who has OI 32 bit installed on his machine, cannot send his data to userB, for userB to run a 3rd party reporting tool with the ODBC driver, even though userB would have paid for an ODBC driver. Okay, bit annoying, in certain situations, people in remote locations may want to access the LH data, and not have to install the complete OI application. Oh well !! :)

] It does not include a lanpack/bump/deployment. But if there's no OI

] there why is there OI data there?

Possibly from an AREV application. Unless of course, going to the 32 bit has resulted in some changes to the LH data structure, which I would doubt.

] No it DOES need an OI 4.1+ so it WON'T work against older OIs nor AREV.

But would work on AREV or older OI's data (REV…. tables), as long as the OI 32 bit was installed, right ? Do you need a complete OI 32 bit deployment, or just the Engine and a few .DLL's ? Just thinking of a cut down deployment, for simplicity, where all the end user wants to do is report from his 3rd party tool (after purchasing the ODBC driver).

Thanks,

Peter

Quite. The ODBC driver is not a all-in-one link between LH files and any other system that can register the ODBC driver. Just think of the issues THAT would create for all but the simplist data structures, what with mfs, indexes, symbolics etc, let alone security and record locking. As Sprez says, you need OIv4.12 live to provide the processes, symbolic calculations, locking, security etc. The 'live system' can be a runtime - provided at no additional cost to the OI developer.

By definition there ARE changes to the LH structure going from 16bit to 32bit. Why do you think we are all so pleased with OI32bit? - the file system breaks beyond the constraints of 16bit world. Now, for small filing systems, the change may be theoretical. Sprez generously made available to the Rev community mfs code which would allow the co-sharing of 16bit and 32bit LH systems.

Of cause, you could wait to the release of Arev 4.0 with full 32bit functioning…

Richard Bright

BrightIdeas New Zealand

…In regards to the linear hash ODBC driver, that is available as a seperate product to plug into OI 32 bit…

Could somebody please direct me to the web page with information about the LH ODBC driver?

- Oystein -

Hi Oystein,

]] Could somebody please direct me to the web page with information about the LH ODBC driver? ]]

Yes, I would like a lot more information also, from the web site. It seems there are many additional layers involved in using the RevSoft LH ODBC driver, as opposed to the other ODBC drivers I have used. Traditional ODBC usually only have the ODBC driver between the data and the 'tool' (whether the tool be a reporter, an application, a system, etc), quite simple, such as:

 Tool -] ODBC Driver -] Data

Even using ADO, the layers are usually not more complex. I'm not writing this as a flame, I'm just saying the RevSoft ODBC driver appears to be (to dumb old me) very much different to classic ODBC models. There have been reasons explained for this (MFS, locking, indexes, symbolics), but isn't all that in the (LH) data and dictionaries anyway, so it would be logical to have the ODBC driver handle more of that, rather than rely on OI 32 bit running. Can I assume that the (obvious to the more technically minded) reason is that the 32 bit OEngine needs to be running, to handle the queueing and processing of requests ?

I appeciate everyone's response to the questions on ODBC. If RevSoft could possibly provide some information on the web site about the ODBC driver, what it does, why it does it that way, constraints with the use of the driver, licensing issues, a graphical overview of the layers/tiers in the architecture of the ODBC functionality, etc, etc.

There could be a marketting side of this also (in the web doc). Most people have responded and mentioned Crystal Reports, as a suggestion to 'reporting tools' to use with the driver. However, there are many OI users I know who have the M$ products like Access/Excel already, and if it was explained that these products can also be used (with examples of how it is done), then it would make a significant difference to the end user making the final decision on whether to buy the driver ($$$$ speak loader than words).

Thanks,

Peter

We have purchased the odbc driver and are unable to get anyuse of it.

We are not able to report on any files with excell Revelation has been unable to assist us. The problem as stated by them is in the oconv of the dictionaries.

Dave Hatcher

What about the OCONV? The ODBC driver does have certain limitations imposed upon it my fixed length environments such as needing to define datatypes correctly but once this is done there should be no problems.

What are your issues?

The Sprezzatura Group

World Leaders in all things RevSoft

We just purchased and believe we have many more clients who will want it.

So far we are unable to use it for any purpose. I suppose we could be the reason it's not working. Could you describe the conditions we need to careful of. Such as data types. If we try to access our Inventory file it fails (as do all our files) so perhaps I could start there. What types of limitations does it have.

Should I create new files and copt the required data into those.

Thanks

Dave

Well all fields need to be ACCURATELY datatyped so that the ODBC driver can convert from OI32s string data to the data type the environment expects. This requires the setting aside of memory so if all of your fields are datatyped as VARCHAR(65535) you will crash the driver as you'll run out of memory pretty quickly!

The Sprezzatura Group

World Leaders in all things RevSoft

Excel has a problem interpreting the OI data dictionary's output conversion. The error is not on each conversion pattern. It likes 'MD2,' but not 'MD2,$'. It does not like the output conversion patterns of PHONE_FORMAT and its kind. Excel also has a problem with a VARCHAR655351 - change this to VARCHAR255.

Sean

As a separate "Heads up" the DATATYPE will have to match the format chosen too - just something people used to typeless data may overlook.

The Sprezzatura Group

World Leaders in all things RevSoft

Sean,

]

The VARCHAR problem concerns me. I just did a search on volume DATAVOL, looking in the dictionaries for string 'VARCHAR', and resulted in finding 29 occurances. Opened just one of the dictionary files, and found 38 occurances of VARCHAR.

What are the implications to the (Excel ) user ? Can the problem be fixed in Excel ? We certainly wouldn't want to reduce the length of the VARCHAR columns in the OI application (data truncation).

Peter

OI remains variable length - you need to tell Excel what the maximum length will be then this will be truncated in the Excel report if the OI data is longer than Excel expects. Datatypes are used to control the external export not the internal use. Remember the ODBC driver is delivering data into a fixed length environment and Excel is going to prereserve that space. It will not appreciate being told to reserve space for a report where every cell of every row is 64K.

The Sprezzatura Group

World Leaders in all things RevSoft

Hi,

]

Thanks, as you can see, the way this thread is progressing, the obvious need for both a technical and end user document from RevSoft, on the functionality of the ODBC driver.

First my impressions were that the use of Excel is very simple. Most users will not want to spend $$ on Crysal when they (might) already have Excel, Access, etc. But now I can see that the end user could purchase the product, to use with Excel, and not be aware of the significant constraints with how the (final) data is represented in Excel.

The user should be told they can use Excel, but also told there will most likely be data truncation, or even data misrepresented (the MD.. issue). How much data will be affected ? How many rows are 64K, and how many rows are in the table to be reported upon ? It is one thing to see a demo, but we all know demos are just that. The demo would need to be on the end users data, otherwise we could be trying to mislead the end user (unintentionaly).

Peter

View this thread on the forum...