MySQL Problem ;/

Renton

Basically, Im writing a program, but I need it to be able to access a MySQL Database by opening a tcp/ip connection to the database server and get / put information.

Anyone got any ideas of what kind of commands it uses ? or any docs related to it ?

Any help appreciated.

jmcc

Check out the MySQL API section of the docs on www.mysql.com it covers implementing connections in C/Perl.

Regards...jmcc

Tom Dunne

Originally posted by Renton


Anyone got any ideas of what kind of commands it uses ? or any docs related to it ?

Any help appreciated.

As far as I remember (haven't worked on MySQL since last year) you also need the MySQL ODBC drivers. These will allow your program to talk to the DB via ODBC. You can find them at this location

TD.

Static

Why use ODBC when you don't have to? Perl/Php/Java can connect directly to mysql.

Tom Dunne

Sorry, I am coming from a VB/Visual C++ background and that is how I did it.

TD:D

jmcc

Originally posted by Static
Why use ODBC when you don't have to? Perl/Php/Java can connect directly to mysql.

Not every database operation requires the use of a browser/webserver.

Regards...jmcc

The Corinthian

Originally posted by jmcc
Not every database operation requires the use of a browser/webserver.

Java does not necessarily need a browser either to access a database or do anything else. As a matter of fact, set up a crontab and neither do Perl or PHP.

Static

Originally posted by jmcc


Not every database operation requires the use of a browser/webserver.

You're the first person to mention a browser/webserver in this thread. I never mentioned it, neither did renton. Not everything exists solely for the web 8)

jmcc

Originally posted by The Corinthian

Java does not necessarily need a browser either to access a database or do anything else. As a matter of fact, set up a crontab and neither do Perl or PHP.

The browser/webserver combination takes a lot of the programming for the display side out of the equation. It is an obvious short cut.

At the most basic, it is possible to write a script (PHP/Tcl/Perl/Bash etc) to ftp to the server and run a local dump/get/insert script. It could be that the program is being made overly complex since it is possible to run a mysql client locally and connect over TCP/IP to the remote mysql server.

Regards...jmcc

Static

At the most basic, that's the dodgiest way to interact with a DB. So many things could break.

It's not difficult at all in perl/php to connect to a database on a remote server. Sturdier, and tidier too.

The Corinthian

Originally posted by jmcc
The browser/webserver combination takes a lot of the programming for the display side out of the equation. It is an obvious short cut.

A display or GUI may be one of the requirements of what Renton is looking to do, but from what he originally posted, it looks more like an automated process. You said it yourself:

Not every database operation requires the use of a browser/webserver.

Still, looking back to his original post, he might just be asking how to connect to MySQL in general...

jmcc

Originally posted by Static
At the most basic, that's the dodgiest way to interact with a DB. So many things could break.

It's not difficult at all in perl/php to connect to a database on a remote server. Sturdier, and tidier too.

Look at the problem from the deployment angle Static.

Using a simple script to ftp the dataset to the server and then execute the loading script is actually an easier and more robust method since it requires a minimal amount of programming and most error conditions can be catered for.

Breaking it down:

ftp data.sql to server
execute mysql -uuser -ppass<data.sql
copy details from mysql log and e-mail them back
ftp or e-mail the results of the GETs.


The procedure is not that complex - just keep a local copy of the MySQL and apply the gets/inserts to that and if they work, deploy to the remote MySQL db. The scripting can be done in PHP or Perl but this way it can be most effective. It is also a good solution if for security reasons, the remote MySQL is running without networking.

Regards...jmcc

Static

I see where you're coming from. However, this relies on the server in question having an ftp client installed. It requires the mysql server to have an ftp server service installed and running. Details returned from a well coded piece of software are more helpful and accurate than trying to get the relevant mysql log file lines (also requires you to know the location, and have access to the mysql log files on the remote server, something I'd be reluctant to have available over ftp, or emailed about the place).

Do it with a program, and all you need is php, or perl (which is pretty much with any standard unix install) with the perl mysql DBI module. Much simpler requirements. And as I said, a lot less can go wrong, and I'd think that would make deployment much simpler.

You mentioned security... Set your firewall (I don't particularly trust mysql's inbuilt security) to only allow connections to the mysql server from the specified machine, and bob's your uncle. I'd rather that than an FTP server anyday.

Sure, if you're not a coding head, then your way is fine 8)

jmcc

Originally posted by Static
I see where you're coming from. However, this relies on the server in question having an ftp client installed.

If it has TCP/IP then there is a good chance it has a fairly basic ftp client as well.

It requires the mysql server to have an ftp server service installed and running. Details returned

The MySQL server will probably be running on some flavour of Unix so it will have an FTP server even if it is not active. Besides it is always possible to use e-mail to deliver the data.

You mentioned security... Set your firewall (I don't particularly trust mysql's inbuilt security) to only allow connections to the mysql server from the specified machine, and bob's your uncle. I'd rather that than an FTP server anyday.

The problem with specifying the individual machine is that it could be a dialup. The no-networking option is actually a more secure option for running MySQL. It also means that you cannot connect directly to MySQL over TCP/IP.

Sure, if you're not a coding head, then your way is fine 8)

Nah, worse - a SysAdmin. I see this kind of update procedure being used every day. So from my point of view, it hasn't to be the most elegant or the most well crafted piece of code, it just has to work, stay working and not interrupt my games of DoD or TFC.

Regards...jmcc

Static

If it has TCP/IP then there is a good chance it has a fairly basic ftp client as well.

...


The MySQL server will probably be running on some flavour of Unix so it will have an FTP server even if it is not active. Besides it is always possible to use e-mail to deliver the data.

I'm sorry, but I have to disagree there 8) Anybody remotely interested in security will install a server box with the _bare_ minimum in servers installed. Eg, with OpenBSD you get almost _nothing_ with the default install. A FreeBSD install is generally minimalistic too, and afaik, you only get ftp daemons if you ask for them. On the linux side of things, redhat for example is very obnoxious:) You might select minimum, but it'll put whatever it wants there, and that usually includes an FTP server, which should really be avoided if you can.

The problem with specifying the individual machine is that it could be a dialup. The no-networking option is actually a more secure option for running MySQL. It also means that you cannot connect directly to MySQL over TCP/IP.

I'm a bit unsure of your security policies. I have to admit, in my experience, any boxes which I've worked on that fetch stuff from databases on other machines have all had static IPs. So I'll concede to you there 8)

Nah, worse - a SysAdmin. I see this kind of update procedure being used every day. So from my point of view, it hasn't to be the most elegant or the most well crafted piece of code, it just has to work, stay working and not interrupt my games of DoD or TFC.

I'm a developer/DBA/admin, whichever my boss wants at the time 8) I do a lot of database stuff so I'm quite fast at knocking progs together, so I guess, when it comes to this sort of stuff, I'm inclined to go with custom apps. Each to their own 8)


I think we've strayed off topic enough 8)

yossarin

hey Renton, what language are you using ?

i presume that mySql will be running off a diff box than the client

mySql will happily run a server on a given port (default== 3306). it will also provide option to send user/pass or not if not set.

java has a set of drivers for tcp/ip to mySql using jdbc.
i dunno about what security is inbuilt for sending that user/pass in the clear. there might be somthing. alternativly, you could build a wrapper server around mySql with hashed user/pass to that and controll all access to mySql through that...

yoss