Access, Blobs, and Linking

[Cynthia]

I have a Framework with 3 data sources.  Sybase, Oracle, and Access.  Sybase and Oracle are playing nicely together and linking the Employee ID fields just fine.

Access, however, is another story.

I am trying to get data out of an Access Database. This database is the product of a Training application called iLinc. I cannot change anything about this database, as our trainers use it and our copy gets overwritten every day.

One of the fields in one of the tables in this Access MDB has the Employee ID number in it.  I need to pull this out to determine what classes a user has taken, match them up to classes that are required (which are in the Oracle and Sybase DBs) and report the results to the trainers.

The Employee ID number, in Access, is in a field type in Access that's a Memo field.  Framework Manager is interpreting it as a blob.

I'm trying to cast this field to an Int so that I can link it to the field in Sybase, but no matter what I seem to try, Framework Manager doesn't want to do it.

I can create the link between two Query Subjects, and get no error, but the fields do not link up.

When I try:
cast([Sources].[CM_CLIENT_FIELD_DATA].[ITEM_VALUE],int)
I get
UDA-EE-0094 The operation "cinteger" is invalid for the following combination of data types: "textblob" and "integer"


When I try:
cast_integer([Sources].[CM_CLIENT_FIELD_DATA].[ITEM_VALUE])
I get
UDA-SQL-0219 The function "cast_integer" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.

Perhaps casting isn't my problem? 
I find it odd that the relationship doesn't give me an error, but when I test it and view the results, the two Employee ID fields do not match up, even when they seem to have the same values in them.

I've tried trim on the Access DB:
trim([Sources].[CM_CLIENT_FIELD_DATA].[ITEM_VALUE])
I get:
UDA-SQL-0540 A "trim" expression returning a blob result is not supported.

The field in the Sybase DB is cast as an int just fine.  So I'd like to cast this Access field as an int too.... or, find a way to get these two fields to match in the relationship.

I'd love some ideas, if anyone has any.
Thanks!
Cynthia

[tjohnson3050]

Just a guess, but try to substring out text first, then cast the results of the substring as integer.

[Lynn]

There are some notes about dealing with blobs in the FM user guide (determinants), but not sure any of those address your issue.

I realize you can't change anything in the database, but perhaps adding something is a possibility? A query in the Access database that performs a cInt function on the memo field might allow you to model against that instead of the base table to successfully join.

Another thought is to load the data from Access into Sybase or Oracle and do the conversion upon load. This could have an added benefit of improving performance.

Sorry I don't have any direct suggestions to get around the issue!

[twlarsen]

Adding something to Lynn's suggestion, I'm guessing you can't add anything directly to that access database since it'll be replaced each night.

I've done it before by setting up an access database with links to the other access database tables, and then creating queries off of those tables with calculated fields to modify what you need.  You won't lose the queries on a nightly basis, and by linking it you'll still get the updated data.

A better idea would be to load the data elsewhere and handle the conversion there, but using access and links it's a simpler solution.

[Lynn]

Clever!

[cognostechie]

By default, FM would read only the first 256 characters from any character field. The moment it encounters 257th character or higher, it marks the field as CLOB. Same for BLOB though I don't remember what is the limit on that.

You could use substring function to read only a certain number and that would resolve this issue. In fact, delete that Query subject, create another one with a custom SQL in which use the substring function so that FM would never have the opportunity to mark it a BLOB. The idea is NOT to give FM the ability to interpret it as a BLOB so use the subtring function in the custom SQL.

[Cynthia]

Thanks for the replies.

Friday before I left work, I tried this, and it works in Framework Manager:

cast(left([Sources].[CM_CLIENT_FIELD_DATA].[ITEM_VALUE],6),int)

I can test the data and see what I am looking for and the fields line up and join properly.

HOWEVER, when I try to run the report in Cognos, I get an error:
An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-EE-0092 Data exception occurred. String data has been right truncated.UDA-EE-0092 Data exception occurred.

Ugh.

I cannot modify the original MDB, as it is overwritten every night. I have created another MDB that links and queries the original MDB, but I really didn't want to go that way. I'd have to probably automatically run a query every day to build the table with the data types I need for the Framework.  Which would, I think, require a macro. Which would, I think, require a Windows Schedule Task to automatically open the MDB every day to trigger the macro, then kill Access. And when I've killed Access on our Cognos server, it doesn't shut down cleanly. It leaves the .ldb file and then I can't open the MDB cleaning when I try again. Bleh. The whole server needs to be bounced to get rid of that .ldb file and since it's a production server, the management doesn't like that to happen too often.

I may just have to import that MDB into our Sybase Database using Pervasive Data Integrator, and then use the data from there.  I was wanting to keep the MDB in and of itself, and not have to import it into our Data Warehouse, but it's looking more and more like that may be the only successful option.

Thanks again for all the input.
Cynthia

[blom0344]

2 thoughts:

Try 'substring' as an alternative for the 'left' function. Testing in FM usually is done against the first 25 rows, so that may not be a good test

[the6campbells]

1. the cast operation cannot be pushed to Access
2. cast operations are not supported locally on LOB types nor other functions which end up being done locally. those functions may work when applied to a database where that operation can be pushed to it.
3. the person speculating about char lengths is wrong
4. use a function is pushed to access and will return the contents of the LOB as a varchar/char(n) vs a LOB type.

[blom0344]

3. the person speculating about char lengths is wrong

Please let us keep things civil.  'The person' is another forum member who's opinion counts. If you think he/she is wrong provide arguments!

[cognostechie]

HOWEVER, when I try to run the report in Cognos, I get an error:
An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-EE-0092 Data exception occurred. String data has been right truncated.UDA-EE-0092 Data exception occurred.

If the data type is character, then you might want to check the data in that field for the entire table (each row). FM would mark a character type field as CLOB (Character Large Object) and integer/decimal fields as BLOB (Binary Large Object). Your original post says that the field is Memo type which would be interpreted as Character so FM should have marked it as CLOB, not as BLOB !!

For any character field (presuming it does not have data in any row which is causing it to be a numeric), the following should work.

substring(ltrim(rtrim([Sources].[CM_CLIENT_FIELD_DATA].[ITEM_VALUE)),1,50)

and if the Data type is Memo, why are you casting that as integer ?

Using the Cast function in Cognos does not send the Cast function to the database. Cognos would re-write the SQL depending on the database. CLNG and CSTR are the alternative to Cast in Access. If it does not re-write using those, try marking the query subject as native and use those functions.

[Cynthia]

and if the Data type is Memo, why are you casting that as integer ?

I'm casting it as an int because I'm linking that field to another table that also has an Employee ID number, and in that table it's an int.  So I was trying to match data type.  If I don't need to... I could try casting it to something else, but I think I needed to match data types in a join.

I have some other stuff I've been having to get done before I can get back and read the replies again but thanks. :)

[Cynthia]

2 thoughts:

Try 'substring' as an alternative for the 'left' function. Testing in FM usually is done against the first 25 rows, so that may not be a good test

I'm pretty sure I tried substring at first and just kept getting errors in Framework Manager, that's why I went to the left.

[Cynthia]

By default, FM would read only the first 256 characters from any character field. The moment it encounters 257th character or higher, it marks the field as CLOB. Same for BLOB though I don't remember what is the limit on that.

You could use substring function to read only a certain number and that would resolve this issue. In fact, delete that Query subject, create another one with a custom SQL in which use the substring function so that FM would never have the opportunity to mark it a BLOB. The idea is NOT to give FM the ability to interpret it as a BLOB so use the subtring function in the custom SQL.

Mean like this?

Code Select Expand

Select
CM_CLIENT_FIELD_DATA.USER_ID,
substring(CM_CLIENT_FIELD_DATA.ITEM_VALUE,1,6),
CM_CLIENT_FIELD_DATA.FIELD_ID
From
[Ilink].CM_CLIENT_FIELD_DATA as CM_CLIENT_FIELD_DATA

That gives me this error:

Code Select Expand

CCLException"type="general">RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 3: Syntax error near ",".

If I leave that substring out, and I use a filter on the filter tab:
len([Sources].[CM_CLIENT_FIELD_DATA QS Test].[ITEM_VALUE] )=6

then the query subject tests okay but I haven't tried to join it to any other QS yet.

I swear there are like 3 million ways to do this stuff in FM, and I really don't know whats going to work and what's not.

[Cynthia]

So there's 3 ways to create a Query Subject.

1. Model (Query Subject and Query Items) - Build the query based on the data that exists in the model.
(This is what I was using at first)

2. Data Source (Tables and Columns) - Manually build a SQL query using a single database source.
(This is what I used on my prior post)

3. Stored Procedure
(I have not used this one)

When attempting to use #2, if I select a single table in the Access Database, it creates the QS okay an I can test the data (I haven't tried to join yet, like I said).

However, still with option #2, when I try to bring in multiple Access tables, I get this error:

Code Select Expand

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-69'.
UDA-SQL-0043 The underlying database detected an error during processing the SQL request.
[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysRelationships'.

And like I said, I cannot change the underlying Access MDB, so if I don't have permissions, I guess I can't go that route either.

[cognostechie]

So that's what the problem is ..

I would suggest read the Fm documentation first and get yourself familiar with how a model should be made in FM and then start on it.

You can use #2 method (create Data Source query subjects) , one for each table and then create joins between them in the FM. The reason you are getting error for this is because you have to provide a column name for the substring function.

Select
   CM_CLIENT_FIELD_DATA.USER_ID,
   substring(CM_CLIENT_FIELD_DATA.ITEM_VALUE,1,6),
   CM_CLIENT_FIELD_DATA.FIELD_ID
From
   [Ilink].CM_CLIENT_FIELD_DATA as CM_CLIENT_FIELD_DATA

Model Query subjects are meant to create Business Layer, not to read data from the Database.
and try using ltrim and rtrim in substring function as I mentioned in my previous post.

If you have to cast it as an integer for the join, cast it as integer like this:

cast(substring(ltrim(rtrim(CM_CLIENT_FIELD_DATA.ITEM_VALUE)),1,6),integer)

However, creating a join on a field that has the cast function might result in performance issues.

[Cynthia]

Well, I think I got it all to work. With Casts and Mids and various other things in the Query Subjects.  Just had to get everything in the right place.

Cyn