It's irrelevant where the damon (service) is running. Either method will
work remotely, or on the local machine. It only depends on what you connect
to. Yes, you can do this, and in fact I do it quite frequently. Usually, I
make my scripts to read an ini file or some similar configuration mechanism
and have a switch there to either execute the script on the machine as it is
running, or to just write the script out to a file for me to have, and
possibly execute else where. One that I have actually writes the .SQL script
to create a subscription to a remote publication, and sends it across to
another machine along with a .bat file (also generated by the perl script,
and the zipped database backup file that the perl script has produced. Once
it is there, I use sockets to execute the batch on the remote machine to
execute the batch which uses ISQL to execute the SQL script which unzips the
backup file, restores to the database, and creates the subscription on that
machine. What I'm saying is that it is only limited by your creativity.
However, I can sit at my desk, and run some variant of the second example on
any of the servers in our data center, or across the wan on one of our
remote failover sites. All you need is some way of connecting to the remote
service (ODBC is my favorite, but is by no means the only way. You can use
DBI, and DBD::ODBC to execute this on the remote machine.
I am sitting at home, so this will be hand typed into the e-mail body, so
there may be some syntax error, but this should give you an idea of how to
do what I am talking about (BTW, I tend to go a bit overboard in my
explanations. I don't mean to offend, I just want to be sure I give a full
example):
use strict;
use DBI;
my $dsn = "northwind";
# define the location of the sql server.
my $database = "DBI:ODBC:$dsn";
my $db_user = "sa"; #bad idea. create a different user.
my $db_password = "";
# connect to the sql server.
my $dbh = DBI->connect($database,$db_user,$db_password, {AutoCommit=>1,
LongReadLen => 4000});
$dbh->do("USE Northwind");
my $tablename = "Customers"; #example because you are normally using a
variable.
open (outfile, ">c:/develop/test.sql"); #change to the file you want to use.
print outfile qq{CREATE TABLE $tablename ( \n};
my $select = qq{select name, type_name(xtype) + case
when type_name(xtype) in ('varchar', 'char','nvarchar', 'nchar') then
'('
+ cast(length as varchar) + ')'
else ''
end,
case isnullable
when 1 then 'null'
else 'not null'
end
from syscolumns where id = object_id('customers')
order by colorder};
$selecth = $dbh->prepare($select) || die qq{can't
prepare\n$select\n$dbh::errstr\n};
$selecth->execute() || die qq{can't execute\n$select\n$dbh::errstr\n};
my ($row, $colname, $coltype, $nullable);
$selecth->bind_columns(undef, \($colname, $coltype, $nullable));
my $def = '';
while ($row = $selecth->fetchrow_arrayref) {
$def = $def. qq{,\n\t\t$colname\t\t$coltype\t\t$nullable};
}
$def =~ s/,//;
$def = $def.')';
print outfile $def;
Now open your test.sql and look at your generated SQL script. That's just a
simple definition. I can't teach you all there is to know about SQL 7's
system catalogues, but you have everything you need in sysobjects, and
syscolumns, and sysindexes (although I usually use sp_helpindex to bring in
information to create indexes on the fly). From there, it's really up to you
how creative you get, and how well you do it.
Have fun.
Steve H.
-----Original Message-----
From: eric wang [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 05, 2001 5:47 PM
To: Steve Howard
Subject: RE: creating columns on the fly
This is a good idea, but the problem is I want to generate a *.sql file so I
can take that to anywhere and be able to upload/update multiple databases
using only one sql file.
Is that possible?
Also, your second method will only work if I am executing this script on my
local computer. But in my situation, I need to process the data on a Unix
machine, then this unix machine will generate the *.sql file. Then I will
take this file to another machine(s) where the database is stored (NT
machine). Is this possible?
Eric
-----Original Message-----
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 05, 2001 3:08 PM
To: eric wang; dave hoover; [EMAIL PROTECTED]
Subject: RE: creating columns on the fly
Two ways you can do it that I can give an example of. One is to query the
system catalogue directly embedding this:
SELECT name FROM syscolumns WHERE id = object_id('<sometable>')
(of course, substituting the table name in the appropriate place).
or, if you are using DBI, you can pull a full list of columns in a statement
handle doing something like this (snipped out of a replication program I
have been working on).:
my $dbh = DBI->connect($database,$db_user,$db_password, {AutoCommit=>0,
LongReadLen => 4000});
my $dbh1 = DBI->connect($database,$db_user,$db_password, {AutoCommit=>0,
LongReadLen => 4000});
$dbh->do("USE Northwind");
$dbh1->do("USE Northwind_copy");
my $select = $dbh->prepare("SELECT * FROM Customers") || die qq{Can't
execute\n$select\n $dbh::errstr\n};
local($\, $,) = ("\n", "\t");
$columnlist = '['.join('], [', @{$select->{NAME}}).']'; #get the column
list from the dbi function.
print @{$select->{NAME}}[0..$#{$select->{NAME}}]; #for demo only. Shows the
array of column names
As you can see from the print statement, the column names are now
referencable in the @{$select->{NAME}}
array. (Notice that $select is the statement handle. If you used $sth your
array would be @{$sth->{NAME}} There are other properties that can be pulled
in similar manners from the statement handles. Once you get the hang of
using this, you can easily get the other properties listed in perldoc DBI.
Hope this helps,
Steve H.
-----Original Message-----
From: eric wang [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 05, 2001 2:58 PM
To: dave hoover; Eric Wang; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: creating columns on the fly
Thanks for the tip,
But what if I am using Microsoft SQL server 7?
is there a way to do this?
eric
-----Original Message-----
From: dave hoover [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 05, 2001 12:43 PM
To: Eric Wang; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: creating columns on the fly
Eric Wang wrote:
[snip]
> First, I decided that I will need to generate the
> SQL statements inside
> the perl script. (i.e. print SQL "insert...
> blah..").
If you use DBI, this is no problem, in fact, it's
expected. You will probably call
$sth->execute(SQL_HERE). You can plug in a previously
generated scalar variable at SQL_HERE.
> But, I need to know
> if this column I insert into has already existed or
> not? if not, create
> it, otherwise just insert it. So, the simple
> question is can I do this?
[snip]
Yes. You can query the database first to see if it's
there, then based on the results, construct your SQL
statement with UPDATE/INSERT accordingly.
=====
Dave Hoover
"Twice blessed is help unlooked for." --Tolkien
http://www.redsquirreldesign.com/dave
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
