Re: [U2] Universe and SQL
I wrote a small article way back in 2007 using the ECL CONNECT verb and the ODBC driver to access mysql. http://mvdbs.com/2007/10/08/universe-odbc-to-mysql/ It works OK. djm WJhonson wrote: > > > But can Universe, access SQL tables which are *not* in Universe? > They are just other SQL tables in other places around the network that > usually is update by other non Pick software. > > Can Universe also access those tables? > - Learn and Do Excel and Share http://mvdbs.com http://mvdbs.com -- View this message in context: http://old.nabble.com/Universe-and-SQL-tp32948798p32958062.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Universe and SQL
Look in the manuals for the BCI - It can access any database with full CRUD , providing you have an odbc library. Or there is EDA - but that may be overkill . -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: 09 December 2011 21:25 To: u2-users@listserver.u2ug.org Subject: [U2] Universe and SQL I see a lot of stuff on Universe accessing it's own files as SQL tables, and also on how you can use OLE to access Universe tables using SQL. But can Universe, access SQL tables which are *not* in Universe? They are just other SQL tables in other places around the network that usually is update by other non Pick software. Can Universe also access those tables? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1415 / Virus Database: 2102/4069 - Release Date: 12/09/11 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Universe and SQL
You can use BCI and it works effectively. You can write basic programs to select and update data on other databases. EDA is another option where you could treat an RDBMS as if it were a UniVerse file. Regards David Jordan -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: Saturday, 10 December 2011 8:25 AM To: u2-users@listserver.u2ug.org Subject: [U2] Universe and SQL I see a lot of stuff on Universe accessing it's own files as SQL tables, and also on how you can use OLE to access Universe tables using SQL. But can Universe, access SQL tables which are *not* in Universe? They are just other SQL tables in other places around the network that usually is update by other non Pick software. Can Universe also access those tables? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Universe and SQL
You will also need a 3rd pary ODBC driver. We use a driver from OpenLink to connect from UV to SQL 2005, and I think others on this list have successfully used EasySoft. I did a trial of EasySoft many years ago but ran into problems getting it to work on RedHat AS 3. Their support was in the UK and it just wasn't worth the frustration waiting a day for each email reply. I'm pretty sure we started using BCI and ODBC at UV 9.6. We're currently on 10.2.7. -John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Colin Alfke Sent: Friday, December 09, 2011 3:00 PM To: 'U2 Users List' Subject: Re: [U2] Universe and SQL Sorry, I'm a UD guy. Not sure when EDA was added to UV - but yes, it is powerful. No, you can't use syntax quite like that. You can use BCI - see the UniVerse BASIC SQL Client Interface Guide for syntax and examples. What it does is create an ODBC connection that you can use - much like below. Colin -Original Message- From: Wjhonson To be clear, I'm only trying to query the database at the moment. EDA looks pretty powerful, IF you upgrade. Can I execute a command in Universe like SELECT NAME, ADDRESS, ZIP FROM someexternaldatabase WHERE DATE = TODAY() ORDER BY DATE Can I do that in Universe 9.6 ? Or 10.2 ? And it will use the built-in SQL processor that comes with Universe ? Not to update the database, just to query from it? And if so, is there documentation on doing this query from an external database ? -Original Message- From: Colin Alfke Sure. They have EDA (originally a number of SQL type DB's but IBM reduced to B2 and now Rocket is adding them back) and BCI - an ODBC connection irectly from Basic. hth olin Alfke algary, Canada -Original Message- rom: Wjhonson see a lot of stuff on Universe accessing it's own files as SQL tables, and lso on how you can use OLE to access Universe tables using SQL. But can Universe, access SQL tables which are *not* in Universe? hey are just other SQL tables in other places around the network that sually is update by other non Pick software. Can Universe also access those tables? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Universe and SQL
Sorry, I'm a UD guy. Not sure when EDA was added to UV - but yes, it is powerful. No, you can't use syntax quite like that. You can use BCI - see the UniVerse BASIC SQL Client Interface Guide for syntax and examples. What it does is create an ODBC connection that you can use - much like below. Colin -Original Message- From: Wjhonson To be clear, I'm only trying to query the database at the moment. EDA looks pretty powerful, IF you upgrade. Can I execute a command in Universe like SELECT NAME, ADDRESS, ZIP FROM someexternaldatabase WHERE DATE = TODAY() ORDER BY DATE Can I do that in Universe 9.6 ? Or 10.2 ? And it will use the built-in SQL processor that comes with Universe ? Not to update the database, just to query from it? And if so, is there documentation on doing this query from an external database ? -Original Message- From: Colin Alfke Sure. They have EDA (originally a number of SQL type DB's but IBM reduced to B2 and now Rocket is adding them back) and BCI - an ODBC connection irectly from Basic. hth olin Alfke algary, Canada -Original Message- rom: Wjhonson see a lot of stuff on Universe accessing it's own files as SQL tables, and lso on how you can use OLE to access Universe tables using SQL. But can Universe, access SQL tables which are *not* in Universe? hey are just other SQL tables in other places around the network that sually is update by other non Pick software. Can Universe also access those tables? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Universe and SQL
To be clear, I'm only trying to query the database at the moment. EDA looks pretty powerful, IF you upgrade. Can I execute a command in Universe like SELECT NAME, ADDRESS, ZIP FROM someexternaldatabase WHERE DATE = TODAY() ORDER BY DATE Can I do that in Universe 9.6 ? Or 10.2 ? And it will use the built-in SQL processor that comes with Universe ? Not to update the database, just to query from it? And if so, is there documentation on doing this query from an external database ? -Original Message- From: Colin Alfke To: 'U2 Users List' Sent: Fri, Dec 9, 2011 1:58 pm Subject: Re: [U2] Universe and SQL Sure. They have EDA (originally a number of SQL type DB's but IBM reduced to B2 and now Rocket is adding them back) and BCI - an ODBC connection irectly from Basic. hth olin Alfke algary, Canada -Original Message- rom: Wjhonson see a lot of stuff on Universe accessing it's own files as SQL tables, and lso on how you can use OLE to access Universe tables using SQL. But can Universe, access SQL tables which are *not* in Universe? hey are just other SQL tables in other places around the network that sually is update by other non Pick software. Can Universe also access those tables? ___ 2-Users mailing list 2-us...@listserver.u2ug.org ttp://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Universe and SQL
Sure. They have EDA (originally a number of SQL type DB's but IBM reduced to DB2 and now Rocket is adding them back) and BCI - an ODBC connection directly from Basic. hth Colin Alfke Calgary, Canada -Original Message- From: Wjhonson I see a lot of stuff on Universe accessing it's own files as SQL tables, and also on how you can use OLE to access Universe tables using SQL. But can Universe, access SQL tables which are *not* in Universe? They are just other SQL tables in other places around the network that usually is update by other non Pick software. Can Universe also access those tables? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
