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

asked 25 May '12, 18:05

George's gravatar image

George
165101017
accept rate: 0%

edited 26 May '12, 15:34

Breck%20Carter's gravatar image

Breck Carter
26.2k430600863

Comment Text Removed

I found, unload select into client file, but apparently it is not in sybase 10. I don't see a way with out lots of work to get the above statement to work. All I can see is making another table and then unload table. Any thoughts?

(25 May '12, 18:51) George

What MS SQL statement do you use to write to a file on the client computer?

(26 May '12, 15:53) Breck Carter

ahhh, not sure exactly what you are asking. The code above is what I have used for years, I am actually taking a vb project I did 10 years ago in MS SQL and making it work with sybase 10 (trying to make it work)

560 sql = "SELECT * from BuildBrokerForumMavin" 570 Set adoPrimaryRS = New Recordset 580 adoPrimaryRS.Open sql, db, adOpenStatic 590 rowcount = adoPrimaryRS.RecordCount 600 txtRecordCount(2) = rowcount 610 Label(5) = "C:Documents and Settings" & AppLoginName & "My DocumentsBrokerForumMavin-" & rowcount & ".TXT"

is a snipit, yes I use line numbers... for error locations.

(26 May '12, 16:10) George
Replies hidden

Please show the code that actually writes to the file...

560 sql = "SELECT * from BuildBrokerForumMavin"

570 Set adoPrimaryRS = New Recordset

580 adoPrimaryRS.Open sql, db, adOpenStatic

590 rowcount = adoPrimaryRS.RecordCount

600 txtRecordCount(2) = rowcount

610 Label(5) = "C:Documents and Settings" & AppLoginName & "My DocumentsBrokerForumMavin-" & rowcount & ".TXT"

(26 May '12, 16:23) Breck Carter

You know I am having to look at what i did. It has been a long time. I did something prety cool at the time.

I don't know how this will show, I want to see if I can make it work with Sybase, all insults and suggestions, I have to take and appreciate. Let's see what this looks like:

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

Seems I used BCP, I can not remember being this smart. strpwd is the password to the servr and of course strsvr is the name of the server.

Sorry, so sorry, I remember having stuff go to the clients, just could not remember BCP. It actually worked PERFECT. Everything you would expect.

Of course there are many lines preceeding this. I cleaned everything up and put it in a table then this.

(26 May '12, 18:43) George
Comment Text Removed

my vb code grabs all the login information so the user does not have to repeat passwords, etc.., as well as server information. I can remember this did take me a while but when it started working it was sweat.

(26 May '12, 18:46) George

See the new answer.

(26 May '12, 21:22) Breck Carter
More comments hidden
showing 4 of 7 show all flat view

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.

permanent link

answered 25 May '12, 20:00

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

This SELECT NAME, ADDRESS1, ... >& foo.txt still puts it on the server, correct?

(25 May '12, 20:44) George
Replies hidden

No. The ">&" and "OUTPUT" syntax is interpreted by dbisql. They instruct dbisql to output the contents of the current cursor to a file. As the writing is done by dbisql, it will be on the client side.

(25 May '12, 21:44) John Smirnios

For what ever reason, I am unable to get any of the above to work. I am including and excluding the space between the ampersign and the f as well as the pound and the f. Simple things in life become hard when you are in a hurry.

(26 May '12, 15:05) George
Replies hidden

If you are doing it from the command line rather than the interactive window you may have trouble since the shell will try to interpret ">" as stdout redirection. Try the OUTPUT statement version instead. See http://dcx.sybase.com/index.html#1201/en/dbreference/output-statement.html*d5e56120 for details.

(26 May '12, 15:10) John Smirnios

(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'
permanent link

answered 26 May '12, 20:58

Breck%20Carter's gravatar image

Breck Carter
26.2k430600863
accept rate: 20%

edited 27 May '12, 08:22

I just got the DSN stuff working on windows 7, vb6 32. I will focus on this in my relax time Monday. I so do appreciate everyone's help. I went thru a head and neck radiation thing a while back and they told me I would forget things. so eigher 54 years, or the treatment is showing its effects.... smile I can remember being focused on the BCP and being really impressed with myself with it all worked, but I can not remember the angry part of making it work. Everyone have a sincere and sacraid Memorial Day. As I said, I will play with this Monday (yes Momerial Day) for my relaxaztion. Thank you everyone. I will film the completed project so you can see what you have helped me through.

(27 May '12, 13:41) George

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.

permanent link

answered 26 May '12, 15:49

Breck%20Carter's gravatar image

Breck Carter
26.2k430600863
accept rate: 20%

edited 26 May '12, 15:50

Okay, this is making some since to me now. This is done with in VB6 application. I was hired to make some additions to an existing software package and the developers of that package only gave select permissions for $175.00, not create or remote user so I have an uphill battle. But I think I can embed in the vb sql statement the share information, and yes there is a service running on the server for as (sqlany10win32dbsrv10.exe) . I have to take a few hours away from this or my wife will divorce me.... After I try the share, I will let you know how it comes out. thanks everyone, I am used to MS SQL so a lot of the things I know and or expect are different. Without knowing they are different it is a little time comsuming. ALL COMMENTS ARE TRULY APPRECIATED. Life is learning.

(26 May '12, 16:00) George
Replies hidden

Tell us how you would code this in MS SQL, in case someone has an "oh, sure, that's what you're asking for, I get it, here's how to do that in SQL Anywhere" epiphany. (but no rush, you're not the only one who has to go offline for [ahem] various reasons :)

(26 May '12, 16:04) Breck Carter

I'm not hopeful that a server running as a service will have access to a network share.

Also, you may want to double-up the backslashes in the filename since it is an escape character when used in string literals.

(26 May '12, 16:06) John Smirnios

yes there is a service running on the server for as (sqlany10win32\dbsrv10.exe)

Just to note: The fact that you're running the database network server (dbsrv10.exe) and not the personal engine (dbeng10.exe) does not necessarily mean that the server is run as a service - you can run dbsrv10.exe as a normal application, too. - As said, just in case...

(28 May '12, 06:49) Volker Barth

Okay, I am RELAXING on Memorial day .... smile I hope you guys are too. I am playing with this and I can NOT see what I am doing wrong. I get this popup when executing from VB6.

Could not execute statement. Syntax error near 'to' on line 1 SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1 select * from EMAILLIST output to C:Documents and SettingsGeorge CollinsMy DocumentsTireMaster EMail.txt Press ENTER to continue...

I have tried ># , output to, >

I have tried every quoting option I can think of,

"dbisql -c eng=autotiretech;dsn=autotiretech;dbn=autotiretech;uid=dba;pwd=sql" select * from EMAILLIST output to C:Documents and SettingsGeorge CollinsMy DocumentsEMail.txt "

is the string value in VB. I had to have eng, dsn, and dbn to get it to work without a pop up, I can copy this to dos and it will work in the dos prompt 100% of the time.

Does not matter if it is going to c:fileneme.txt or longer path. Can you see what I am not seeing?

(28 May '12, 15:58) George
Replies hidden

It looks like you missed the semicolon after the select statement (between EMAILLIST and output). The quotes look a little funny too: you have a quote at the beginning, one after pwd=sql and a third one on the end. they look mismatched to me.

As mentioned earlier, if you are putting ># or >& on a command line rather than interactively, you will need to hide them from the shell. For example:

dbisql -c "...." "select * from foo >& out"

or

dbisql -c "..." "select * from foo; output to out"

(28 May '12, 16:02) John Smirnios

Here is what I got to work. I do not know how it will show, EVERY SPACE has to be exactly or it will fail.

DBISQLEXECUTE = "dbisql -c ""eng=autotiretech;dsn=autotiretech;dbn=autotiretech;uid=ext;pwd=ext "" ""select * from EMAILLIST >& '" & SaveFileName & "' "" "

I can not figure out how to turn off the following:

-- Executing command: -- select * from EMAILLIST -- Execution time: 0.218 seconds

and at the end of the text file:

-- 217 rows written to "C:UsersGeorge CollinsDocumentsEMail.txt"

No matter how I try the "output to" I can not get it to work from within VB, in a dos prompt I an get a lot to work, just not within VB.

Everyone (Breck ) Thanks so much, I hope this will help another. If seems the order, spaces, quotes, all have to be perfect.

George

(28 May '12, 20:34) George
Replies hidden

Use ># to get rid of the statistic information.

I don't know what would be wrong with using the output statement. Did you add the semicolon as I suggested in response to your previous posting?

(28 May '12, 21:07) John Smirnios
Comment Text Removed

What I have found. from dos prompt the command line has different results then from the VB shell command line, different formatting of; and data. It includes the column titles and stats in one and not the other. SAME OPTIONS.

EXACT SAME LINE copied from the vb code to the dos window and just the extra quotes taken out. the (-q) dash que removes ALL the data output not the the informational data.

Both server and client are SQLANYWHERE 10.

My process was to get the command line working in a dos window and then put that line in the vb code with the proper quotes/structure. If there is interest I can hook up my video machine and grab the process and be corrected or help find/fix a bug. THANK YOU EVERY ONE, If someone would guide me on how to use this voting thing I am happy too. With out the input here I would have never gotten thru this. Now it is all coming back what I did years ago, just the two sets of results are uncomfortable. Thanks George M. Collins

(29 May '12, 01:06) George

I can't comment on the ISQL output redircetion part; as John has suggested, using the ISQL OUTPUT statement should be simpler...

Three remarks on the forum GUI (just as mere hints, there's really no need for any rules or "correct behaviour" here:):

  1. For the "voting thing": Just klick on the "thump up" button on the left side of each question/answer to vote that up (and for comments, on the small button on the right beneath the according comment):
    Image with vote me up button

  2. To "comment on a comment", you can use the "Reply" button on the left of the "I like this comment" (aka upvote) button.

  3. In case your question is answered, you mave accept that - as described in this FAQ...

(29 May '12, 03:28) Volker Barth
More comments hidden
showing 4 of 10 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×39
×22

question asked: 25 May '12, 18:05

question was seen: 2,464 times

last updated: 29 May '12, 03:31