1#!/usr/local/bin/perl -w
2
3package XMLDBI;
4use DBI qw/:sql_types/;
5use XML::Parser;
6
7use vars qw(@ISA @EXPORT $table $dbh $sth @col_vals);
8
9@ISA= ("XML::Parser");
10
11sub IsNumber {
12	my ($value) = @_;
13
14	return ($value =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/); # Regexp taken from the perlfaq4
15}
16
17sub new {
18	my($proto) = shift @_;
19	my($class) = ref($proto) || $proto;
20	my($self) = $class->SUPER::new(@_);
21
22	my $driver = shift;
23	my $datasource = shift;
24	my $userid = shift;
25	my $passwd = shift;
26	$table = shift; # Not sure if we want to limit to individual tables yet
27	my $dbname = shift;
28
29	bless($self, $class);
30	$self->setHandlers('Start' => $self->can('Start'),
31						'Init' => $self->can('Init'),
32						'End'  => $self->can('End'),
33						'Char' => $self->can('Char'),
34						'Proc' => $self->can('Proc'),
35						'Final' =>$self->can('Final'),
36						);
37
38	# Setup the DB Connection
39
40	$dbh = DBI->connect("dbi:$driver:$datasource", $userid, $passwd) or die "Can't connect to datasource";
41	if ($dbname) {
42		$dbh->do("use $dbname") || die $dbh->errstr;
43	}
44
45	return($self);
46}
47
48sub execute {
49	my ($self, $sql) = @_;
50	$dbh->do($sql);
51}
52
53sub Init {
54	my $expat = shift;
55
56	# OK, here we setup the insert statement.
57	# We use the prepare method because it offers us _very_ fast inserts.
58
59	$sth = $dbh->prepare("select * from $table where 1=2") || die $dbh->errstr;
60	$sth->execute() || die $dbh->errstr; # Get column names
61	my $names = $sth->{NAME};
62
63	my $sql = "insert into $table ( " . (join ", ", @$names) . " ) values ( ";
64	my $colnum = 1;
65	eval {
66		$sql .= (join ", ",
67					(map {
68							$expat->{ __PACKAGE__ . "columns"}->{uc($_)} = $colnum++;
69							'?';
70						} @{$names})
71				);
72		};
73	if ($@) {
74		die $@;
75	}
76
77	$sql .= " )";
78#	print $sql, "\n\n";
79	$sth = $dbh->prepare($sql) || die;
80
81#	my $count = 1;
82#	foreach my $f (keys(%{$expat->{ __PACKAGE__ . "columns"}})) {
83#		$sth->bind_param( $count++ , undef );
84#	}
85
86	# Possibly add begin transaction code here.
87}
88
89sub Start {
90	my ($expat, $element, %attrs) = @_;
91	# Structure goes: DSN->Table->Column
92	if ($expat->within_element("ROW")) {
93		# OK, got a column, reset the data within that column
94		undef $expat->{ __PACKAGE__ . "currentData"};
95	}
96}
97
98sub End {
99	my ($expat, $element) = @_;
100	if ($element eq "ROW") {
101
102		# Found the end of a row
103		print "Inserting a row...\n";
104                shift @col_vals;
105
106                #kip: handy for debugging.
107                #DBI->trace(5);
108		#print "colvals are @col_vals\n";
109
110		$sth->execute(@col_vals) || die;
111	        @col_vals = ();
112
113                # kip:
114		# the following is no longer needed but I'll leave it just in case I'm wrong.
115		# Re-bind to undef (makes sure things are NULL)
116		#my $count = 1;
117		#foreach my $f (keys(%{$expat->{ __PACKAGE__ . "columns"}})) {
118		#	$sth->bind_param( $count++ , undef );
119		#}
120	}
121	elsif ($expat->within_element("ROW")) {
122		$element = uc($element);
123		return unless $expat->{ __PACKAGE__ . "columns"}->{$element};
124                $col_vals[$expat->{ __PACKAGE__ . "columns"}->{$element}] =
125                  $expat->{ __PACKAGE__. "currentData"};
126	}
127}
128
129sub Char {
130	my ($expat, $string) = @_;
131	# The only Char is the data. (AFAIK) Otherwise this will break (sorry!)
132	my @context = $expat->context;
133	my $column = pop @context;
134	my $curtable = pop @context;
135
136	if (($curtable) && ($curtable eq "ROW")) {
137		$expat->{ __PACKAGE__ . "currentData"} .= $string;
138	}
139}
140
141sub Proc {
142    my $expat = shift;
143    my $target = shift;
144    my $text = shift;
145}
146
147sub Final {
148    my $expat = shift;
149
150	# Possibly put commit code here.
151}
152
1531;
154#########################################################################################
155
156package main;
157use strict;
158use Getopt::Long;
159use vars qw($datasource $userid $password $table $inputfile $help
160			$dbname $verbose $truncate $driver);
161
162sub usage;
163sub quote;
164sub IsNumber;
165
166# Options to variables mapping
167my %optctl = (
168	'sn' => \$datasource,
169	'uid' => \$userid,
170	'pwd' => \$password,
171	'table' => \$table,
172	'input' => \$inputfile,
173	'help' => \$help,
174	'db' => \$dbname,
175	'verbose' => \$verbose,
176	'x' => \$truncate,
177	'driver' => \$driver,
178	);
179
180# Option types
181my @options = (
182			"sn=s",
183			"uid=s",
184			"pwd=s",
185			"table=s",
186			"input=s",
187			"db=s",
188			"driver=s",
189			"help",
190			"verbose",
191			"x"
192			);
193
194GetOptions(\%optctl, @options) || die "Get Options Failed";
195
196usage if $help;
197
198unless ($datasource and $userid and $table and $inputfile) {
199	usage;
200}
201
202$driver = $driver || "ODBC"; # ODBC is the default driver. Change this if you want.
203
204my $xmldb = XMLDBI->new($driver, $datasource, $userid, $password, $table, $dbname);
205
206if ($truncate) {
207	$xmldb->execute("DELETE FROM $table");
208}
209
210open(FILE, $inputfile) or die $!;
211my $file = join "", <FILE>;
212
213$xmldb->parsestring($file);
214
215# End
216
217####################################################################
218### subs ###
219
220sub usage {
221	print <<EOF;
222Usage:
223    xls2sql.pl {Options}
224
225    where options are:
226
227        Option   ParamName     ParamDesc
228        -sn      servername    Data source name
229		[-driver dbi_driver]   Driver that DBI uses. Defaults to ODBC
230        -uid     username      Username
231        -pwd     password      Password
232        -table   tablename     Table to extract
233        -input   inputfile     File to get input from (excel file)
234        [-x]                   Delete from table first
235        [-db     dbname]       Sybase database name
236        [-v or --verbose]      Verbose output
237EOF
238	exit;
239}
240
241