Downloaded 28 times
![Perl DBI usage at report building Joe Jiang [email_address]](https://image.slidesharecdn.com/dbi-110703223126-phpapp01/75/perl-usage-at-database-applications-1-2048.jpg)
Uploaded byJoe Jiang
perl usage at database applications
The document discusses various techniques for querying databases and generating reports from the query results using Perl. It provides examples of using DBI and SQL to query databases and format output, techniques for binding variables, preparing queries, and fetching and printing rows. Additional examples show merging and transforming tabular data for different output formats.
perl usage at database applications
- 1. Perl DBI usageat report building Joe Jiang [email_address]
- 2. Perl Language PracticalExtraction and ... Reporting Language
- 3. Perl Programmer LazinessImpatience Hubris (Boldness)
- 4. DBMS Database Management(descide) System
- 5. DBI.pm The gateto Management level programming
- 6. Back to reportingThe man in breau The game they played The suggestion I once made on OLAP
- 7. Chemical Perl (Gas-ware)PHP for website Python for architecture Ruby for rails Perl for what?
- 8. Reports again DMBSis the container SQL is for query Perl is the formater
- 9. Give me ExcelCopy HTML table into Excel Or, name HTML as .xls Use some IDE tool?
- 10. Toolset basic $echo 'select SESSION_ID, HIT_ID from hit where USERNAME=? and ACCESS_TIME between to_date(?,?) and to_date(?,?)' > session.sql ↵
- 11. It worked $bofc < session.sql user-123 201106122345 YYYYMMDDHH24MI 201106122357 YYYYMMDDHH24MI | wc -l ↵ SESSION_ID HIT_ID 12
- 12. And ... $bofc < session.sql user-123 201106122345 YYYYMMDDHH24MI 201106122357 YYYYMMDDHH24MI | sort ↵ SESSION_ID HIT_ID 20110612234046 1772475805 20110612234046 1772475807 20110612234046 1772475809 20110612234046 1772475811 20110612234046 1772475813 ...
- 13. Highlights works withwc -l & sort (Laziness)
- 14. works with parameterbinding (Boldness)
- 15. purify the SQLwith limited punctuaction (Impatience)
- 16. Camel coding PERL5LIB=/proj/Web/EMIS/Modulesperl -MEMIS::Database -le '($s= do{$d=EMIS::Database::gethandle(q(CDB)); $d->do(q(begin; nls_format_setting; end;)); $d}->prepare_cached(join q(),<STDIN>))->execute(@ARGV); $,=qq(\t); print STDERR @{$s->{NAME}}; print @r while @r=$s->fetchrow_array; $d->disconnect' $*
- 17.
- 18.
- 19. $s = do{ $d=...; ...; $d } -> prepare_cached( join q(),<STDIN>)
- 20. ( $s =... ) -> excute ( @ARGV );
- 21. $, = qq(\t );
- 22.
- 23. print @r while@r=$s->fetchrow_array;
- 24. emp #!/usr/bin/perl -luse DBI; $s = do { DBI-> connect ( q(DBI:mysql:database=employees) , q() , q() ); }->prepare_cached( join ( q() ,<>) ); $s->execute ( @ARGV ); $, = qq( \t ) ; print STDERR @{$s->{ NAME }} ; print @r while @r = $s->fetchrow_array ;
- 25. Work Horse ButI want query more than one DB ... Can you send me another column? Make a comma seperated list in one column ...
- 26. Report Driven Reportbinding variables no longer in @ARGV
- 27. SQL no longerin STDIN
- 28.
- 29. Running faster byprepare $s=$d->prepare_cached(do { open(SQL, q(<), shift); join(q(),<SQL>) });
- 30.
- 31.
- 32. @x = map{q()} @r unless @x;
- 33.
- 34. emp-for #!/usr/bin/perl -wlanuse DBI; use IO::Handle; BEGIN { $s = do { DBI-> connect ( q(DBI:mysql:database=employees) , q() , q() ); }->prepare_cached( do { open ( SQL , q(<) , shift ); join ( q() , <SQL> ) }); autoflush STDOUT 1 ; $, = qq( \t ) ; } END { $s->finish ; $d->disconnect ; close SQL ; } $s->execute ( @F ); if ( @r = $s->fetchrow_array ) { print @r ; @x = map { q() } @r unless @x } else { print @x }
- 35. How to listlast deptartment? $ echo 'select emp_no from employees limit 3'|emp 2>/dev/null ↵ 10001 10002 10003 $ echo 'select emp_no from employees limit 3'|emp 2>/dev/null| emp-for emp_dept.sql ↵ d005 d007 d004
- 36. Highlights First timein Perl script
- 37.
- 38.
- 39.
- 40. Demo data replacebofc < x.sql | tee x.bak | do-bofc-for.pl y.sql Backup and update in one line
- 41. Avoid the "nowhere" update fault
- 42. Save a lotof coding with perl -lan
- 43. Or compose thecode for debugging purpose
- 44.
- 45. merge-tab @f= map{ IO::File->new($_) } @ARGV;
- 46.
- 47. join (qq(\t), map{ m{(.*)} && $1 } ...
- 48. print ... untilm{^\t+$}
- 49.
- 50.
- 51.
- 52.
- 53. print join qq(\t),@x while @x=$ea->();
- 54. Demo usage Howto describe a wide table? With sample data attached?
- 55. Columns $ echo'select DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name=?'| emp employees | tee emp-desc ↵ DATA_TYPE CHARACTER_MAXIMUM_LENGTH int date varchar 14 varchar 16 enum 1 date
- 56. Demo Row $echo 'select * from employees limit 1' | emp 2>&1 | row2col | tee emp-data ↵ emp_no 10001 birth_date 1953-09-02 first_name Georgi last_name Facello gender M hire_date 1986-06-26
- 57. And now? Runmerge-tab over the two files $ merge-tab emp.data emp.desc ↵ emp_no 10001 int birth_date 1953-09-02 date first_name Georgi varchar 14 last_name Facello varchar 16 gender M enum 1 hire_date 1986-06-26 date
- 58. 2excel & 2csvperl -MSpreadsheet::WriteExcel
- 59. $s->write_unicode($.-1, $_, encodeq(utf16), decode q(utf8), $F[$_])
- 60.
- 61. 2zip BEGIN {$z=Archive::Zip->new()}
- 62.
- 63.
- 64. Thanks Any Question?http://weibo.com/saved2serve