Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1298

Re: Odbc and client library/drivers

Show all headers | View raw

Den onsdagen den 19:e september 2012 kl. 21:16:51 UTC+2 skrev Erland Sommarskog:
> >>But you should not use SELECT * in production code.
> > Why?
> Because if the table changes, that changes the result set, and 
> "interesting" things can happen.

Exactly.
Person A (wrinting standard code) decides to select some values from a table.
Person B takes the prdoct, and make customer adjustments,
B adds column ColB to the table.
Person C does the actual project coding, and sees that standard does a select
from the table. He now uses the new column.
But A only did select ColA from the table.

However the struct (which is autogenerated from the table definition) now contains ColB.
so, Person C uses that new filed in the struct, and Bang. Crash, 

But if Person A did a select * instead, person C would have made it .
Yes I know this is 'sloppy' but i largish systems, maintained for 10-15 years, these
things do happen. And every times it does, it means a customer standing still.
Thus, the pragmatic route is to do a select *. 

Yes I know it takes longer time. But that time accumulated compared to a standstill 
is nothing.


> If you only need three columns, but retrieve 30 you are degrading 
> performance in two way:
> 1) Extra chatter on the wire (particularly evil if you have LOB columns)
 We have no LOBs (yet)

> 2) With a narrow result set, the optimizer might find a better query
>    plan, and for instance use a narrow result set.

hmm yes, but that is no problem so far.


> > The system is in Ada, and being in Ada it is strongly typed. That means
> > we use some kind of struct, with members that corresponds to the fields
> > of a table. We want to fill the whole struct, because i project
> > adaptions, they might want to use a field that we do not use in
> > standard. And then, we find the quality of the system increases a whole
> > lot, if we get all the data out, instead of just getting the data we
> > want rigth now. 
> 
> 
> 
> And then the question arises: is this column really used for anything?
> Maybe you want to drop the column. Maybe you want to redefine its
> semantics. But you find that it is virtually impossible to tell. 
Yes, we use all the columns. We have no 'nice to have an extra string col' at all.
Dropping columns are almost unheard of, but if so, the struct would also drop it,
Ancd the compiler would protest if we tried to use a non-existing member of a struct

> 
> > select a,b,c, from d 
> > are farr worse, since that tends to be
> > get(a,varA);
> > get(c,varC);
> > get(b,varB); <--fail because we got B first.
> 
> 
> > This is mostly a problem in migrating old sites,
> > since the select * method gets it right.
> 
>  
> 
> I have not programmed much with ODBC, but I'm somewhat skeptic that 
> this is a behaviour of the driver. Can you repro this with a C++ 
> program? Since you program in Ada, I assume that you have something
> that sits between your Ada environment and ODBC, and this could be
> the culprit.

Nope:
http://msdn.microsoft.com/en-us/library/ms715441.aspx

section about sql-state 07009
§6 :

(DM) The application has already called SQLGetData for the current row; the number of the column specified in the current call was less than the number of the column specified in the preceding call; and the driver does not return the SQL_GD_ANY_ORDER bitmask for the SQL_GETDATA_EXTENSIONS option in SQLGetInfo

ie, don't do get in another order than the select presents the columns, or yoy get failure.
Ada,c++,c does not matter. Driver behaviour, and documented.


And there is not much in between. we use a very thin bindng to odbc.
(almost too thin in my taste, http://gnade.sourceforge.net/#ODBC)

Yes, a bit dated, but the best I could find that fits my needs fairly well


But focus on my original question was lost.
Are there any other driver that lets me retrieve values from a resultset in the order I choose?



--
Björn Lundin

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-18 10:48 -0700
  Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-18 21:57 +0200
    Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-18 13:24 -0700
      Re: Odbc and client library/drivers George Neuner <gneuner2@comcast.net> - 2012-09-19 11:46 -0400
      Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-19 21:16 +0200
        Re: Odbc and client library/drivers Gene Wirchenko <genew@ocis.net> - 2012-09-19 13:14 -0700
          Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:41 -0700
            Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:45 -0700
              Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:56 -0700
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 14:00 -0700
        Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:26 -0700
          Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-19 23:44 +0200
            Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-20 01:50 -0700
              Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-20 23:31 +0200
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-21 01:18 -0700
                Re: Odbc and client library/drivers bradbury9 <ray.bradbury9@gmail.com> - 2012-09-21 02:57 -0700
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-21 03:10 -0700
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-21 03:14 -0700
  Re: Odbc and client library/drivers rja.carnegie@gmail.com - 2012-09-19 16:57 -0700
    Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-20 01:58 -0700

csiph-web