LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: j thomas

I have an ODBC data source configured for Perforce, which uses the 'Perforce ODBC Driver'.

The data source works in MS Query Analyser and Access but when I try to use a DB Query node I get the error: "Unable to translate ODBC type: -9" (it's finding the data source because I get a different error if I use an invalid data source name).

So do I need to do something to tell LAE about the Perforce ODBC Driver eg copy the dll somewhere?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

First, try a simple query such as
SELECT * FROM tablename

If this works, then your connection the database is correct.

I believe that the error you are getting is due to an invalid data type on a specific column.

What type SQL statement are you trying to execute? Are you running a SELECT, UPDATE or an INSERT?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: j thomas

Hi, that's what I was doing, just a simple test select statement, but it looks to me like it can't find the Perforce database driver?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: j thomas

Ooo, interesting, I tried a few different queries to try to exclude weird datatypes.
The 'counters' table has only 2 columns, both text:
select * from counters;
and
select counter from counters;
returned the same error but
select 1 from counters;
and
select ''+counter from counters;
returned successful results.

Selecting a 'date' type column works but columns MS Access interprets as 'text' and 'memo' seem to need forcing to a string for LAE to handle them.

Any ideas what I can do about this other than explicitly selecting and casting every text column?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: j thomas

Further to above, number fields can be selected without error too.
However, I've spotted that LAE loses the time portion of datetime fields (treats them as dates).

At least it's probably usable for my purposes, for now at least, but any advice to make LAE handle the Perforce data types better would be appreciated, thanks.

Here's a link to the Perforce DB schema; the tables I need to connect to are changes, fixes and jobs:
http://www.perforce.com/perforce/doc...aa_schema.html

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Losing the time portion of the field is a known issue.. it's mentioned in the release notes for v4.1.4. It was done (for now) to maintain backward compatibility.

[QUOTE=j thomas;1085]However, I've spotted that LAE loses the time portion of datetime fields (treats them as dates).
QUOTE]

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: j thomas

I've managed to work around the time problem by retrieving the field as a string then converting it to a timestamp.

The problem I can't work around is that I can't retrieve LONGVARCHAR fields - I've tried
select str(description) from changes;
but I get the error "unable to free statement handle".
If I try
select ''+description from changes;
the query executes but nulls are returned (but I know there's data in there).

Any ideas?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: j thomas

Is it a known issue that LONGVARCHAR fields can't be retrieved via ODBC?
Is there a fix/workaround?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: j thomas

I cannot retrieve data from Perforce that is of type LONGVARCHAR. Among other things this means I cannot retrieve the change description which would be a useful field to have access to.

I've tried casting to various types but nothing has worked so far and I'm out of ideas.

1) Is this a bug?
2) Is there a workaround?
3) Is it related to http://community.mda-data.com/showthread.php?t=187 which is scheduled to be fixed in 5.0?

Thanks in advance for any replies.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

J_thomas, Are you still having problems with the longvarchar in perforce? If so I would recommend you contac DesktopSupport@MDA-DATA.com, to get in touch with the support group. RegardsTimon KoufopoulosMDA Support.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

This is a known issue with MDS Lavastorm Engineering. There are certain datatypes in a class of unicode LAE does not yet support. Relating to ODBC, these include nvarchar, nchar, ntext, uniqueidentifier.

This has been noted and planned for future development of LAE.

-Timon Koufopoulos
MDS Lavastorm Support.