Database Programming

This is about the fourth generation of this programme that I have been using over the last few years to generate SQL and Perl code based on a MySQL database schema. The idea behind this is much repetitive coding can be eliminated - the ouput of this programme can be tweaked/corrected and then cut and pasted into an application.

Sometimes you need to create several databases and upload same SQL dump to each database. So this little script help you complete this task. You can run it like so: $batch_databases.pl --host="localhost" --user="root" --password="secretpass" --create_db="test_db" --create_user="test_db_user" --create_password="test_user_password" --dumpfile="test_dump2.sql"

Getting the identity column value of your last insert statement is tricky with DBI. the "SELECT SCOPE_IDENTITY()" statement MUST be in the same sql statement as the execute. Below is sample code from a mantis conversion program which inserts a row into the database and gets the identity column value back. Look at $insertSQL for details

syncs database tables. My main use is to sync Informix tables and MS SQL Server tables.
ini file looks like this:

[configs]
; can we perform deletes in this table?
deletes = 0 ; 0 for no / 1 for yes


[columns]
;Remote(MSSQL)    Local(INFORMIX)
IDCode       =id_code
FirstName    =first_name
MiddleInitial =     mid_init
Lastname     =     last_name
Address      =     address
Address2     =     address2
Address3     =     address3
City         =     city
State        =     state
ZipCode      =     zip
Country      =     country
BirthDate  =     birth_date

[keys]
rkey  = IDCode
lkey  = id_code

[tables]
rtable =  Employee_ID
ltable =  empview

[defaults]
[download]

When using Class::DBI with a table such as:

Table: preferences
  +----+------------------+----------------------------+
  | id | name             | value                      |
  +----+------------------+----------------------------+
  |  0 | filename         | /path/to/file.txt          |
  |  1 | item_enabled     | 1                          |
  |  2 | task_name        | something                  |
  +----+------------------+----------------------------+
[download]

you want to use:

  print DB::Prefs->filename,$/;
  DB::Prefs->filename("/new/path/to/file");
[download]

instead of:

  my $row=DB::Prefs->search({ name => "filename" })->first;
  print $row->value,$/;
  $row->value("/new/path/to/file");
[download]

I wrote this to avoid writing the same kind of DBI calls in my program. I haven't used this module that much yet. but i would like to hear what your thought to it.

This node is just to point to my SOPW entry which should belong here actually.

It is important to know that this is just one method of reconnecting a dead connection. Note that we are handling the errors manually for the individual query. It wouldn't take much to create a db_exec subroutine so we just call the db_exec() subroutine and just worry about the reconnect in one place.

Notice that because of DBD::Sybase bug # 616, the error 151 will be printed to STDERR.

./test_sybase
OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (
+151)
Message String: ct_cancel(): user api layer: external error: A connect
+ion to the server must exist on the connection structure before this 
+routine can be called.

ERROR: Connection to DBMS died

syb_db_disconnect(): ct_close() failed

MSG: Reconnected
[download]


The stupid question is the question not asked
	PerlMonks