How do I get UNLOAD and LOAD between separate servers to work using a named pipe?

[Breck_Carter]

This blog post talks about using a named pipe to pass data from the UNLOAD TABLE statement on one Version 12 database to the LOAD TABLE on another database running on the same server: UNLOAD and LOAD Via Named Pipes

Is it possible to get that to work for the same databases running on separate servers on the same computer?

On different computers on the network?

The following code is exactly the same as the blog post, except for the separate servers; instead of working, it gets stuck forever.

"%SQLANY12%\\Bin32\\dbinit.exe"^
  ddd1.db

"%SQLANY12%\\Bin32\\dbinit.exe"^
  ddd2.db

"%SQLANY12%\\Bin32\\dbspawn.exe"^
  -f "%SQLANY12%\\Bin32\\dbsrv12.exe"^
  -o dbsrv12_ddd1_log.txt^
  ddd1.db

"%SQLANY12%\\Bin32\\dbspawn.exe"^
  -f "%SQLANY12%\\Bin32\\dbsrv12.exe"^
  -o dbsrv12_ddd2_log.txt^
  ddd2.db

"%SQLANY12%\\Bin32\\dbisql.com"^
  -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=ddd1"

"%SQLANY12%\\Bin32\\dbisql.com"^
  -c "ENG=ddd2;DBN=ddd2;UID=dba;PWD=sql;CON=ddd2"

---------------------------------------------------------------------
-- On ddd1

BEGIN
   DROP TABLE t1
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t1 ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t1 VALUES ( 1, 2 );
INSERT t1 VALUES ( 2, 2 );
COMMIT;

SELECT * FROM t1;

---------------------------------------------------------------------
-- On ddd2

BEGIN
   DROP TABLE t2
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t2 ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

BEGIN
   DROP SERVER ddd1_server;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER ddd1_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 12;ENG=ddd1;DBN=ddd1';

BEGIN
   DROP EVENT unload;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE EVENT unload
HANDLER BEGIN

DECLARE @sql LONG VARCHAR;

SET @sql = 'UNLOAD TABLE t1 TO ''\\\\\\\\.\\\\pipe\\\\data''';
   SET @sql = REPLACE ( @sql, '''', '''''' );
   SET @sql = REPLACE ( @sql, '\\\\', '\\\\\\\\' );
   SET @sql = REPLACE ( @sql, '\\\\', '\\\\\\\\' );
   SET @sql = STRING ( 'FORWARD TO ddd1_server ''', @sql, '''' );

MESSAGE STRING ( CURRENT TIMESTAMP, ' ***** Step 1: ', @sql ) TO CONSOLE;
   EXECUTE IMMEDIATE @sql;
   MESSAGE STRING ( CURRENT TIMESTAMP, ' ***** Step 2: unload done' ) TO CONSOLE;

END;

MESSAGE STRING ( CURRENT TIMESTAMP, ' ***** TEST STARTING *****' ) TO CONSOLE;
TRIGGER EVENT unload;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ***** Step 3: load starting' ) TO CONSOLE;
LOAD TABLE t2 FROM '\\\\\\\\.\\\\pipe\\\\data';
MESSAGE STRING ( CURRENT TIMESTAMP, ' ***** Step 4: load done' ) TO CONSOLE;

SELECT * FROM t2;

=====

SQL Anywhere Network Server Version 12.0.1.3298
...
Now accepting requests
2011-07-31 05:41:44.239 ***** TEST STARTING *****
2011-07-31 05:41:44.253 ***** Step 1: FORWARD TO ddd1_server 'UNLOAD TABLE t1 TO ''\\\\\\\\\\\\\\\\.\\\\\\\\pipe\\\\\\\\data'''
2011-07-31 05:41:44.260 ***** Step 3: load starting
Cache size adjusted to 4020K

>>> STUCK at the LOAD TABLE t2 FROM '\\\\\\\\.\\\\pipe\\\\data';

[johnsmirnios · Accepted solution]

Actually, you're not going to be able to get it working. It turns out that the LOAD TABLE side is implemented but UNLOAD is not. UNLOAD to named pipes is only implemented in.... wait for it... v9! It was added for the upgrade to v10 which required a new database format.

I'll give a few details on how it was supposed to work but first a few disclaimers before I go any further. The use of OS named pipes or even internal named pipes is undocumented and subject to change. Exposing the details of the current implementation on this forum does not constitute "documenting" the feature and in no way holds us responsible for maintaining backward compatibility. I also take no credit (or blame, really) for the rather awkward way the support was added so far 🙂

For LOAD TABLE, regular OS pipe paths are recognized and converted to use an internal same-process implementation. If, however, the pipe pathname contains "extrbld" on Windows or "-extrbld-" on UNIX, we will look for a real OS named pipe. For example, you could use a pipe name of \\.pipeextrbldfoo.

Since UNLOAD doesn't handle OS named pipes, you would need to make your own standalone named pipe "server" to create the named pipe and produce the data. LOAD TABLE would be able to read from that.

[Breck_Carter · Accepted solution]

Read Chapter 1 of my new book, "Undocumented SQL Anywhere For Dummies Unleashed!" here: http://sqlanywhere.blogspot.com/2011/08/unload-to-named-pipe-beats-unload.html

[VolkerBarth]

I do not know whether this will work - so treat this as guesswork:

Windows does support to access Named Pipes from different machines. Whereas the "pipe server" can only create a named pipe locally, the pipe client can be on a different machine and then will need to qualify the server's name when accessing the pipe, such as '\\\\RemoteServer\\pipe\\data'.

So you might just have to replace the dot in '\\\\.\\pipe' with the server machine name.

Note, this will only work (if at all) if SQL Anywhere does really use a OS named pipe. From the cited question, I remember John explaining that there was something particular to consider:

Due to historical reasons, SA recognizes any path that starts with \\\\.\\pipe as a local named pipe and doesn't actually open an OS named pipe. Instead, it bypasses all that and shuffles the data via internal buffers.

That may (or may not) prevent the possibility to create real (and remotely accesible) OS named pipes.

[Breck_Carter]

It doesn't work with both servers on the same computer, and that's what I actually want 🙂

[VolkerBarth]

I would try to use SysInternals's Process Explorer to check whether both processes have opened the same named pipe. I haven't checked it but I guess procexp will show handles of pipes just as it shows handles of files or other IPC objects that are in use by a certain process.

However, given John's elaborate explanation, the '\\\\.\\pipe' path won't lead to the usage of a real OS named pipe but to internal local buffers - which seem to work only for databases on the same database server. - And John has not yet shown how to use an OS named pipe in his answers:

Another caveat: if you do use a real OS named pipe (which I haven't showed you how to do), the server is at the mercy of the producer (which in this case is the UNLOAD but could an arbitrary program) to close the pipe. Otherwise, LOAD will wait indefinitely for more data.

So I would think unless he does tell us more, your efforts won't work that way:(

[VolkerBarth]

Can you hear us, John? - Your expertise seems needed (as usual:))

[johnsmirnios]

Yes, I hear you. I've been away for a few days. I'll need to research a few of the details and get back to you.