Unfortunately the report whose ID you mentioned does not contain any useful
data. Currently waiting for Ian's feedback to (similar) issue 75444, I have hope
we can both prove to be duplicate to issue 74732.

No, it seems that 75442 is still a problem, ie ODBC connections to the same
datasources as accessed via JDBC fail although the symtoms are now different.

However all is not lost as there is definitely an improvement over previous OOo
versions and testing I've done with UniVerse.

As background - UniVerse supports 2 types of SQL "schemas" 1. a "true" SQL
schema with Catalog defintions, triggers, constraints, views etc which are
enforced by the DBMS and 2. a "free-form" "ACCOUNT" which enables the user to
create non-SQL tables/files,  and implement rules in client applications only
(although triggers are also supported).  (A third option also exists in that
non-SQL tables can be created in SQL Schemas - used for speed eg hashed tables).
 However non-SQL files are "sqlised" on the fly by the ODBC driver as long as
the dictionaries of these files have been massaged to make the data ODBC
compliant and the Account is marked as accessible via ODBC.

The symptoms are different when connecting to a SQL schema vs Universe ACCOUNT
as follows:

Connecting to "true" SQL Schema:

I can connect to the data source but no tables are shown.  Trying to select
Query facilities, eg Create Query in SQL View does not enable me to access the
database or create queries.  This is unusual as, for example, "true" UniVerse
SQL Schemas are more easily accessed via ODBC applications than UniVerse
Accounts as the Schema and catalog take care of everything, ie there is no
manual intervention required to make non-SQL tables accessible via ODBC.  I have
also tested that the username has been GRANTed the correct priveleges.

Also, I do get the same error message as reported in 75442 when OOo crashes.

Connecting to an UniVerse Account:

The behaviour changes slightly here: While OOo does not retrieve/display the
table definitions as for SQL Schemas,  I am able to create a query in Create
Query in SQL View and retrieve data from the table - however trying to then
enter design view results in a message stating that the "CUSTOMER_MASTER" is not
a table or a query.  I have tried disabling schema and catalog names in Advanced
Settings but that makes no difference.

In an Account I can also access the facilities to create tables and the correct
data types for columns is shown, which is not the case with the JDBC,  although
UniVerse disallows actual creation of the tables as SQL DML is not permitted in
UniVerse accounts.

In both cases (Schemas and Accounts) if I check on the server a connection has
been made and a call to obtain the schema details is made.

Regards
Ian Stuart

Hi Frank,
If it were possible to provide you with a copy of the most recent release of
UniVerse and sample databases would you be willing to have another look at the
ODBC and other JDBC issues.  UniVerse 10.2 has a much easier installation
process with easier to use client tools to manage the DBMS. 

If so how would I get the CDs to you.

I really think you are close to having the ODBC work - it works with nonSQL
Schemas in that I can create SQL queries;  the JDBC it seems is working quite ok
except for not being able to create databases which at this time is not a real
issue but would be nice to have.

Also, UniVerse 10.2 has a built in Web Services server - would it be possible to
retrieve data via SOAP/Web Services

I'm meeting with Susie Siegusmund who heads up the U2 (UniVerse & UniData)
database division on 16th April - I will also ask her whether IBM would be
willing to provide some resources - would this be acceptable to you if they
could provide some technical input - how would they contact you.  The IBM U2
team port Universe to Solaris, AIX, HP-UX systems as well as Redhat/SUSE and
Windows.

If I knew where to start looking in OOo I would , but I think the complexity of
OOo is going to put any meaningful input outside of my reach.
Regards
Ian Stuart

sorry for the delay in response, there were some bank holidays here ...

Yes, if I could evade the pain of installing Universe again, this would would
increase my willingness to look into the problems - the easier to install, the
better. I cannot do any promises, since, honestly, Universe support isn't
requested too often, which effectively means its priority is not very high (hey,
what about your company sponsoring a developer (for a month or so) who looks
into the problems, with our help?). So, please take this as expression of
willingness, but no promise.

> If so how would I get the CDs to you.

If you cannot put them online for download somewhere, I could mail you my
business address.

> Also, UniVerse 10.2 has a built in Web Services server - would it be
> possible to retrieve data via SOAP/Web Services

There's no driver for this kind of data currently.

> I'm meeting with Susie Siegusmund who heads up the U2 (UniVerse &
> UniData) database division on 16th April - I will also ask her
> whether IBM would be willing to provide some resources - would
> this be acceptable to you if they could provide some technical
> input - how would they contact you.  The IBM U2 team port Universe
> to Solaris, AIX, HP-UX systems as well as Redhat/SUSE and Windows.

This sounds even better. I know that IBM has a great interest in OpenOffice.org,
so it might be worth asking them whether they are willing to sponsor somebody
investigating the problems.
In any case, dev@dba.openoffice.org is the public place for somebody embarking
on this.

> If I knew where to start looking in OOo I would , but I think the
> complexity of OOo is going to put any meaningful input outside of my
> reach.

I currently implement some logging facilities in some selected SDBC drivers in
OOo - definitely the JDBC bridge, not yet sure about the ODBC bridge. My hope is
that this can help in the future to do some remote investigations.

Hi
Had a reasonably positive meeting with the IBM and the local distributor of U2
products re assisting with this issue and others related to JDBC and ODBC

Initially, the distributor has agreed to provide a technical support person to
assist who will also have contact with IBM.

What skills level do you envisage this person will need to have, eg should they
be C++ proficient etc.

Regarding logs - how does one turn the SDBC logging on if it is available at OOo 2.2

Regards
Ian Stuart

Ian, sorry for the delay, have been off for a while (and probably will the next
weeks, again, so you'll find me responding irregularily only).

Well, somebody with the ability to debug the problem would be ideal, of course.
In this case s/he could try to find out what's going on, and we could discuss
possible technical solutions. So, if you have somebody with C++ background,
http://wiki.services.openoffice.org/wiki/O3-build could allow to kickstart
compiling OOo. I'd be glad to discuss pointers where to look in the code in
dev@dba.openoffice.org.

Re logging: That's not available in 2.2, yet, it is near before integration into
the upcoming developer builds (which would be sufficient for the purpose of
obtaining a log, I suppose). However, I did not finish it, yet, and for the
absense reasons mentioned above, I might need a few more weeks. Perhaps I am
able to publish a preliminary build with those logging capabilities, will
evaluate. Linux and Windows non-product
(http://wiki.services.openoffice.org/wiki/Non_Product_Build) might be easy to do.

Ian, with snapshot build 680m218 (should materialize soon at
http://download.openoffice.org/680/index.html), you can turn on JDBC logging as
described in http://wiki.services.openoffice.org/wiki/Logging_JDBC_Activity.
Perhaps this can give us some insights.

Argh. The logging is for JDBC, and this issue is about ODBC, isn't it? Sorry,
this will not help us, then.

does this problem still occure in a current version?

Yes, this still occurs at OOo 2.4.0 as described 16 Mar 2007.

Many thanks for keeping this issue open

change owner

Have tested UniVerse ODBC connection with OOo 3.0.0 OOO300m8 with some success
and some previously experienced issues remain.

1. After defining the data source it is still necessary to run the following
macro before attempting to connect to the datasource

REM  *****  BASIC  *****

Sub Main
	oDBC = createUnoService( "com.sun.star.sdb.DatabaseContext" )
	oDB = oDBC.getByName( "file:///C:/path/to/database/document.odb" )

    Dim nFilterMode as Integer
    nFilterMode = 0
    AddDataSourceSetting( oDB, "TableTypeFilterMode", nFilterMode )

    Dim bBeRespectful As Boolean
    bBeRespectful = TRUE
    AddDataSourceSetting( oDB, "RespectDriverResultSetType", bBeRespectful )

    oDB.DatabaseDocument.store()
End Sub

Function AddDataSourceSetting( oDB as Object, sSettingsName as String,
aSettingsValue as Variant ) as 

Variant
    ' append the new setting
    Dim aInfo as Variant
    aInfo = oDB.Info

    aInfo = AddInfo( aInfo, sSettingsName, aSettingsValue )

    oDB.Info = aInfo
End Function

Function AddInfo( aOldInfo() as new
com.sun.star.beans.PropertyValue,sSettingsName as String, 

aSettingsValue as Variant ) as Variant
    Dim nLower as Integer
    Dim nUpper as Integer
    nLower = LBound( aOldInfo() )
    nUpper = UBound( aOldInfo() )

    ' look if the setting is already present
    Dim bNeedAdd as Boolean
    bNeedAdd = TRUE

    Dim i As Integer
    For i = nLower To nUpper
        If ( aOldInfo( i ).Name = sSettingsName ) Then
            aOldInfo( i ).Value = aSettingsValue
            bNeedAdd = FALSE
        End If
    Next i

    ' allocate the new array
    Dim nNewSize as Integer
    nNewSize = ( nUpper - nLower )
    If bNeedAdd Then nNewSize = nNewSize + 1
    Dim aNewInfo( nNewSize ) as new com.sun.star.beans.PropertyValue

    ' copy the elements (a simply copy does not work in Basic)
    For i = nLower To nUpper
        aNewInfo( i ) = aOldInfo( i )
    Next i

    ' append the new setting, if necessary
    If ( bNeedAdd ) Then
        aNewInfo( nUpper + 1 ).Name = sSettingsName
        aNewInfo( nUpper + 1 ).Value = aSettingsValue
    End If

    AddInfo = aNewInfo()
End Function


Once this macro has been run it is then possible to connect to the database
without OOo crashing.

2. Change the database advanced settings not to use Catalog name 

Thereafter it is possible to create an SQL  using Create Query in SQL View and
selecting Run SQL command directly.

Remaining problems include:

When using Create Query in Design View, all the tables and columns are shown and
can be joined and columns selected for the query.  However on running the query
or when attempting to switch design view off OOo crashes.

The Query Wizard crashes on "Finish" with the following error dialog

Runtime Error! 
Program c:\Program Files\OpenOffice.org 3\program\soffice.bin
R6025
- pure virtual fucntion call