Further, stored procedures which don't require parameters do not have to be quoted in your get statement. For example, if we assume I have a stored procedure called get_ftp_data in my database, I can issue the following get statement from my ftp client:
ftp> get get_ftp_data localfile.out
Which is far simpler than:
ftp> get 'select a.col1,a.col2,b.col3,a.col9 from table1 a, table2 b where ...' localfile.out
and more likely to succeed than the latter query.
In short: Use stored procedures because they're more reliable, reduce
overhead on the SQL server (when optimized), and reduce workload on the
programmer retrieving the SQL data through SQL-ftpd.
As always, Use Stored Procedures wherever possible. Using stored procedures
will eliminate the majority of this problem, and help to optimize the query.
Author's note: The SITE command is not available under Novell's FTP client.
Like Q+E, SQL-ftpd sees the messages returned from the Openserver
and assumes a problem has occurred in processing the SQL statement.
authors note: Openserver uses the msgno of 20003, which is also the
SQL server msgno for connection timed out. Because of this, it
raises an error flag and any data returned after the openserver
call is not returned to the client. Just like Q+E.
There is a way around this, however it's dangerous, so use it with
extreme caution. You, the programmer MUST insure that the data was
passed correctly, because an error code or message will not be returned
to you, regardless of the status of your call, when using this command.
This command disables messages from the SQL Server. Like the site tabs
command, it is a toggle which upon first invocation disables the SQL server
messages. To use this command is as follows:
Author's note: The SITE command is not available under Novell's FTP client.
The solution to this problem is to create a stored procedure on the sql server
which your ftp client can then use to retrieve the results. Since stored procedures
can be parameterized, various results can be selected and returned.
Note that the SQL-ftpd server will not return print statements. It will
simply ignore them, and return only results from select statements. Some stored
procedures will look strange when executed, due to the lack of print statements,
however the results will still get to you, if you are careful to use SELECTs
where ever results need to be returned to the ftp client.
Author's note: Try doing a get "sp_help table_name" - on a table with keys
and indexes (and triggers) for an example.
Some rudimentary remote help is available also, from your ftp client, issue the
command rhelp (or remotehelp on Novell FTP).
If you wish to BCP the file you retreived back into the SQL server, you'll
need to use BINARY mode to transfer the data. ASCII mode will generate
the carriage-return characters which require a different translation table
for BCP to correctly identify the line breaks.
Keep in mind that you must have the tab delimiters turned on to create a BCP file.
Fortunately, this is easy to get around. Since the ls command just
maps to an sp_help command within the SQL server, all you need to do
is a get "sp_help" <filename>. For example:
Note: Most ftp clients built on the Berekeley BSD model will accept a dash
(-) as the standard output character. Even Novell's!
First, this client insists on performing a PWD, followed by a CWD <filename>
followed by a CWD <pathname> before ever getting around to do a get.
I'm assuming it's because it assumes (perhaps Novell's doesn't) that the
ftp server can't tell a directory from a plain file, and it attempts to
do so, on the server's behalf. For whatever reason, it almost violates the
RFC, and wreaks havoc with the SQL-ftpd implementation.
Since a CWD "select-statement" will generate a SQL server error, which
causes Novell's FTP client endless grief, SQL server error messages are
disabled during a CWD. Hence, CWD to a non-existant database will return
no error messages. This is currently the only way to use the SQL-ftpd
server with the Novell FTP client. Thanks Novell!
Quoted strings on the IBM MVS ftp client
The IBM MVS ftp client will not allow you to give double quotes (") as a
part of the remote filename, only single quotes are allowed (').
For this reason, you'll need to quote the select statement with
single quotes if you're doing this from the Mainframe. For example:
ftp> get 'select * from table' localfile.out
Where most systems will use double quotes around the select statement,
such as:
ftp> get "select * from table" localfile.out
If you need to give a where clause, within quotes you can do something
akin to the following:
ftp> get 'select * from table where column like "x%"' localfile.out
this only applies to the IBM MVS ftp client. The standard Berkeley
clients will use the reverse of the above situation.Use a special user-id for your production applications
When your application needs to become production, you likely won't
wish to use your sybase userid and password for the data retrieval.
The correct procedure would be to inquire Systems about creating
a userid specifically for the ftp server to acquire data from your
application.Retrieving data without the tab delimiters
If your processing program expects the data in fixed format fields,
without delimiters, there is a way to disable the tab-delimited
function of the ftpd server. To do this, you must use the SITE
command from your ftp client, such as:
ftp> site tabs
This will turn off the tab delimiting, the first time it's used.
Since this is a toggle command, it will negate the state of the
tabs function, the first time turns it off, the next turns it on,
etc.Using OpenServer in your query stored procedures
Using Openserver in your query can be useful if you need
to retreive data from a dataserver that's not local to you. However
it creates a great deal of grief when used with SQL-ftpd.
ftp> site msgs
You should never use this command unless you are using an openserver
call in your query stored procedure.Getting Around your Client's Short length Command Buffer
Some FTP clients insist on having the command buffer be as short as possible.
One example of this is the MVS client when run in batch, which limits the entire
get statement to 72 characters or less. A non-trivial query, plus the quotes,
plus the keyword get, plus the remote file name will easily overrun this
limit.Setting Your Timeout Value
If you intend to give the server a query which might take some time to complete,
you'll need to set the timeout parameter on the server. The default timeout is
currently only 5 minutes, and it will be easy to overrun that. To change the timeout
value for the server (before it assumes you're done and forgot to log out), the
command is idle >number-of-seconds<. For example:
ftp sybasedev.acs.ncsu.edu 9220 # connect to port 9220 on sybasedev (sqlftp)
User: <your-sybase-user-id>
Password: <your-sybase-password>
230 User <your-sybase-user-id> logged in.
ftp> idle # display the current timeout value.
200 Current IDLE time limit is 300 seconds; max 7200
ftp> idle 7200 # set the timeout to the maximum value.
200 Maximum IDLE time set to 7200 seconds
Every time you give a command to the server, the idle time is reset.
So when your query completes, and you start a new query, for example,
the idle time clock will begin counting as soon as you've given the
second query, not from when you first connected.Using a Control-File to Check The Expected Number of Records
If you wish to check on the data returned (and this would likely be a good idea,
because not all ftp clients correctly return their exit status), you can perform something
like a control-file lookup, to insure that the correct number of records were returned.
Your processing program can use this control-file to compare the number of records
expected versus the number of records that were actually transferred. For example:
ftp sybasedev.acs.ncsu.edu 9220 # connect to port 9220 on sybasedev (sqlftp)
User: <your-sybase-user-id>
Password: <your-sybase-password>
230 User <your-sybase-user-id> logged in.
ftp> ascii
ftp> idle 7200
ftp> get "select count(*) from table where ..." table.ctl
(... information messages)
ftp> get "select column,column,column from table where ..." table.data
(... more information message)
ftp> quit
Now your processing program can use the number found in table.ctl to determine
the number of records that should be present in table.data. It's just a double
check, because the ftp client may not be smart enough to let the job know that
there was an ftp transfer error.Using ASCII versus BINARY modes of transfer
In ftp, you can set the client/server to transfer data exactly as it appears
or, where necessary, change it so that carriage-return/line-feed characters are
translated into your system's usual line delimiter. Here, since what we
are returning is SQL server data, we generally take the approach that most
systems can handle a cr/lf as a line terminator (and translate it where necessary).
However, this isn't always true.Getting Around the ls mapped to dir command problem.
Since some clients insist on mapping ls to dir (NLST versus LIST commands
to the server), your client may not be able to return a list of database
objects. All you'll ever see is the list of databases.
ftp sybasedev.acs.ncsu.edu 9220 # connect to port 9220 on sybasedev (sqlftp)
User: <your-sybase-user-id>
Password: <your-sybase-password>
230 User <your-sybase-user-id> logged in.
ftp> ascii
ftp> get "sp_help" -
( ... information messages )
( sp_help output ... )
( ... information messages )
ftp> quit
the example above assumes that the standard output device is referenced
by a dash (-) character. Your client may need something else to direct
the output to stdout, or to the screen. If your system doesn't support
redirection of output to the screen or stdout, you'll have to save it in
a file, but you can then look at the file.Novell's DOS Based FTP client
Now this is a pandora's box, if one ever existed. This client is the most
dorked FTP client known to exist in the computer universe.