unload odbc on client putting data on server

Former Member · ‎2012 May 25

Code is vb6.

myRSATT.Open mySQL2Text, myConnATT, adOpenStatic, adLockOptimistic

mySQL2Text = "unload SELECT NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, EMAIL,CUCD, INVOICE, INVDATE, INVTOTAL FROM EMAIL_LIST " & _
"Where ((INVTOTAL  > '" & LowDollars & "') and (INVTOTAL  <  '" & HighDollars & "')) GROUP BY NAME, CUCD, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, EMAIL, INVDATE, INVOICE, INVTOTAL " & _
" TO '" & SaveFileName & "' FORMAT ascii delimited by ';'"

SavedFileName is from popup savefile.showsave, it gets string :

"c:\\documents and settings\\username\\my documents\\email.txt"

it unloads the data on the server machine not the client.

When I use ISQL thru ODBC, I get the same result, the data file is created on the server. I have never encountered this before. I have built a lot in MS SQL (not good or bad, I know it does work) with expected results, data file on machine program is run on.

Any suggestions? If you are in SOCal I will buy dinner.

George

Breck_Carter · ‎2012 May 26

(Revised: See additional example at bottom.)

Here is what you used to run on MS SQL, with line breaks for readability:

"BCP.EXE ElectroPro.dbo.BuildFileAllOthers out " &  
   """C:Documents and Settings" & AppLoginName &  
   "My DocumentsItem Listing NAME.TXT""" &  
   " -c -U" &  
   """" &  
   AppLoginName &  
   """ -P" &  
   """" &  
   strpwd &  
   """" &  
   " -S" &  
   "" &  
   strsvr  
   & ""

BCP.EXE is an MS SQL executable utility program ("Bulk Copy") that you launched from inside your application. It ran on the client workstation, and it connected over the network to the MS SQL database. According to the bcp docs at http://msdn.microsoft.com/en-us/library/ms162802.aspx you told it to dump the table ElectroPro.dbo.BuildFileAllOthers to the file C:Documents and Settings...NAME.TXT.

The -c says to use tab delimited character output with cr/lf line breaks.

The -U -P -S give the user id, password and server name to connect to.

You can do exactly the same thing by executing dbisql.exe, and having it run a combination of SELECT and OUTPUT statements. The SELECT statement is passed over the network connection to the SQL Anywhere, which returns the result set to dbisql. The OUTPUT statement is a special statement executed by dbisql, on the client workstation, rather than by the SQL Anywhere server. OUTPUT can be coded to do pretty much exactly the same thing as UNLOAD, with the difference that it is executed by dbisql on the client computer and thus writes its output to a local file.

Here is a sample table:

CREATE TABLE t1 (
   pkey INTEGER,
   data VARCHAR ( 10 ) );

INSERT t1 VALUES ( 1, 'Hello' );
INSERT t1 VALUES ( 2, 'World' );
COMMIT;

Here is a dbisql.exe command line that runs OUTPUT with tab-delimited output:

"C:\\Program Files\\SQL Anywhere 10\\win32\\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'select_output.txt' DELIMITED BY '\\X09';

Here's what the output file looks like:

1   'Hello'
2   'World'

For more about OUTPUT see the V10 Help: http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-output-statement.html

For more about dbisql see http://dcx.sybase.com/index.html#1001/en/dbdaen10/da-dbisql-interactive-dbutilities.html

Note that dbisql accepts multiple SQL statements separated by semicolons on the command line, but the V10 Help doesn't admit to that 🙂

If the SQL commands get too long and funky to code on the dbisql command line, you can put them inside a *.SQL text file, and have dbisql READ that file and execute it:

"C:\\Program Files\\SQL Anywhere 10\\win32\\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" READ ENCODING Cp1252 "C:\\projects\\$SA_templates\\run\\dbisql\\select_output.sql"

The "ENCODING Cp1252" is optional, but like waving a dead chicken over the keyboard it solves some possible problems with READ.

Note that in this example, the full path for the select_output.sql file is provided on the command line.

Here is what select_output.sql looks like; note that the OUTPUT command also specifies the full path for the select_output.txt file:

SELECT * FROM t1 ORDER BY pkey; 
OUTPUT TO 'C:\\projects\\$SA_templates\\run\\dbisql\\select_output.txt' DELIMITED BY '\\X09';

Again, here is what select_output.txt looks like, same as before:

1   'Hello'
2   'World'

johnsmirnios · ‎2012 May 25

That's exactly how UNLOAD works without CLIENT FILE: the file name is relative to the server:

http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-unload-statement.html "Because it is the database server that executes the statements, file-name specifies a file on the database server computer."

If you can't upgrade to use CLIENT FILE, you need to fetch the data in your ODBC app and then write it out to a local file in your app. If dbisql is a valid option, use

SELECT NAME, ADDRESS1, ... ;
OUTPUT TO foo.txt

Or, equivalently,

SELECT NAME, ADDRESS1, ... ># foo.txt

Or, with statistics:

SELECT NAME, ADDRESS1, ... >& foo.txt

Another option might be to unload to a file on a server then use xp_readfile to fetch the file contents. That's not a great approach though.

Breck_Carter · ‎2012 May 26

It might not help (because the filespec is coming from user input) but UNC filespecs are often used with LOAD and UNLOAD statements to point to the client computer:

   \\\\server-name\\share-name\\directory-filename

where server-name is the computer name of the client computer, share-name is the name of a share on the client computer, and directory-filename is the filespec beneath the share; e.g.,

   \\\\ENVY\\C\\temp\\xyz.txt

Another reason it might not help is if the SQL Anywhere server is running as a service and it doesn't have file access rights to the network and/or client file.

unload odbc on client putting data on server

Know the answer?

Need more details?

Accepted Solutions (0)

Answers (3)

Answers (3)

Re: The use of Class CL_APS_BC_SCOPE_CHANGE_API is...

Re: After installing HANA Express on my Mac Failin...

After installing HANA Express on my Mac Failing to...

Re: How to make a acuired SAC model 24 months roll...

Re: Process Development perspective missing in my ...