Received: by 10.180.105.2 with SMTP id gi2mr1180204wib.4.1348086373778; Wed, 19 Sep 2012 13:26:13 -0700 (PDT) Received: by 10.52.19.197 with SMTP id h5mr1027535vde.17.1348086373612; Wed, 19 Sep 2012 13:26:13 -0700 (PDT) Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!news.mixmin.net!feed.xsnews.nl!border-1.ams.xsnews.nl!xlned.com!feeder3.xlned.com!feeder1.cambriumusenet.nl!feed.tweaknews.nl!209.85.212.216.MISMATCH!yt1no22773083wib.1!news-out.google.com!ed8ni2292956wib.0!nntp.google.com!i6no10236qas.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Wed, 19 Sep 2012 13:26:13 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=46.195.147.193; posting-account=3_reEwoAAAC163IAIrx427KYmwahFuh9 NNTP-Posting-Host: 46.195.147.193 References: <94298aed-7cbb-41f8-a155-c4632fc9db35@googlegroups.com> <4486daa1-575b-4713-9ca5-f4c8654eb9c8@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <3fe97e67-bae3-423e-9a1d-bdb938e48e7d@googlegroups.com> Subject: Re: Odbc and client library/drivers From: =?ISO-8859-1?Q?bj=F6rn_lundin?= Injection-Date: Wed, 19 Sep 2012 20:26:13 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: csiph.com comp.databases.ms-sqlserver:1298 Den onsdagen den 19:e september 2012 kl. 21:16:51 UTC+2 skrev Erland Sommar= skog: > >>But you should not use SELECT * in production code. > > Why? > Because if the table changes, that changes the result set, and=20 > "interesting" things can happen. Exactly. Person A (wrinting standard code) decides to select some values from a tabl= e. 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 sele= ct 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 c= ontains ColB. so, Person C uses that new filed in the struct, and Bang. Crash,=20 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 yea= rs, these things do happen. And every times it does, it means a customer standing sti= ll. Thus, the pragmatic route is to do a select *.=20 Yes I know it takes longer time. But that time accumulated compared to a st= andstill=20 is nothing. > If you only need three columns, but retrieve 30 you are degrading=20 > 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.=20 >=20 >=20 >=20 > 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.=20 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 dr= op it, Ancd the compiler would protest if we tried to use a non-existing member of= a struct >=20 > > select a,b,c, from d=20 > > are farr worse, since that tends to be > > get(a,varA); > > get(c,varC); > > get(b,varB); <--fail because we got B first. >=20 >=20 > > This is mostly a problem in migrating old sites, > > since the select * method gets it right. >=20 > =20 >=20 > I have not programmed much with ODBC, but I'm somewhat skeptic that=20 > this is a behaviour of the driver. Can you repro this with a C++=20 > 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 =A76 : (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 numbe= r of the column specified in the preceding call; and the driver does not re= turn 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=F6rn Lundin