SQL-ftpd ftp server for SQL Server data retreival

Examples of Usage

Warning: This document may be obsolete.

In the following examples, the prompts and responses are shown as well as user commands. In each example, what you type is highlighted in bold, such as this sentence. The responses and prompts are in plain text. Wherever a phrase is contained within brackets (such as <phrase-here>) enter a value which meets your needs.

Here's an example of a simple connection and it's responses:

To specify a database (Use a database, in SQL parlance) you would use the cd command, just as you were changing a directory in a remote file system when using ftp. Here we also illustrate listing the available databases on a given server that we've connected to. (See the note in Concerning Novell's DOS based FTP client).

author's note: The dir function may not work with all clients.

Wouldn't you know it? You've used a database, and now you've forgotten the name of the table you were going to select from. Here's an example of how you can see all the objects within a database:

author's note: The ls function may not work with all clients.

The ls command is actually mapped to an "sp_help" command. It returns not only the name, but the type of the object, for greater clarification. Note that some ftp clients insist on mapping the ls command so that it performs the same function as a dir command. An example of how to get around this is shown in Hints and Things to Keep In Mind

Now we're ready to get data from our table. Here's an example of how:

There's a lot packed into that one simple example. First, we gave the command ascii. When the sqlftp server returns it's results, it may not be compatible with the client's method of terminating lines. To prevent problems with this, the ascii mode is generally compatible with most systems method of line-delimiters (cr/lf, or newline). If your system can't deal with a cr/lf combination, use the keyword binary instead.

Second, the get statement looks strange. That's because it is. This is the method of translating a remote filename into a Transact SQL statement. The quotes around the SQL statement are required! Also note that the statement needs to be as short as possible. Finally, you'll need to provide a filename on your local host for the data to be stored in. Most systems won't be able to create a file called select * from view_name, and the client will likely tell you so, hence we've told the client to store the data in a local file called xyz.txt.

You'll see a flurry of informational messages come from the server, such as the example above. Most are harmless informational messages, but if you give the server a SQL statement which it can't perform, you'll be able to use those messages to determine what went wrong. For example:

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: Using the OpenServer from within your query stored procedure is possible but requires some non-standard workarounds. For more information on this procedure see Hints: Using OpenServer calls in your query stored procedure.

Putting information into a table is easier than retrieving information from a table. By using the put and append commands you can transfer information from a file into a table. Both commands are implemented because the put command will truncate the table, but the append command will not truncate the table.

To copy information from a file into a table, the file must first be formatted for the table. The file must have the column data delimited by a delimiter character, and the column data should have some resemblence to the column definitions. There is a check on the number of columns versus the number of delimiters (#delims+1 == #columns), and the put operation will fail if there is a difference. Also, there should be no blank lines in the file (including a trailing blank line).

In short, the file should be constructed just as if the file were being built for an extract load into the sybase server. The advantage of this method is that the extract system need not be invoked (which it can't be on sybase1 or the image system servers) in order to update or refresh a table on the server.

One should always keep in mind that this method of placing information into tables is not designed to replace the update system, and should not be used in favor of the update system. This method uses the BCP (Bulk-Copy) interface to the SQL-server and if indexes are present on the table, a slow bulk copy is performed. As such, it should be used for small amounts of information or the user must program a method of dropping and recreating the indexes on a table before using the put function.

To place information into a table (truncating the table first):

If you don't wish to truncate the table, simply replace the verb put above, with the verb append. The message of the table being truncated will not appear and the data will be appended to the data in the table. Note that if indexes are on the table, the data may be inserted between existing records instead of at the end of the table. This could significantly impact performance of the insertion. Further, attempting to use this system, with the procedures outlined below will result in the possible loss of your index stored procedure, as well as the possible failure to load extract data.

A stored procedure can be used to drop/create indexes on a table whenever a fast bcp is desired. An example of such a procedure and it's usage follows. It is assumed that the table is named xyzzy, with columns named call, infreq, outfreq and source. Note that allow_dup_row is specified on the clustered index, for this example.

Then, to use the stored procedure from the SQL-ftpd server:

This may be a bit of extra work on the user's part, however it could be worth the effort if you need to copy a large (1000 rows+ perhaps?) amount of data into a table with multiple indexes.

Forward to the Hints and Tips Page
Return to the SQL-ftpd Contents