-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ian Stuart Sent: Wednesday, May 11, 2005 3:38 PM EDT To: u2-users@listserver.u2ug.org Subject: {Virus?} Re: [U2] OpenOffice.org 1.9.100 and UniVerse JDBC
******************************************************************* WARNING: THIS MESSAGE HAS HAD ONE OR MORE ATTACHMENTS REMOVED WARNING: (OPENOFFICE.ORG BASE.LNK). WARNING: PLEASE READ THE "VIRUSWARNING.TXT" ATTACHMENT(S) FOR MORE INFORMATION. ******************************************************************* That's the point; one sometime feels completely helpless in the face of large corporations and the disinterested, stone-walling answers one gets. I agree whole-heartedly - I'll live with being flamed if I can eventually frame a meaningful question and communicate with someone who cares enouigh about where and how their software is being used and, just as important, how the user wants to be able to use the software. To use OOo with UniVerse: Make sure you have jdbc installed on your server. On our AIX box it's in /usr/ibm/unishared/jdbc. See, we do actually like IBM products. Your UniVerse files must have @SELECT phrases created in the DICTS and HS.UPDATE.FILEINFO must have been run in the account, if it is not a schema - just like for ODBC. It's best to create a new account with pointers to the data and local copies of dictionaries that do not have "." in the names of the fields or file names - OOo assumes certain things about table names that are "." delimitered and strange results are obtained in the SQL it generates when joining files. Schemas and Tables are "automatically" accessible. At this time I'm still battling with JDBC not interpreting dates as DATE columns. To get around this convert dates to the form YYYYMMDD via an itype specified as INTEGER SQL datatype. Make use of field 8 in the DICT to specify the SQL data type eg, INTEGER, NUMERIC,11,2 CHAR,20 etc. as JDBC does not seem to correctly use the CONV definition. JDBC seems to be a lot less strict than ODBC but also is'nt as clever in all respects (I think?, maybe) Download the Windows and/or Linux/Solaris version of OOo.1.9.100 (2.0 beta) - this version incorporates three changes specifically requested for UniVerse including not aliasing table names in queries to the same name, which may seem redundant but is a common implementation for other database SQL query tools -(do you think M$ would respond to a request from one lone voice in the wilderness and change MSQuery or Access to do something like this) Click on this link http://www.openoffice.org/issues/show_bug.cgi?id=15113 and look for and download the attachment called Universe settings.bas - this is a macro you will need to modify slightly, install and run once later for each datasource you will be creating. Make sure you have a JRE installed - the latest possible version Install OOo on your system - on a P4 2.4Ghz on windows this takes less than 15 minutes and does not require a reboot. Install UniVerse client software (objects JDBC etc) Fire up OOo - any application, spreadsheet, word etc ( or right-clock on OOo image on taskbar and select Database if on windows or run sbase.exe) Click File\New\Database Check "connect to existing database" and select JDBC and click Next In the Data Source Url field enter "ibm-u2://HostNameOrIPAddress/PathNameORAccountNameORSchemaName". In the JDBC driver class field enter "com.ibm.u2.jdbc.UniJDBCDriver". Click Test Class - you should get a dialog confirming it was able to load the class Click Next and specify a user name and check the password required check box Click Test Connection - if it works, great you have successfully connected to UniVerse via JDBC - if not check out the account name and/or path Click Next Check Register database Check Open database for editing - do this only if you really want to use JDBC to create new tables. Now for the tricky bit: Depending on what windows platform you are on, you will need to copy the .odb file to an accessible path - I could'nt get OOo to work with a pathname with spaces, maybe somone can help out here. The .odb file is a compressed xml file, database document. If your datasource was created as MYDATABASE then a file called MYDATABASE.odb will be in Documents and Settings\User\My Documents. On Linux copy this to your home directory. If OOo is up, click on Tools\Macros\Organise\OpenOffiice.org Basic Create a new Module under My Macros (I called mine UV - how original) Copy in the source code from the Universe settings.bas you downloaded earlier and make the following changes to the macro (Oh, what the hell, here is the source code for the macro) REM ***** BASIC ***** Sub Main oDBC = createUnoService( "com.sun.star.sdb.DatabaseContext" ) oDB = oDBC.getByName( "file:///C:/arjdbc.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 -------------------------------------------------------------------- In the macro change the line that reads oDB = oDBC.getByName( "file:///C:/arjdbc.odb" ) to reference the path to your .odb file Save the macro and run it. If it fails check the path to the file. Copy the .odb file back to it's orignal location. Open the .odb file (double click it or access it via the OOo menus) and you should be able see your tables. If you can't see the tables then the macro probably did'nt run. Feel free to email me directly although I will be away from tomorrow until the 19th. May, or if you are in South Africa you can phone me. I'll send you my number via email.. Unfortunately the current OOo ODBC does not work with UniVerse although the earlier StarOffice 5.2 does as well as earlier versions of OOo. To be honest, ODBC actually worked better than the current JDBC, but with a bit of massaging of dicts wonders can be achieved. Join OpenOffice.org and free yourself and your company from enslavement to that other monopoly. Ian Stuart Wendy Smoak wrote: >Ian Stuart wrote: > > >>Compare this to OOo response: After being elated about the success of >>the JDBC patch in OOo I found a problem updating tables >>viafile:///C:/Documents%20and%20Settings/All%20Users/Start%20Menu/Prog >>rams/OpenOffice.org%201.9.100/OpenOffice.org%20Base.lnk >><cid:[EMAIL PROTECTED]> >> > > > >>JDBC, so I posted an issue. Not 1 hour later I get a meaningful >>response along these lines: >> >> > >This isn't anything specific to OpenOffice, it happens daily in dozens >of open source projects. And it completely spoils you for "traditional" >software support. ;) > >I'm now used to being able to talk to the developers of whatever it is >I'm using, have learned to ask good questions, and I expect to be taken >seriously. Contrast that with having to fight your way through >first-level phone support in order to get to someone who *might* know >the answer when you have a problem with commercial software. > >I haven't used the JDBC driver, but I have had issues with UniObjects >for Java that were only solved by begging for help here on the list, >not through official support channels. ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ ____________________________________________________________ CONFIDENTIALITY This e-mail and any attachments are confidential and also may be privileged. If you are not the named recipient, or have otherwise received this communication in error, please delete it from your inbox, notify the sender immediately, and do not disclose its contents to any other person, use them for any purpose, or store or copy them in any medium. Thank you for your cooperation. ____________________________________________________________ ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/