HDTenEightyP/NetNews huggingface dataset:
From: esh@prague.tss.com (Edward S. Hirgelt)
Subject: Re: possible FAQ Question...
Organization: Teknekron Software Systems, Inc.
Date: Fri, 8 Jan 1993 18:43:13 GMT


On Fri, 8 Jan 1993 15:53:56 GMT,
ecr2@ellis.uchicago.edu (corprew reed) said:

	    I was wondering if it was possible to find out the number of
    rows that a query will return before accessing the data without using
    'count(.*)' with select.  I have a large query that takes a while to
    execute, and I'm hoping to keep my application reasonably interactive
    by not executing it once for the count and once for the data.

As far as I know this isn't possible although very desirable. Think
about it this way, the server can't know what rows will match the where
clause until it actually qualifies them. So it must evaluate your query
to know how many rows. Since it is qualifying the rows, it immediately
starts sending them off to you as soon as it can.

This is what you really want. If there are 500,000 rows that match a
complicated query you want to start seeing rows as soon as possible. The
user sitting behind the application is not going to be pleased waiting
for the 500,000 row to be qualified before he sees the first. This is
the reason why you don't want to do the select count(*) now. It is also
the reason that rows are returned immediately upon being qualified.

I've written applications where I've done the count(*) first to inform
the user what is happening. However, these was generally done for small
tables where the user got to chose some information. For big results or
small results with complicated queries it is a lot messier.

The best you can do is keep track of where you are in the list, enable
row buffering in dblib, implement your own row buffering,  and wait for
cursors (would they really help though). 

Good luck,
Ed
--
Ex vitio alterius	|Ed Hirgelt			|esh@tss.com
sapiens emendat suum.	|Teknekron Software Systems, Inc|
(Publilius Syrus)	|Palo Alto, Ca.			|
Newsgroups: comp.databases.sybase
From: wolfgang@fraser.sfu.ca (Wolfgang Richter)
Subject: Re: Explicitly lock table
Organization: Simon Fraser University, Burnaby, B.C., Canada
Date: Wed, 6 Jan 1993 18:42:52 GMT

"John Salter"  <salter@magnus.acs.ohio-state.edu> writes:


>Is is possible to explicitly lock a table in Sybase to prevent
>other users from inserting into table for a very short time?  If
>so, how is it done?  Thanks.

Here is a repost of information that I gleaned from this newsgroup
some time ago.

So far, the best solution that I have come across was supplied by Scott
Elliott of AT&T Network Systems.  I have implemented his method and it works
fine.  Here is what needs to be done:

  declare @id
  begin transaction
    if not exists (select * from table HOLDLOCK where id=@id)
      begin
        insert into table  values (@id,...)
          .
          .
          .
      end
  commit transaction

If two users execute this at the same time, the following will happen:

User 1 and User 2 acquire shared locks on the table. They will each
hold these locks until the end of the transaction (thanks to the HOLDLOCK
keyword).  User 1 will request an exclusive lock on the table in order 
to insert the new row.  He will be blocked because User 2 still holds a
shared lock!  User 2 will now request an exclusive lock to insert a row
also.  He is also blocked since User 1 still holds his shared lock.  We
now have DEADLOCK!!!!  Sybase will then choose a victim and abort one
of the transactions.  End result:  ONLY 1 USER HAS ADDED THE ROW.  You
should also include some code to check the return value of the transaction
to see if it was rolled back due to deadlock.  If so, repeat the request.


Good Luck...
-- 


   +-------------------------------------------------------------------+
   |   Stuart A. Stakoff           |      sstakoff@csfb1.fir.fbc.com   |
   |   First Boston Corporation    |                                   |
   |   (212)909-4926               |      (212)688-0913 - FAX          |
   +------------------------------------------------------------------+

-- 
-- Wolfgang Richter (e-mail: wolfgang@sfu.ca)
-- Academic Computing Services
-- Simon Fraser University
-- Burnaby, B.C.
-- Canada V5A 1S6
-- Phone: 604-291-4449
Newsgroups: comp.databases.sybase
From: kdomb@ctp.com (Kevin Dombroski)
Subject: Re: group by, max(), and unexpected results
Keywords: unexpected results :-(
Organization: Cambridge Technology Partners
Distribution: usa
Date: Mon, 4 Jan 1993 21:20:29 GMT

This is what I was looking for...


select distinct field1, max(field2) 'max field2', field3
from #junk
group by field1
having field2 = max(field2)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
order by field1

-- 
================================================================================
Kevin M. Dombroski                  | "One good thing about music,
Cambridge Technology Partners       |  When it hits, you feel no pain...." 
================================================================================
From: dbenua@panix.com (David Benua)
Newsgroups: comp.databases.sybase
Subject: Re: Microsoft ODBC driver for Sybase 4.9
Date: 5 Jan 93 02:39:51 GMT
Organization: PANIX Public Access Unix, NYC

In <1993Jan4.160116.939@iplmail.orl.mmc.com> casavant@elmer.orl.mmc.com (John Casavant) writes:

>Where can we find a driver for Sybase 4.9 that works with Microsoft ODBC?
>Is there an ftp site that has drivers for ODBC?

As far as I can tell, the only ODBC driver available today for Sybase is 
that shipped with MS Access.  One caveat: it only officailly supports
MS SQL Server not Sybase and is know not to work with FTP Softwares's 
PC TCP.
If you want more details, Email me 

Dave 
dbenua@panix.com
From: htunca@ncsa.uiuc.edu (Skinny Puppy)
Newsgroups: comp.databases.sybase
Subject: Moving Sybase from one server to another
Date: 4 Jan 93 22:04:38 GMT
Organization: ncsa
X-Xxdate: Mon, 4 Jan 93 16:04:12 GMT
X-Useragent: Nuntius v1.1.1d11
X-Xxmessage-Id: <A76E107CC201149F@thunderbird.ncsa.uiuc.edu>

I'm currently in the process of trying to move a Sybase SQL
server from one machine to another.  I've been looking into
different ways of doing this.  I've been playing with dump
database and load database, unfortunately the manuals say
that in order to use this the database and log segments must
be exactly alike (size, placement) etc in order for this to work.
I'd like to be able to place the databases on new drives and with
larger sizes on the new machine without having to use alter
database.  The other option is bcp.  However, bcp will not
recreate the table structure, etc.  Has anyone done this before
who might be able to give me some insights as to which is
the easiest way to transfer the server from one machine to 
another?

Thanks in advance.

____________________         __           ____________________
\_____________      \       /_ \         /      _____________/ 
 \_____________      \_______/  \_______/      _____________/
  \_________________________      _________________________/ 
                           /______\
   Han Tunca
   National Center for Supercomputing Applications
   htunca@ncsa.uiuc.edu
Newsgroups: comp.databases.sybase
From: sreit@ctp.com (Stefan Reitshamer)
Subject: SQL sort problems (group by?)
Organization: Cambridge Technology Partners
Date: Mon, 4 Jan 1993 19:38:01 GMT

I need to sort data in a bizarre way and have NO idea how to do it (other
than looping through rows and assigning index numbers and then selecting
by ascending index number). Here's the data involved:

Claim #  Subscriber #  Date
-------  ------------  --------
22402    111111111     10/10/92
55600    333333333     04/04/92
45200    222222222     09/15/92
22404    111111111     08/08/92
22400    111111111     09/09/92
44900    111111111     08/30/92
45202    222222222     07/01/92

The last 2 digits of the claim # are adjustment numbers (the 00 claim is the
original, others are adjustments of the original).
I need to sort this data by subscriber number, and by descending date 
within each sub #. BUT, if a claim has been adjusted, I need the adjusted 
claims to appear after the original (regardless of the date).

Basically, rows with the first 3 digits of the claim # in common should be
grouped together. These groups should then be sorted by sub # and descending
date.

So, the above data would be sorted as follows:

Claim #  Subscriber #  Date
-------  ------------  --------
44900    111111111     05/05/92                     ]Group 1
22400    111111111     02/02/92                          ]Group 2
22402    111111111     03/03/92 <- Adjustment of 22400   ]Group 2
22404    111111111     08/08/92 <- Adjustment of 22400   ]Group 2
45200    222222222     01/01/92                   ]Group 3
45202    222222222     07/01/92                   ]Group 3
55600    333333333     04/04/92              ]Group 4

I was thinking something like:
    select *
	group by round(Claim#/100,0)
	order by Subscriber,Date desc
but I guess this is incorrect usage of the "group by" clause.

Does anyone know how to "select" this? (Hope this explanation makes some sort
of sense)

Thanks very much!
  Stefan
-- 
``````````````````````````````````````````````````````````````````````````````
Stefan Reitshamer      sreit@ctp.com       Cambridge Technology Partners, Inc.
     Opinions expressed herein belong to no one at all (just to be safe).
``````````````````````````````````````````````````````````````````````````````
From: jordan@warped.pnl.gov (Randy Jordan)
Newsgroups: comp.databases.sybase
Subject: Re: Using Views for data presentation
Keywords: name, spelling
Date: 5 Jan 93 18:07:29 GMT
Article-I.D.: oracle.1993Jan5.180729.14793
Reply-To: jordan@warped.pnl.gov (Randy Jordan)
Organization: Battelle, Pacific Northwest Laboratory


In article <29829@ursa.bear.com>, solar@fsrg.fsrg.bear.com (William del Solar) writes:
|>Path: pnl-oracle!ogicse!uwm.edu!zaphod.mps.ohio-state.edu!usc!cs.utexas.edu!qt.cs.utexas.edu!yale.edu!ira.uka.de!sol.ctr.columbia.edu!ursa!fsrg!solar
|>From: solar@fsrg.fsrg.bear.com (William del Solar)
|>Newsgroups: comp.databases.sybase
|>Subject: Re: Using Views for data presentation
|>Keywords: name, spelling
|>Message-ID: <29829@ursa.bear.com>
|>Date: Mon, 4 Jan 93 14:53:31 PST
|>Article-I.D.: ursa.29829
|>References: <92356.101548IDAZS@ASUACAD.BITNET> <wolfgang.726176551@sfu.ca>
|>Sender: news@bear.com
|>Organization: Bear Stearns & Company, New York, NY
|>Lines: 26
|>
|>In article <wolfgang.726176551@sfu.ca> wolfgang@fraser.sfu.ca (Wolfgang Richter) writes:
|>><IDAZS@ASUACAD.BITNET> writes:
|>
|>>>First and last names are stored in separate fields in upper
|>>>case. We have been partially successful in combining them to display
|>>>in upper-lower case, using "substring" and "rtrim" functions. Names
|>>>that have spaces or dashes, like "James Mc donald" or
|>>>"Carol Terry-peplow" have been a problem....
|>
|>By storing names in upper case only, you are losing information that
|>must later be either derived or even manufactured:
|>
|>How will you correctly spell names such as da Vinci, van Beethoven,
|>ter Horst, von Krupp, della Robbia, and del Solar?
|>
|>If the spelling has been Americanized, how will you correctly spell
|>names such as DellAquila, Deluca, DeSaro, or Delvalle?  From the last
|>four names, it is not possible to derive a rule to spell the name correctly.
|>
|>The only solution that will satisfy both the folks who have Americanized
|>the spelling of their names and those who haven't is to store names in
|>both upper an lower case.
|>
|>Wm del Solar
|>____________________________________________________________________________
|>My opinions are not my employer's opinions:  I do not speak for my employer.
|>
--

	What your are talking about is "Case-insensitive" sort order.

	It's what we use... and even though the BOOK states that you take 
	a performance hit for using it we haven't felt it, and the User-
	friendly nature is more than usfull.

RJ

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	To Thyne Own Self be true...
	                             myne opinions are.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Newsgroups: comp.databases.sybase
From: rob@fosters.med.utah.edu (Rob Sargent)
Subject: Re: SQL sort problems (group by?)
Organization: University of Utah
Date: 5 Jan 93 11:36:57


My first inclination would be to peal off the last two digits of the
Claim # into a separate column in the result/report/temp_table, then
use it in your ordering.

If claim number is an int you would have to do something like
	sub_no = substring(convert(varchar(15), claim_no),
        	          datalenght(convert(varchar(15), claim_no)) -2,
                	  2)

or better interms of numberic, rather than alphabetic, sort ordering

	sub_no =  claim_no % 100

but this would drop leading zero (if you care).

rjs

--
Rob Sargent                      s-mail: Dept. of Human Genetics    
e-mail: rob@fosters.med.utah.edu         U. of Utah Medical School     
				         Eccles Genetics Bldg
				         Salt Lake City, Utah  84112

Newsgroups: comp.databases.sybase
From: justice@dao.nrc.ca (Gerald Justice,P=363-0055,R=152A)
Subject: Re: Can SYBASE Server listen on two ethernet interfaces?
Keywords: SYBASE server ethernet interfaces subnets
Reply-To: justice@dao.nrc.ca
Organization: DAO/CADC .
Date: Fri, 8 Jan 93 02:07:04 GMT

Not so long ago I asked about this and one person did reply with a suggestion
to change the interfaces file master entry to refer to nullhost rather than
the server's own real name.  However, Sybase Tech Support finally found their 
own documentation on this after some weeks and the real answer is that you
can have as many master entries in the interfaces file for the protocol/port
combinations that you have.  In our case we simply added a new line for the
alternate hostname assigned to the second ethernet port, e.g.
The interfaces entry was:
SYBASE
	query tcp sun-ether primename 2025
	master tcp sun-ether primename 2025
	console tcp sun-ether primename 2026
	debug tcp sun-ether primename 2027

And it now is
SYBASE
	query tcp sun-ether primename 2025
	query tcp sun-ether secondname 2025
	master tcp sun-ether primename 2025
	master tcp sun-ether secondname 2025
	console tcp sun-ether primename 2026
	debug tcp sun-ether primename 2027

The key on the server end is the master line not the query line.

Tech Support has a short note (with no apparent title or name) about this and you
might have trouble convincing them of this.

---

Phone:     (604) 363-0055  PDT      Fax:    (604) 363-0045    Telex:  049-7295 
Internet:  justice@dao.nrc.ca       Mail:   Dominion Astrophysical Observatory 
                                            National Research Council (Canada) 
BITNET:    justice@nrcdao                   5071 W. Saanich Road 
                                            Victoria, B.C.   CANADA  V8X 4M6
From: yangying@ces.cwru.edu (Ying Yang)
Newsgroups: comp.databases.sybase
Subject: Re: KILLed Sybase process will not die?
Date: 8 Jan 1993 01:58:09 GMT
Organization: Agency Rent-A-Car, Cleveland, OH
NNTP-Posting-Host: easy.ces.cwru.edu

In article <1ideloINN1bd@usenet.INS.CWRU.Edu> yangying@ces.cwru.edu (Ying Yang) writes:
>
>4. The process is sleeping. We cannot kill a sleeping process. When an
>end-user process gets disconnected, we cannot kill the Sybase process
>and release the locks.  To deal with this, we have installed a EBF to
>kill disconnected processes when our clients turn off their PCs.  At
>last year's User Conference, Sybase said the 5.0 version would provide
>unconditional KILL, hope this is true.

I have received several inquiries about the EBF. It seems a lot of having
the disconnect problem,  so I am sending follow up to this group.

We have an 4.8 server on VAX/VMS platform. We have installed EBF989, 
which can kill a sleeping process and clean up the locks. In order 
for this EBF to work, we have to set  the "keep-alive" flag
in our Wallongon TCP/IP. We an end-user turns off his (her) PC, 
TCP/IP's keep alive inquiries get no responds form that PC. After
two minutes, TCP/IP sends a signal to the server notifying the 
disconnect. Upon receiving the signal, the EBF kills the process 
and cleans up the locks.

A short history. Last April, our production was in in a very bad 
shape due to locks left by the disonnects. We managed to get the 
attention of the Sybase Management (we are so called "critical account"). 
A Sybase level III engineer worked with us for several weeks, and 
produced this EBF, which has been very critical to us.

Ying Yang

MIS 
Agency Rent-A-Car
From: davidvc@sybase.com (David Van Couvering)
Newsgroups: comp.databases.sybase
Subject: Re: What does dbcancel() do?
Date: 7 Jan 93 05:00:48 GMT
Organization: Sybase, Inc.

[ stuff deleted... ]
>dbcancel() says that it sends a signal to the server to stop
>processing the current command batch and discard any results. Since
>I am just doing inserts, I don't really care about the results.
>However, I am not sure what delimits the "current" command batch,
>and I might not want to cancel THAT.
>
>Does anyone KNOW how this works? Particularly if the command batch
>DOES contain several statements (all INSERTs in this case).

dbcancel() cancels all the inserts that are part of the current
dbsqlexec().  dbcanquery would only cancel one of them (unless
the inserts were all part of a stored procedure).  In more exact
terms, dbcanquery() only cancels one set of results (equivalent
to one call to dbresults and then an empty dbnextrow loop).  
dbresults() cancesl all sets of results (equivalent to an empty
dbresults loop and an empty dbnextrow() loop).

David
--
davidvc@sybase.com | {pacbell,lll-tis,pyramid,sun}!sybase!davidvc
David Van Couvering Sybase 1650 65th Street Emeryville, CA  94608
- Opinions expressed here are mine and not necessarily those of Sybase -
Newsgroups: comp.databases.sybase
From: gaupkg@fnma.COM (Prab Goripathi)
Subject: Re: KILLed Sybase process will not die?
Reply-To: gaupkg@fnma.COM
Organization: Fannie Mae
Date: Wed, 6 Jan 1993 14:52:36 GMT
From: kaskubar@mayo.edu (Bruce Kaskubar)
Newsgroups: comp.databases.sybase
Subject: Ethernet and Token Ring
Date: 6 Jan 93 16:15:52 GMT
Distribution: usa
Organization: Mayo Foundation, Rochester MN. Campus

We're running Sybase on an RS/6000 and would like to get to it from  
both Ethernet and Token ring depending on the client.  What's  
needed in the Interface file for this?

thx,

Bruce Kaskubar
kaskubar@mayo.edu
Newsgroups: comp.databases.sybase
From: lewism@jmb2.jmb.com (Mike Lewis)
Subject: Why does dateadd() allow milliseconds?
Organization: JMB Realty Corporation
X-Newsreader: TIN [version 1.1 PL6]
Date: Tue, 12 Jan 1993 21:11:35 GMT

I just discovered that the datetime data type stores the date and time parts
separately as 4-byte integers.  The 4-byte integer representing time is the
number of 3/100 of a second since midnight.  Therefore, the following equality
is often (though by no means reliably) true:

iamadatetimevariable = dateadd(ms, 1, iamadatetimevariable)


There are 24*60*60*1000= 86,400,000 milliseconds in 24 hours.  A signed, 4-byte
integer should be able to store a number as large as 2,147,483,648.  This raises
two questions in my mind:

1)	What else is Sybase doing with the 4-byte integer representing time?

2)	Why, if the above equality can be true, does Sybase give me the ability
	to add a single ms to a datetime?  This ability implies that the above
	equality should never be true and is misleading in the extreme.

Don't get me wrong, this isn't the bug that ate Tokyo or anything.  I was
just wondering why things work this way.


Mike Lewis (lewism@jmb2.jmb.com)
From: dptom@powerplay.asd.sgi.com (Tom Arnold)
Newsgroups: comp.databases.sybase
Subject: Re: Query Question
Date: 12 Jan 93 23:52:33 GMT
Organization: Silicon Graphics, Inc.

In article <1993Jan8.204249.6995@mercury.cair.du.edu>,
elinden@diana.cair.du.edu (Elwood Blues) writes:
|> What I want is for one record per store and 5 columns as follows
|> 
|> store   reg   OT      sunday     sundayOT 
|> -------------------------------------------
|> store1  800   40      30         10
|> etc.
|> 
|> Any ideas would be greatly appreciated.  There are many circumstances in
|> which
|> I would like to have a single field (hours) displayed in multiple
|> columns.

I'm assuming that the table contains something like payroll transactions
and you want to view display/print the results in an array for each store.
The available syntax in SQL isn't going to help you very much. Depending on
the report/writing tools that you have available to you, you might want to
try one of them. Shy of this, a quick 'C' program will do the trick. 

Now... A less attractive way to do this would be to create a "temp-type"
table that looks like "store_data(store, reg, ot, sunday, sundayot)" and
load the data into it using some stored procedure.  This table could be
persistent for a period of time and users could query against it to get the
appropriate data in a format that they like. The stored procedure could
update the table over some interval and all might be okay. Unfortunately,
this may be more work than is necessary for a simple report and it may be
better to take the first alternative.  
-- 
***************************************************************************
**
*       Tom Arnold                    | "When you step in the ocean       
*
*       dptom@sgi.com                 |   you become a part of the        
*
*       Silicon Graphics, Inc.        |    food chain."                   
*
*       (we do it in three            |                                   
*
*        dimensions)                  |                                   
*
***************************************************************************
**
From: lmcgee@bondo.corp.sgi.com (Lee McGee)
Newsgroups: comp.databases.sybase
Subject: Re: Questions sbout time based queries
Date: 11 Jan 93 16:43:47 GMT
Reply-To: lmcgee@sgi.com
Distribution: na
Organization: Silicon Graphics

In article <1ikt6eINNh61@meaddata.meaddata.com>, jimj@meaddata.com (Jim Johnstone) writes:
|> We have a need to query out all rows entered by a given user since a specified
|> time.  My first thought was to use timestamp columns in all tables. So (I am
|> very new to Sybase, so please be nice) my questions are:

I have a great "horror story" for you! :^( - I once had an an application that used a timestamp column for this purpose, until one day THE TIMESTAMPS ROLLED OVER (reset back to a lower value) and EVERYTHING IN THE DATABASE GOT SELECTED!

You can't count on the sybase timestamp value as an always increasing number!

(our experience was under SQL Server 4.0.1; your mileage may vary.)

|> 1. Are timestamps the best way to go? 
|> (nice because they are automatic, bad because they can't be CONVERTed)
|> 
|> 2. Should I just use datetime in each table?
|> ... insert TABLE values(..., getdate(), ...) 
|> then 
|> ... select * from TABLE where date > @threshold and user = @user> 

|> 3. Is there some Sybase table/feature that will allow me to meet the 
|> requirement directly?
|> 
|> 4. How would you handle this requirement?

Use a datetime column and a trigger to keep it updated.

-- 
________________________
Lee McGee lmcgee@sgi.com    		Spring is what winter
Silicon Graphics, Inc	   		  Always goes inter.  
PHONE (415) 390-2403	    
FAX   (415) 961-9584 	  			- Ogden Nash  
PAGE  (415) 807-2246
From: John L Wolfe <John_L_Wolfe@ccm.hf.intel.com>
Newsgroups: comp.databases.sybase
Subject: DPT Card / High capacity drives
Date: 4 Jan 1993 12:13:05 -0600
Organization: UTexas Mail-to-News Gateway
NNTP-Posting-Host: cs.utexas.edu

Has anyone used the DPT card in and machine running OS/2 1.3 and 
SYBASE 4.2.

Any recommendations / suggestions or better ideas for a very high 
performance EISA SCSI card.

Any recommendations on a high reliability / high capacity half height 
disk drive.
 __________________________________
/                                  \ 
|John Wolfe (Programmer / Analyst) | 
|John_L_Wolfe@CCM.HF.INTEL.COM     | 
|Phone (916) 356-3007              | 
|Fax (916) 356-3994                | 
|Intel Corp.                       | 
|1900 Prairie City Road            | 
|Folsom, CA. 95630-9501            | 
\__________________________________/
Newsgroups: comp.databases.sybase