Re: [RDBO] Remote DB connection over SSL to MySQL DB using Rose::DB
On Sep 3, 2007 9:57 PM, Kurt Hansen [EMAIL PROTECTED] wrote: John Siracusa wrote: On 9/3/07 12:31 PM, Kurt Hansen wrote: However, I did not try the connect_options method based on your inlined into the DSN comment and reading up on the Rose::DB and DBI docs. The SSL options DO need to be inlined into the DSN Okay, I'll make the change in SVN when I get a chance. These are in SVN now. -John - SF.Net email is sponsored by: Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Remote DB connection over SSL to MySQL DB using Rose::DB
John Siracusa wrote: On 8/26/07 11:44 PM, Kurt Hansen wrote: Would it be sufficient to add the following ssl parameters to my Rose::DB module? I realize I may need to do some work to configure DBD::mysql to support ssl connections, but if DBD::mysql configured properly, will Rose::DB send the parameters properly? __PACKAGE__-register_db( domain = 'pax', type = 'transaction', driver = 'mysql', database = 'dbname', host = '192.168.2.18', username = 'USERNAME', password = 'PASSWORD', #ssl parameters: mysql_ssl = 1, mysql_ssl_client_key = 'KEYFILE', mysql_ssl_client_cert = 'CERTFILE', mysql_ssl_ca_file = 'CAFILE', #end ssl parameters ); Assuming those are connection options for DBD::mysql, try Rose::DB's connect_options method: http://search.cpan.org/dist/Rose-DB/lib/Rose/DB.pm#connect_options __PACKAGE__-register_db( ... connect_options = { mysql_ssl = 1, mysql_ssl_client_key = 'KEYFILE', mysql_ssl_client_cert = 'CERTFILE', mysql_ssl_ca_file = 'CAFILE', }, ); If those values need to be inlined into the DSN, then Rose::DB will need to be changed to support them. Thanks, John, for your rapid response and advice. I'm able to do the remote connection over SSL now. However, I did not try the connect_options method based on your inlined into the DSN comment and reading up on the Rose::DB and DBI docs. The SSL options DO need to be inlined into the DSN so I took a different tack. Essentially, I created a db handle using DBI, used the dbh method for my Rose::DB object to set the object'd dbh to the one I created, and then passed this Rose::DB object to by RDBO object when creating it. Once I got my head around how Rose::DB and Rose::DB::Object interact, it was very simple to do this. Thanks! For those who want to do this, here's what I had to do (including the mySQL and DBD steps): 1. Get a mySQL server and client binary that supports SSL. I first thought all needed to do was get a client that supports SSL, but it turns out you need to install the server binary along with the client binary to get the SSL support. Those wiser than I might be able to figure out how to compile a mySQL client binary from the sources, but the easiest path for me was to grab the latest stable mySQL binaries. That was 5.0.45 in my case. These have SSL capability built in. To enable the SSL in the server, you need to start with server with the ssl parameter set. See mySQL documentation for details. 2. Compile DBD::mysql with SSL support. See DBD::mysql docs for details. This is pretty straitforward. 3. Register the remote database in your Rose::DB object, e.g: package My::RoseDB; use strict; use base 'Rose::DB'; # Use a private registry for this class __PACKAGE__-use_private_registry; # Set the default domain and type __PACKAGE__-default_domain('domain'); __PACKAGE__-default_type('type'); # Register the data sources __PACKAGE__-register_db( domain = 'mirror', type = 'dbtype', driver = 'mysql', database = 'databasename', host = '192.168.2.3', username = 'username', password = 'password', ); 1; 4. Create your connection to the database using DBI, assign that db handle to your Rose::DB object, use that Rose::DB object when creating your RDBO object: use DBI; use My::RoseDB; use My::RDBO; my $dsn = DBI:mysql:databasename;host=192.168.2.3;mysql_ssl=1;mysql_ssl_client_ key=KEYFILE;mysql_ssl_client_cert=CERTFILE;mysql_ssl_ca_file=CAFILE; my $user = 'username'; my $password = 'password'; my $dbh = DBI-connect($dsn, $user, $password, {'RaiseError' = 1}) or d ie Can't connect to database: $DBI::errstr\n; my $db = My::RoseDB-new(domain='mirror',type='transact'); $db-dbh($dbh); foreach my $trans (@{$transactions}) { my %data = $trans-hashify; my %mirror_data = Convert::Utils-main_to_trans(hash = \%data); print (\n mirror data: , Dumper(%mirror_data)); my $mirror = DB::CWTrans::Transaction-new(db= $db); - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Remote DB connection over SSL to MySQL DB using Rose::DB
(Previous message sent before editting complete. Please ignore.) John Siracusa wrote: On 8/26/07 11:44 PM, Kurt Hansen wrote: Would it be sufficient to add the following ssl parameters to my Rose::DB module? I realize I may need to do some work to configure DBD::mysql to support ssl connections, but if DBD::mysql configured properly, will Rose::DB send the parameters properly? __PACKAGE__-register_db( domain = 'pax', type = 'transaction', driver = 'mysql', database = 'dbname', host = '192.168.2.18', username = 'USERNAME', password = 'PASSWORD', #ssl parameters: mysql_ssl = 1, mysql_ssl_client_key = 'KEYFILE', mysql_ssl_client_cert = 'CERTFILE', mysql_ssl_ca_file = 'CAFILE', #end ssl parameters ); Assuming those are connection options for DBD::mysql, try Rose::DB's connect_options method: http://search.cpan.org/dist/Rose-DB/lib/Rose/DB.pm#connect_options __PACKAGE__-register_db( ... connect_options = { mysql_ssl = 1, mysql_ssl_client_key = 'KEYFILE', mysql_ssl_client_cert = 'CERTFILE', mysql_ssl_ca_file = 'CAFILE', }, ); If those values need to be inlined into the DSN, then Rose::DB will need to be changed to support them. Thanks, John, for your rapid response and advice. I'm able to do the remote connection over SSL now. However, I did not try the connect_options method based on your inlined into the DSN comment and reading up on the Rose::DB and DBI docs. The SSL options DO need to be inlined into the DSN so I took a different tack. Essentially, I created a db handle using DBI, used the dbh method for my Rose::DB object to set the object'd dbh to the one I created, and then passed this Rose::DB object to by RDBO object when creating it. Once I got my head around how Rose::DB and Rose::DB::Object interact, it was very simple to do this. Thanks! For those who want to do this, here's what I had to do (including the mySQL and DBD steps): 1. Get a mySQL server and client binary that supports SSL. I first thought that all I needed to do was get a client that supports SSL, but it turns out you need to install the server binary along with the client binary to get the SSL support. Those wiser than I might be able to figure out how to compile a mySQL client binary from the sources, but the easiest path for me was to grab the latest stable mySQL binaries. That was 5.0.45 in my case. These have SSL capability built in. To enable the SSL in the server, you need to start the server with the ssl parameter set. See mySQL documentation for details. 2. Compile DBD::mysql with SSL support. See DBD::mysql docs for details. This is pretty straitforward. 3. Register the remote database in your Rose::DB object, e.g: package My::RoseDB; use strict; use base 'Rose::DB'; # Use a private registry for this class __PACKAGE__-use_private_registry; # Set the default domain and type __PACKAGE__-default_domain('domain'); __PACKAGE__-default_type('type'); # Register the data sources __PACKAGE__-register_db( domain = 'mirror', type = 'dbtype', driver = 'mysql', database = 'databasename', host = '192.168.2.3', username = 'username', password = 'password', ); 1; 4. Create your connection to the database using DBI, assign that db handle to your Rose::DB object, use that Rose::DB object when creating your RDBO object: use DBI; use My::RoseDB; use My::RDBO; my $dsn = DBI:mysql:databasename;host=192.168.2.3;mysql_ssl=1;mysql_ssl_client_key=KEYFILE;mysql_ssl_client_cert=CERTFILE;mysql_ssl_ca_file=CAFILE; my $user = 'username'; my $password = 'password'; my $dbh = DBI-connect($dsn, $user, $password, {'RaiseError' = 1}) or die Can't connect to database: $DBI::errstr\n; my $db = My::RoseDB-new(domain='mirror',type='dbtype'); $db-dbh($dbh); my $mirror = My::RDBO-new(db= $db); and away you go. :-) Take care, Kurt Hansen [EMAIL PROTECTED] - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Remote DB connection over SSL to MySQL DB using Rose::DB
On 9/3/07 12:31 PM, Kurt Hansen wrote: However, I did not try the connect_options method based on your inlined into the DSN comment and reading up on the Rose::DB and DBI docs. The SSL options DO need to be inlined into the DSN Okay, I'll make the change in SVN when I get a chance. so I took a different tack. Essentially, I created a db handle using DBI, used the dbh method for my Rose::DB object to set the object'd dbh to the one I created, and then passed this Rose::DB object to by RDBO object when creating it. Yeah, that's a reasonable work-around. -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Remote DB connection over SSL to MySQL DB using Rose::DB
John Siracusa wrote: On 9/3/07 12:31 PM, Kurt Hansen wrote: However, I did not try the connect_options method based on your inlined into the DSN comment and reading up on the Rose::DB and DBI docs. The SSL options DO need to be inlined into the DSN Okay, I'll make the change in SVN when I get a chance. Thanks! You can probably prioritize this low since there is an easy workaround. Take care, Kurt - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
[RDBO] Remote DB connection over SSL to MySQL DB using Rose::DB
Hello, I'm trying to connect to a remote MySQL database over an SSL connection. I'm not sure if it is possible using Rose::DB. It looks like DBD::mysql does support it, so I'm guessing Rose::DB does. Would it be sufficient to add the following ssl parameters to my Rose::DB module? I realize I may need to do some work to configure DBD::mysql to support ssl connections, but if DBD::mysql configured properly, will Rose::DB send the parameters properly? __PACKAGE__-register_db( domain = 'pax', type = 'transaction', driver = 'mysql', database = 'dbname', host = '192.168.2.18', username = 'USERNAME', password = 'PASSWORD', #ssl parameters: mysql_ssl = 1, mysql_ssl_client_key = 'KEYFILE', mysql_ssl_client_cert = 'CERTFILE', mysql_ssl_ca_file = 'CAFILE', #end ssl parameters ); Any advice much appreciated! Thanks, Kurt Hansen [EMAIL PROTECTED] - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object