To use the SQL-ftpd server, your client must allow you to specify a port to connect with. Since this is not the standard ftp server, it is implemented using a TCP/IP socket, residing on port 9220. If your client insists on using the standard ftp port number, it will be of no use to you (such as Novell's RapidFiler).
There are a couple of other requirements your client must be able to handle as well, such as allowing you to "quote" the remote filename you wish to receive. Since you'll use the client's get command to receive the SQL statement results, the SQL statement will need to be placed within quotes, unless you are using a non- parameterized stored procedure. Stored procedures do not require quotes, unless you give them a parameter. Note that The IBM MVS ftp client differs in the types of quotes necessary. For more information on using the IBM MVS ftp client with quoted strings see Hints: IBM MVS Client differences with quoted strings.
Also, your client will need to allow you to specify a file name to store the returned data in. Typically most clients allow this, so that you can rename the file as you bring it over the network.
The SQL-ftpd server returns data much like BCP extracts data from a table (or view). Columns are tab-delimited, and are the same length as their definition on the SQL server. This should make it easy for the application programmer to parse the returned data, since it will be in a fixed format, with tabs (ascii 9, control-I) delimiting the columns. As long as you know the column length of the sybase table column you've selected from, your application program should have little difficulty in parsing the data. You can optionally choose to remove the tab-delimiters and send the data in fixed width columns. For information on removing the tab delimiters, see Hints: Retrieving data without tab delimiters.
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.
The ftp protocol command structure has been mapped into Transact SQL as best as possible. This means some small changes in what you're used to issuing as ftp commands. Generally most clients will support the basic requirements, however, you'll need to learn how to use the commands differently in order to use SQL-ftpd.
A synopsis of usage is:
ftp <hostname> 9220 (proceed with logging in, using your SQL server userid and password) ftp> get "sql-statement" local-file ftp> quitThe hostname maps to the host where the SQL server is running. This may change later, or additional port numbers may be added in the case of multiple servers running on a single host, however for now, the list is as follows:
The server commands supported are listed below. Note that these commands are what the client sends to the SQL-ftpd server, not commands you would issue to the client. A list of translations and commands which apply to most clients is provided along with the server commands, as well as a short description of their function.
Server Command Client Translation Function ------------------------------------------------------------------ USER user set the username PASS <no translation> set the password QUIT quit quit the connection PORT <no translation> establish a data connection PASV passive set passive mode TYPE type set file transfer type * STRU stru set file transfer structure * MODE mode set file transfer mode * RETR get retrieve data *** CWD cd change (use) database LIST dir list available databases NLST ls list database objects SITE site set site params * SYST system set remote system type * STAT status show local client status STAT rstatus show remote server status ** HELP rhelp show server help ** NOOP <no translation> do nothing PWD pwd show current database Notes:SQL Server messages are passed back to the client using the STAT RFC code (211). Errors returned from the SQL server will appear to the client as illustrated below:* - this function does not exist under the Novell ftp client. ** - leave it to Novell to be weird ... rstatus is remotestatus under the Novell ftp client. rhelp is remotehelp under the Novell ftp client. *** - Novell Strikes again! the Novell ftp client insists on doing a CWD to the remote file specified in a RETR command, which fails. This server responds, and you'll usually see the failure message if you have verbose set. Though a "get" will work, the Novell ftp client will generate bogus error messages with this command, due to it's ignorance of the RFC. The USER and PASS commands are generated automatically by most clients whenever a connection is established. These are used to perform the login. The STAT command (rstatus) can also be used to examine a database object more closely. Using the rstatus command maps to an sp_help <objectname>. Some ftp clients automatically map the NLST command to a LIST command. To get around this you can issue a get "sp_help <objectname> - to see the object more closely.
ftp> get "select * from vew_name" sql.txt
220 PORT command succesful. # ftp informational messages
211- SQL Server message: 208 # notice the SQL Server message portion.
Server: sybasedev # and more detail concerning the error.
Message: Invalid object name 'vew_name'. # oops. Misspelled the view name.
550 SQL Error # more detail.
211 End of SQL Server Messages. # and the closing detail line.
ftp>
Note that SQL server Print statements are not returned to the client.