Solved

Which language is best for database probing?

Posted on 2004-10-14
20
462 Views
Last Modified: 2007-12-19
Hi All,

I need to create an application which will probe the database for various properties. e.g. it will list all the related records from tables which are linked thoruh specified Foreign Key for one particular table.

What is te best language which can be used for such operations?

I have following options :

perl
Java
.Net (C-sharp/VB.net)

what are the pros/cons for using each one of these? which is best performance wise/maintenance wise/implementation wise?

Regards
Ajay

0
Comment
Question by:avi_india
  • 8
  • 5
  • 3
  • +3
20 Comments
 
LVL 6

Expert Comment

by:etmendz
ID: 12306178
You can create stored procedures to query the records.

All of these languages support connecting to the database and executing stored procedures.

The choice of language is a matter of taste, really. But if you are on a Windows platform and you are using SQL Server, you can expect .Net to perform better than the other languages. Basically, because .Net is made by the same company that created Windows: Microsoft.

Have fun.
0
 
LVL 9

Expert Comment

by:konektor
ID: 12306264
java has a lot of classes which can wery closely manipulate the database ...
0
 

Author Comment

by:avi_india
ID: 12306283
hmm...

Database can be any thing - oracle/db2/sql sever/my sql/ms access

given this, now which would be beter to give a genric solution?
0
 
LVL 6

Expert Comment

by:etmendz
ID: 12306338
Your issue now then will rely more on what drivers are available and which one's you want to use. For all of these languages, you'll encounter drivers that are free and drivers that must be purchased.

If you want portability, go Java. The driver issues apply (availability, free or not).

.Net Mono (.Net on Linux, Windows, Mac OS, etc.) may be portable as well. The driver issues would still be the same.

If you know your application is to run specifically on Windows, go .Net. Again, the driver issues would still be the same.

Have fun.
0
 

Author Comment

by:avi_india
ID: 12306356
can you throw some light on .Net Vs perl (pdsql) for such kind of application? I just talked to my manager. I pushed on .Net ande on perl (pdsql). Any baselines for convincing him on using .Net?

This application need to run only on Wndows.
0
 
LVL 6

Expert Comment

by:etmendz
ID: 12306514
.Net is built to perform best on Windows. Looking forward, .Net will grow as Windows grow. Support, documentation and communities are available here and there. Database support uses ADO.Net.

Perl is a great open-source language best for I/O operations and for processing/manipulating string data. Support, documentation and communities are available here and there. Database support uses Perl's DBI module and Win32 ODBC.

In both, database driver issues apply (availability, free or not)...

Since you're building an application for Windows, I recommend you go .Net.

Have fun.
0
 

Author Comment

by:avi_india
ID: 12306533
drivers will not be an issue. Most probably we'll buy drivers from DataDirect. I have worked these before and I really like there performance. There support persons are also realy geat and they try to help/resolve your problem as much as is possible.

I am still keeping this question open for next two days. May be i'll get somee more poniters.. :)

Still. thanks for all the answers.. :)
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 12308550
If I understand you correctly you want to extract the stucture from a Database. This is a very specific task and the solution greatly depends on the database you want to querry/analyze. If you need to extract the data from a SQL Server then you should consider taking a look at the SQLDMO COM Component. With this class you can querry a SQL Server for almost everything in it. Since this is quite MS-Centric i would also suggest .NET for this specific task. But it really depends on what language you are more used to. Both languages are able to achieve what you want. So pick the one you are faster with :) (That would also be the line of argumentation i would try on my boss)

I dont know the inner structure of the other DBs so i cant really help you out on those.
0
 

Author Comment

by:avi_india
ID: 12316177
have to learn both.... :).. and i think it would be easier to learn .Net then perl.. also in long run it will be better to know .Net.. what say??? :)

I have worked only in C/C++ till date.. So in that perspective also i think it is easier to learn .Net..


0
 
LVL 6

Expert Comment

by:etmendz
ID: 12316314
If you are working on an application that will run on Windows, you are in a better position to use tools that are built to work especially with Windows and its built-in features. .Net has C#.Net and C++.Net so you can leverage your existing skills and talents.

Have fun.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:hatky
ID: 12318196
Why do you need to "probe the database for various properties"? Do you want a database manager? becouse you can get a free one like http://sourceforge.net/projects/phpmyadmin/ it is an open source database manager so you have a complete solution for your problem already, it is built in php that is also free from http://www.php.net will work on any platform and any database.

On the perl, Java, .Net (C-sharp/VB.net) I would have to vote highly against perl, it is easy to write, but imposible to read, very bad for maintaining, generaly both of the other are not build for perfemance but for RAD (Rapid Application Development), I am sorry to bring another tech for the equasion but as you considered perl, why not php? It's a free scripting language that is readable, maintainable, fast to learn, fast to code, and has many free helpers like in http://pear.php.net and other places on the net, and as I sayed before has a full database manager that quries all you want so you can read there code for some help

Good lack,
Hatky.
0
 

Author Comment

by:avi_india
ID: 12318252
hmm... I am wondering why nobody asked this "why" question before.

You see.. we have soft data integrity i.e. we do not maintain database level integrity rather we maintain it through code. Why - This question can not be answered (but you can imagine why )...

so if the somebody has messed up with data manually - database will not stop that person from doing so. We are trying to create a utility which will probe the database for its integrity.

0
 
LVL 11

Expert Comment

by:rdrunner
ID: 12318376
Well

If there are no "Hard relationships" then probing the DB wont help you out either. It will be VERY tricky to resolve these by hand. Even if you probe the DB and get all Tabels and fields, how will you relate them to each other?Also you said the DB can be anything. This will make it even harder to figure out what is correct. I hope you have a strict naming policy on your DB so you have at least a chance to link the items by their name.

Also I tend to skip the WHY question unless you are building a brand new system... I almost never got a resonable answer to the Why question.

Do you really need to probe a whole bunch of different DB systems? If its "YOUR" application i think you would be faster if you create SP that check the integrety of the DB... An automated aproach would have a lot of requirements which most likely wont be fullfilled here...

*Takes a look at our DB-Diagram..... One looooong line of tables next to each other with a tiny group at the end which was added since i work here*
0
 

Author Comment

by:avi_india
ID: 12318433
gotta go now.. will give details on monday
0
 
LVL 1

Assisted Solution

by:hatky
hatky earned 50 total points
ID: 12318561
Dear avi india,

I can only think of one reason you don't have constraints, old mysql db without innodb table? or do you really think it is worth the "Perfemance Gain" I don't like to be blunt but I would call any answer you can present as a mistake, point blank, no reason to not haveing database contraints!

No reason to not haveing database contraints!
No reason to not haveing database contraints!
No reason to not haveing database contraints!

Now write it on the wall 1,000 times :), sorry got carried away.

Again recomending my beloved PHP my admin at  http://sourceforge.net/projects/phpmyadmin/ they got what you need, you can define soft links and check their integerty, I just don't remeber if you can check more then one at a time, but as it is open source you can either ask them for it or add the code yourself and sumit it in :).

Hope this is a sollution for you,
Hatky.
0
 
LVL 3

Assisted Solution

by:djwillms
djwillms earned 50 total points
ID: 12329436
Actually, you SHOULD be using the programming language that is internally supported by the database server.

In Microsoft SQL-Server, you should be writing stored procedures in transact SQL to do this.
In oracle you should be writing PL/SQL stored procedures to do this.

Then after writing all the logic / querying in the stored procedures you should be using .Net if youre developing a web site or Desktop application that runs on windows computers.

The reason is that if you write the code in the stored procedures, then you will not have to submit multiple queries across the network to the database server. The more queries you pass over the network the slower your application is. So if all your application does is call (in sql server syntax) "exec spGetResults @Parm='abc'"  then its just the database server querying itself without causing any network traffic until the results are ready.

The MOST persuasive argument for USING .NET is the development environment.
The visual studio debugger in particular is the best ive ever seen. It is a debugger capable of debugging multi-threaded applications. The features that most people like the most are the fact that it shows you the documentation for the language as you type it (intellisense).  Also, the debugger shows you the values and datatypes for any variables in scope at the time.

Visual studio also supports source control using Microsoft source safe. This isnt supported by PERL or Java.
If you want to use Perl or Java then you should buy a 3rd party development environment, otherwise you will just be using notepad.

Also, SUN corporation just got sued and lost a patent infringement case on Java. The other company was awarded $100million dollars damage by the judge.

As for perl, Its roots are in Unix and I havent seen a developement environment for perl that is as good as microsoft visual studio for .net.

0
 

Author Comment

by:avi_india
ID: 12336169
One of the benifits having application not defining any database level constraints is that end user can define his/her own database schema. and tell application which fields are to be used as constraints. Ofcourse it puts a big "?" on what end user would define. but with the help of support, this is taken care of evry efficiently.

Only problem is that as we do not have any database level checks, soif at some time some code fails, or some transaction fails, which we are not able to catch, will result is databse inconsistancy. But this has happens only 1 in 100000 times. (This is from the data collected from various customers over the years).

So now we are developing application to detect such discrepencies in database.

0
 
LVL 1

Expert Comment

by:hatky
ID: 12336390
Well if you are working like this for a long time I guess you won't change that fast but I want to state some things:
1. If my system had this statistics, we would be fighting inconsisensy every 1-2hr, and we are not there around the clock and our users are.
2. Did you think about securty, malitios users trying to bug the system?

I think you can still use every soulotion presnted here,
Good luck,
Hatky.
0
 
LVL 6

Accepted Solution

by:
etmendz earned 100 total points
ID: 12336633
It seems to me that you have a product that is like MS Access in some ways because it allows the users to design the database themselves. The advantage of using MS Access, however, is that you can define the relationships anytime and the rules/constraints are applied immediately even to the existing data in the table. This feature ensures data integrity even at design time.

Here are my thoughts:

1) You are trying to solve the problem at the surface. Your current plan may solve only immediate needs. However, solving it at its core may reduce the need to even double check later.

2) I can imagine that you are trying to maintain a generic product. I believe that it is best for you to have product variations where each variant targets a specific database product. This way, you can take advantage of the built-in RDBMS features of the target database if there are any. You can still let the user define their own database schema (like in MS Access) but use the features of the target database that enforces data integrity.

3) The change required by #2 above is not as big as your product because it may only affect the part of your product that creates or alters the database schema for the user. Everything else would be a matter of handling exceptions thrown by the database.

So, your current plan may use any language with database support. If time-to-deliver matters, .Net should offer the best productivity because you can leverage your C/C++ background using C++.Net or C#.Net.

Looking forward, I recommend that you solve the problem at its core...

Have fun.
0
 

Author Comment

by:avi_india
ID: 12336663
Yes - We also support MS Access (now mainly because of legacy reasons)


1. - YES
2. - YES



I also agree with your recommendation. But currentnly we want to know which database areas are most susceptible for such issues.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now