General DB Question: ODBC The answer??? - KiXtart.org - official site

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Lounge » General DB Question: ODBC The answer???

Page 1 of 2

1

Topic Options

#161247 - 2006-04-26 11:33 PM General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	Is there a generic driver I can use to connect to any DB??? That way if I'm working on a large project and want it to not be limited to one DB or another, I don't have to provide a bunch of different code for all of the different SQL statements I'd have to write?
Top

#161248 - 2006-04-26 11:35 PM Re: General DB Question: ODBC The answer???
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	Generic? There are so many drivers out there. Are you trying to open an Excel File, Text File, AccessDB, Oracle, MySQL, SQL, or Oracle? If you know what data source you are going after, that would help. That is the beauty of the ODBCxx() UDFs provided here. That way, you don't have to have a ton of code. {Edit} Quote: That way if I'm working on a large project and want it to not be limited to one DB or another, I don't have to provide a bunch of different code for all of the different SQL statements I'd have to write? I re-read your question.. I think you are looking for a DTS Package that when there is a change in a SQL table, it updates data on other systems. You will want to do that and is very powerful. {/Edit} Kent Edited by kdyer (2006-04-26 11:39 PM) _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#161249 - 2006-04-27 12:01 AM Re: General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	yeah... so basically, if i was working on a project and wanted the project to NOT be limited to using MySQL over SQL or P-SQL or Oracle, etc. Is there a generic driver I can use to write to all of them instead of needing to ensure that the clients have a bunch of different drivers??? I may not even be asking a valid question mind you. This is all because I've written a project where all of my clients write to a MySQL database and had to push out the MySQL ODBC driver to all systems to allow them to write to the DB with their information. I was wondering if there's a driver or way to get around this requirement...
Top

#161250 - 2006-04-27 12:05 AM Re: General DB Question: ODBC The answer???
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	No... You want to do this as a Trigger to fire your update from your source db to your destination. You will want to do it at the time of the transaction as your don't want to clean up a bunch of garbage. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#161251 - 2006-04-27 01:09 AM Re: General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	thanx for the clarification kdyer and i apologize to whoever moved my post for posting it in the wrong area.
Top

#161252 - 2006-04-27 04:31 AM Re: General DB Question: ODBC The answer???
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	There's no way to be generic. In addition to utilizing specifc ODBC Drivers for each database you're most likely also using SQL commands/functions that are database-specific. Just look at SQL Server and Oracle, SQL uses ISNULL, Oracle uses NVL. Also, joins behave differently in SQL Server and Oracle when combined with WHERE clauses due to different optimizations being used. _________________________ There are two types of vessels, submarines and targets.
Top

#161253 - 2006-04-27 10:03 AM Re: General DB Question: ODBC The answer???
Richard H. Richard H. Administrator Registered: 2000-01-24 Posts: 4946 Loc: Leatherhead, Surrey, UK	ODBC does abstract the queries you need to make to a large extent, which means that the same code will work with many different database back-ends. There are a number of problems though - you need a specific ODBC driver for each back-end that you want to use. Also ODBC has a number of levels which support different features. Your chosen backend may not support those features, or may not have a driver of sufficient level to support them. Proprietary DB features are unlikely to be available via ODBC (not that you'd want to use them if you want to be back-end independant). As Sealeopard points out, some SQL may be interpreted differently. You can work around these issues or avoid problematic SQL to produce code which is completely back-end independant. If your requirements are simple enough then it's no big task. However what usually happens in real life is that you code for your primary development back-end, then add exceptions when it doesn't work for the alternatives. Don't let that put you off though - coding via ODBC is still a good solution if you may want to change the back-end. At best you will not have to make any change at all, at worst the number of changes will still be less than if you'd used a propritary interface.
Top

#161254 - 2006-04-28 12:23 AM Re: General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	ok... i know i'm asking a lot guys and thanx for being as informative as you have. i have such big aspirations with a KiX/Perl/PHP/%DB% and i just want to make it as x-platform as possible. 1. would you mind elaborating on what a DTS Package is??? 2. what is "triggering" and is there an online doc i can read about the concept and/or how to do it? 3. is there a website or book i can read about writing SQL-platform independent code. which statements syntax are universal and which i should shy away from, etc, etc? thanx in advance guys...
Top

#161255 - 2006-04-28 05:05 AM Re: General DB Question: ODBC The answer???
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	DTS is an ETL tool in Microsoft SQL server 7.6/7.0/2000 and performs data loading/transformations. A trigger execute a specific action upon an event, e.g. an INSERT trigger will run whatever code ws assigned to the INSERT trigger whenever a new row is beign inserted. for more info, read a basic DB book or your favorite DB's manual. Red up on the SQL-92 syntax standard for the lowest common denominator, just be in for a surprise regarding lack of functionality. For performance purposes it's best to code database-specific, otherwise use a tird-party ETL tool between the database sources and targets. _________________________ There are two types of vessels, submarines and targets.
Top

#161256 - 2006-04-28 06:50 PM Re: General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	damn... that really sux that there's no common driver and language standard you can write for any. maybe a task for ISO?? =D thanx for the heads up Jens.
Top

#161257 - 2006-04-29 04:58 AM Re: General DB Question: ODBC The answer???
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	There is. The common driver is ODBC, however even a common driver needs to be specific to the database you're interacting with. And SQL92 is a standard, namely ISO/IEC 9075:1992, see http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt You might want to read Understanding Multidatabase APIs and ODBC _________________________ There are two types of vessels, submarines and targets.
Top

#161258 - 2006-04-30 12:11 AM Re: General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	ok... so the interface is the same. it's just the driver for the different database that converts the ODBC commands back and forth between vendor-specific understanding and the coding of the programmer? i guess i would've just assumed that Microsoft would've included a wide variety of ODBC drivers for the dominant databases out there since ODBC's been around for so long but working with their software, i can also see why they wouldn't. =P
Top

#161259 - 2006-04-30 02:06 PM Re: General DB Question: ODBC The answer???
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Why should microsoft do that? 1) They are a mjor competitor with other RDBMS vendors and 2) The ODBC-drivers are DB-specific, thus best written by the vendor. For a list of thrid-party ODBC drivers, see http://www.sqlsummit.com/ODBCVend.htm. Oh, and BTW, microsoft does offer an Oracle ODBC driver, see http://support.microsoft.com/kb/q244661/ , however, it's much less powerful than Oracle's ODBC Driver. The situation with graphics card drivers is analoguous. You could use the Microsoft drivers, but the vendor-specific drivers are much better. _________________________ There are two types of vessels, submarines and targets.
Top

#161260 - 2006-04-30 03:40 PM Re: General DB Question: ODBC The answer???
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	Quote: The situation with graphics card drivers is analoguous. Bad analogy. If MS did not provide Video drivers, most users would have a really bad OOBE which would not bode well for the product. The number of users needing ODBC out-of-the-box are far fewer than those needing video. _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#161261 - 2006-05-31 09:25 PM Re: General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	so for a little more clarification since i'm a bit slower than the rest, IS there a driver for a DB platform that comes, by default, with say, Windows 2000/XP that I can use WITHOUT having to download and network push the install of a vendor specific driver that will have problems on some computers? Why you ask? Because I've used tools such as SMS, WSUS, GPO Sofware distribution and about the only thing they can agree on is that none of them are a 100% gurantee and if there is a driver that comes in the box that i don't have to worry about failing installs, then i'd prefer to use that (even if it is not as efficient as the vendor one). Edit: I searched google and microsoft and must not be typing in the correct key words because none of the articles i'm finding seem like anything relevant. Edited by thepip3r (2006-05-31 09:27 PM)
Top

#161262 - 2006-06-01 12:45 AM Re: General DB Question: ODBC The answer???
thepip3r thepip3r Hey THIS is FUN Registered: 2005-03-02 Posts: 350	- OR - ...how about this? have the computers write to a log file in a centralized directory and then I could write a script to parse that directory and either grab the info directly from there or write it to a DB. can anyone see any major pros/cons in doing it with this method?
Top

#161263 - 2006-06-01 12:54 AM Re: General DB Question: ODBC The answer???
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11625 Loc: CA	Actually I've done it before via that method of having the machines write their info to a log file (each having their own file) and then parsing it and putting it into a DB or Excel. Doing the live DB connection / update would work, but is a continuous issue to work out errors and machines coming in and out of the network. Edited by NTDOC (2006-06-01 12:55 AM)
Top

#161264 - 2006-06-01 01:15 AM Re: General DB Question: ODBC The answer???
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	I would suggest using the INI format and WriteProfileString() function as the script can update data rather than just append to it. It would also simplify the consolidation of data without need to parse. _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#161265 - 2006-06-01 02:02 AM Re: General DB Question: ODBC The answer???
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11625 Loc: CA	Yes the .ini is a good alternative. I replaced the file once a week though and already had an automated PERL script to parse it for me and I didn't keep history, so I just dropped the MySQL database and rebuilt it on the fly. Doing all from scratch I might choose the .ini as well.
Top

#161266 - 2006-06-01 07:04 AM Re: General DB Question: ODBC The answer???
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	IIRC, MDAC is installed out-of-the-box, this would provide a limited set of database drivers. _________________________ There are two types of vessels, submarines and targets.
Top

Page 1 of 2

1

Previous Topic

Previous Topic View All Topics

View All Topics

Index Next Topic

Next Topic

Moderator: Arend_, Allen, Jochen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Mart

Hop to:

Shout Box

Who's Online

0 registered and 202 anonymous users online.

Newest Members

BeeEm, min_seow, Audio, Hoschi, Comet
17882 Registered Users

Generated in 0.165 seconds in which 0.108 seconds were spent on a total of 13 queries. Zlib compression enabled.

click tracking