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

Groups > comp.databases.ms-sqlserver > #1292 > unrolled thread

Odbc and client library/drivers

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1292 — Odbc and client library/drivers

Hi!
I maintain a largish application that was once written for vax/vms and mimer database.
This was then migrated to oracle, and later on, to windows and aix. But still oracle. 
Having that said, there is a large portion of code out at customers, that we do not want to change,
If we migrate them from aix+oracle to windows+sqlserver.

A couple of years ago, I ported the application to use sql-server as db, and we have some 
sites in production now, on sql-server. It runs fairly good overall, but there is one thing
That comes up time after time.
If we 

select * from sometable

And retrive the colunms in another order than the selcect presents them, we get a failure.
I know this is the client lib 'optimising' by dropping data it does think i do not want. 

I wonder if there exists such an odbc client lib other than the one that get installed on the server,
That allows me to retrive values from a resultset in the column order i like?

--
Björn Lundin

[toc] | [next] | [standalone]

#1293

björn lundin (b.f.lundin@gmail.com) writes:
> That comes up time after time.
> If we 
> 
> select * from sometable
> 
> And retrive the colunms in another order than the selcect presents them,
> we get a failure. I know this is the client lib 'optimising' by dropping
> data it does think i do not want. 
> 
> I wonder if there exists such an odbc client lib other than the one that
> get installed on the server, That allows me to retrive values from a
> resultset in the column order i like? 
 
You should use SQL Server Native Client 10 as the ODBC driver if you 
are on SQL 2008, not SQL Server, which is an old driver. Whether this 
resolves the issue you are talking about I don't know. But you should
not use SELECT * in production code.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1294

Den tisdagen den 18:e september 2012 kl. 21:57:14 UTC+2 skrev Erland Sommarskog:
> björn lundin  writes:
> 
> 
> You should use SQL Server Native Client 10 as the ODBC driver if you 
> are on SQL 2008, not SQL Server, which is an old driver. Whether this  
> resolves the issue you are talking about I don't know. 

Sorry I did not mention that. We are on sql server 2008,
but I expect to move to 2012 fairly soon.

SQL Server Native Client 10 is the one we are using, thus 
giving me headache.

>But you should not use SELECT * in production code.

Why?

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.
Ie fill all the struct members, or else someone will look at an empty field.
(Yes the y should check this, but experience shows otherwise)


Besides, the select * are the simple ones, since we have routines that fills that struct 
in correct order.

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.

--
Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1295

On Tue, 18 Sep 2012 13:24:30 -0700 (PDT), björn lundin
<b.f.lundin@gmail.com> wrote:

>Den tisdagen den 18:e september 2012 kl. 21:57:14 UTC+2 skrev Erland Sommarskog:
>
>>But you should not use SELECT * in production code.
>
>Why?

To insulate the client (as much as possible) from future changes to
the table(s).  You always should specify the columns you want in the
order you want them.

>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.

How language variables are bound to the query is not relevant.


>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.
>Ie fill all the struct members, or else someone will look at an empty field.
>(Yes the y should check this, but experience shows otherwise)
>
>Besides, the select * are the simple ones, since we have routines that fills that struct 
>in correct order.
>
>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 makes no sense to me ... it may be an artifact of the library you
are using.  

In cases not involving long fields ( text, blobs, arrays ) the DBMS
should return all the requested fields in a single fetch ... stepping
through with a debugger you will see individual calls to populate your
bound variables, but the data is all together in the retrieve buffer.
Individual fetches may be necessary for long fields, but this is
library dependent.

George

[toc] | [prev] | [next] | [standalone]

#1296

björn lundin (b.f.lundin@gmail.com) writes:
>>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.

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)
2) With a narrow result set, the optimizer might find a better query
   plan, and for instance use a narrow result set.

 
> 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. 


> 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.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1297

On Wed, 19 Sep 2012 21:16:52 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>björn lundin (b.f.lundin@gmail.com) writes:
>>>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.
>
>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)
>2) With a narrow result set, the optimizer might find a better query
>   plan, and for instance use a narrow result set.

     Occasionally, I want all columns regardless.  This happens in one
of two cases:
  1) I am doing something DBAish (in which case, it is not production
code), and
  2) I am building a cursor out of several queries.  The queries out
of the raw tables will have columns specified, but queries out of a
cursor usually are for all columns.

>> 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. 

     In what way is it better?  It takes more memory, bandwidth, and
time.

>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. 

     My two exceptions are safe from this as both are temporary.  I
would hate to have to clean up code with select * in the way OP wants.
It would be much harder to find references to columns.

>> 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.

     get() is miswritten then.  I think your comment is wrong, too.
Your bogus code corrected is likely:
          get(a,varA);
          get(c,varC);
          get(b,varB); <--fail because we got C first.
                                              ^

>> This is mostly a problem in migrating old sites,
>> since the select * method gets it right.

     Read "hides the bug".

>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.

     I agree with this.  I think that there is some sloppy or limited
code between the data and the app.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1299

Den onsdagen den 19:e september 2012 kl. 22:14:36 UTC+2 skrev Gene Wirchenko:
>      In what way is it better?  It takes more memory, bandwidth, and
> time.

Indeed, but as it is more future proof. 
It handles table changes more easy.

> It would be much harder to find references to columns.
Well no.
define a struct like

type Table_A_Type is record
 Col_A : Integer;
 Col_B _ String (1..4);
end record:

 My_Table_A_Record : Table_A_Type;


 Sql.prepare(A_Select, "select * from TABLE_A"):
 Sql.Open_Cursor(A_Select);
 Sql.Fetch(A_Select,End_Of_Set);
 if not End_Of_Set then
  My_REcord := Table_A.Get(A_Select):
 end if:
 Sql.Close(A_Select);

 if My_Table_A_Rec.Col_B then 
....


Not that difficult to understand where Col_B came from?

>      get() is miswritten then.  
Nope see reply to Erland.


>I think your comment is wrong, too.
Indeed, sorry for that
 
> Your bogus code corrected is likely:
>           get(a,varA);
>           get(c,varC);
>           get(b,varB); <--fail because we got C first.
> 
>                                               ^
>      I agree with this.  I think that there is some sloppy or limited
> code between the data and the app.

Nope.
I point you to the same page as I referred Erland to.
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#07002

Under SqlGetData

§5 (hmm I counted to 6 before)

(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.


But I am surprised that you have not run into this.

--
Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1300

Den onsdagen den 19:e september 2012 kl. 22:41:29 UTC+2 skrev björn lundin:
> I point you to the same page as I referred Erland to.
> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#07002
> 
> §5 (hmm I counted to 6 before)

Ha, it is not the same page, but the same content.
Erland got the microsoft reference 
http://msdn.microsoft.com/en-us/library/ms715441.aspx 
where it indeed is § 6 for state 07002


--
 
 Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1301

Den onsdagen den 19:e september 2012 kl. 22:45:42 UTC+2 skrev björn lundin:
> > http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#07002
> http://msdn.microsoft.com/en-us/library/ms715441.aspx 
> where it indeed is § 6 for state 07002

I'm getting tired ...
It should be sql state 07009 and nothing else.

Invalid descriptor index

sorry for my confusion

--
  Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1302

Someone else describing my problem, with C-code...
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/8db96b6b-4d79-4a69-a4d9-2f1d992db478


 --

  Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1298

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

[toc] | [prev] | [next] | [standalone]

#1303

björn lundin (b.f.lundin@gmail.com) writes:
> 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)

I think this is the error. Just as you should not always use SELECT *, 
you should use the same object all over town just because the data 
comes from the same table. Rather you should have a record type per stored
procedure or somesuch.

> Yes, we use all the columns. We have no 'nice to have an extra string col' 
> at all.

My experience from having worked with a system that has lived for a
long time, is that everyonce in a while I would find columns that 
were useful once, maybe with a previous customer, but which smells
funny. Or that the need of a column (or an entire table) disappears. 
There is also the situation that a new requirement could possibly be
bundled with some existing functionality.

All these are situations where I wanted to first find the references in
the database code, and then sometimes look further in the client code
to see if the idea I had could be pursued.

> (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 

I found in the entry for SQLGetData for SQL Server Native Client that it 
says: "The SQL Server Native Client ODBC driver does not support using 
SQLGetData to retrieve data in random column order.".

> Are there any other driver that lets me retrieve values from a resultset 
> in the order I choose?
 
There are a couple of vendors on the market, but I would guess
they mainly focus on non-Windows platforms. They may also be less
complete than Microsoft.

As I said, I am not very well versed in ODBC, but it appears that binding
columns with SQLBindCol is an alternative, although it may not fit well
with Ada. But given this restriction, this is an accident waiting to happen
unless you bundle it properly. I am thinking that you would use something
that uses SQLDescribeCol to find out the column order, and then 
retrieves the column to fill the record. But I only have a dim idea
of how that would work.

I will have to learn ODBC one day, since my interface for Perl programmers
uses OLE DB, and Microsoft has deprecated OLE DB as a means of connection
to SQL Server.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1305

> 
> > However the struct (which is autogenerated from the table definition)
> 
> I think this is the error. Just as you should not always use SELECT *, 
> you should use the same object all over town just because the data 
> comes from the same table. Rather you should have a record type per stored
> procedure or somesuch.

We do not have any stored procedures.

And here is a clearly a difference in opinion.
I do not see 'select *' as an error or as a problem.
Going your route, means total redesign, which I am not interested in.
This is a totally different question than the original.
But I see you answered that one below.

As a side note
All sql is within the Ada code, sent to the db as SQLPrepare
Then the bind parameters are set with SQLBindParameter.
(Which I now see is a generic ifc to SQLBindParameter)
There's also a call to SQLDescribeParam, when binding.

I should mention that the Ada code is daemons/services with no GUI.

 
> My experience from having worked with a system that has lived for a
> long time, is that everyonce in a while I would find columns that 
> were useful once, maybe with a previous customer, but which smells
> funny. 

Those coulumns gets into the customers adaptions. And there they are used.
They do not enter into the standard product.

>Or that the need of a column (or an entire table) disappears.
That happens, but very rarely.
And when it does, the compiler lets us know where the trouble is at,
since it would complain on use of members in the struct that are not present.
 
> There is also the situation that a new requirement could possibly be
> bundled with some existing functionality.
Same thing there, The compiler tells us.
New field? No problem, it is filled with value seince we do 'select *'.
Just use it, after the table_package/struct is regenerated.


> All these are situations where I wanted to first find the references in
> the database code, and then sometimes look further in the client code
> to see if the idea I had could be pursued.
No 'database code' in this system. All sqls are in Ada code.

The client on the other hand, reads from views. It is read only, and send messages
over a socket if it wants to update something. All DML are in services in Ada code.
This way, we can change GUI, which we have done. (Gui gen 3 C#, looking at gen 4 now - html5)



> I found in the entry for SQLGetData for SQL Server Native Client that it 
> says: "The SQL Server Native Client ODBC driver does not support using 
> SQLGetData to retrieve data in random column order.".

Thanks, this is what I was after. But I really hoped to find
a parameter to send in, just as MARS=yes.


> 
> > Are there any other driver that lets me retrieve values from a resultset 
> > in the order I choose?
> 
> There are a couple of vendors on the market, but I would guess
> they mainly focus on non-Windows platforms. They may also be less
> complete than Microsoft.

Ok. The only time we would use that would be when I port the system to ARM, 
and a raspberry PI. There is no Oracle client for ARM. But there is unixodbc
for sql-server. But that is in the future.



> 
> 
> As I said, I am not very well versed in ODBC, but it appears that binding
> columns with SQLBindCol is an alternative, although it may not fit well
> with Ada. But given this restriction, this is an accident waiting to happen
> unless you bundle it properly. I am thinking that you would use something
> that uses SQLDescribeCol to find out the column order, and then 
> retrieves the column to fill the record. But I only have a dim idea
> of how that would work.

Well, the 'select *' combined with the autogenerated struct works fine.
They take care of picking the cols in correct order.
It is the case for old sites, where 'select col_A, col_B, col_C' are
more common. The following get()s are handwritten, and _may_ have the
wrong order. But I guess there is no substitute for testing,testing...

> 
> I will have to learn ODBC one day, since my interface for Perl programmers
> uses OLE DB, and Microsoft has deprecated OLE DB as a means of connection
> to SQL Server.

Raw ODBC is no fun. On the other hand, raw OCI is even worse.
Abstraction layers make stuff MUCH easier.


Having set the first live site with Sql-server, I am pleasantly surprised over
using it. The management tools are much better than Oracles.

Once I got past locking problems, by running in 'READ COMMITED SNAPSHOT' as
I think you once tipped me to, the only 'problem' remaining is the column order in get()s.
The faking of sequences (as in getting a number from db OUTSIDE a transction)
by inserting a record in a dummy table, getting its rowid, and delete it, works well.
It will be replaced by the native solution present in sql-server 2012.


I do have another question, but that will be a new thread

Thanks for the replies

--
Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1310

björn lundin (b.f.lundin@gmail.com) writes:
> And here is a clearly a difference in opinion.
> I do not see 'select *' as an error or as a problem.

But most experienced people in this trade do. Maybe you will not
redesign your system, but one day you may change jobs. In which case
can be good to know that using "SELECT *" is something you should
be restrictive with.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1313

Den torsdagen den 20:e september 2012 kl. 23:31:13 UTC+2 skrev Erland Sommarskog:
> > And here is a clearly a difference in opinion.
> > I do not see 'select *' as an error or as a problem.
> 
> But most experienced people in this trade do. Maybe you will not
> redesign your system, but one day you may change jobs. In which case
> can be good to know that using "SELECT *" is something you should
> be restrictive with.

Indeed good to know. Although this point of view is not the dominating one
at my current work, it is good to know.

I am also aware of that most people outside my job sees Ada as 
a strange language, and when say that it is the best language for me,
they tend to look strange at me ...

But if I change to another job, adaption is of course necessary.
In Rome do as Romans...


--
Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1314

> > > And here is a clearly a difference in opinion.
> > > I do not see 'select *' as an error or as a problem.

As a developer I have seen some code that generated an excel from database input using a XSLT (nothing weird so far).

get database data: select * from tablename where blablabla
for each row
    for i = 0 to data.columns
         excel.column[data.columname] = value --> BOOOOM
    end for
end for

It worked fine for almost four years, until someone added a column to a table without adding it to the XSLT. new column was not found in the transformation file.

[toc] | [prev] | [next] | [standalone]

#1315

Den fredagen den 21:e september 2012 kl. 11:57:16 UTC+2 skrev bradbury9:
> > > > And here is a clearly a difference in opinion.
> > > > I do not see 'select *' as an error or as a problem.
> 
> get database data: select * from tablename where blablabla
> 
> for each row
>     for i = 0 to data.columns
>          excel.column[data.columname] = value --> BOOOOM
>     end for
> end for
> It worked fine for almost four years, until someone added a column to a table >without adding it to the XSLT. new column was not found in the transformation >file.

Hmm yes but that is something different. 
The code cleary states that is _treats_ ALL columns.
That is not my case.

We READ all columns into structs but that does not
mean the code is looping over all fields, _treating_ them.

When an addtion/change of the code is done, the data
is already in the struct, and no need to change the sql.
Or really, no need to worry about some 'lazy' progammer
taking a shortcut assuming the data is already read from the database.

1 crash/bug less :-)


But I respect your point of view.

--
Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1316

Den fredagen den 21:e september 2012 kl. 12:10:54 UTC+2 skrev björn lundin:
> 
> We READ all columns into structs but that does not
> mean the code is looping over all fields, _treating_ them.

On a performance note:
This is a WMS/WCS system. The main goal is to create assignments
to move pallets around in a warehouse.

Taking 10/100/1000 ms more to read all columns is a small part of
the total time the assignment lives.
Fork lifts, stacker cranes, conveyor systems all takes minutes
in comparison.


 
--
 Björn Lundin

[toc] | [prev] | [next] | [standalone]

#1304

How about using views on the server on the same table - then you can 
stick to using "SELECT *" in queries, but you can use different column 
orders on the same table in different views.  In MS SQL Server,
I suppose probably in Oracle as well, you can also INSERT, UPDATE,
and DELETE on a view.

Also, if your elderly ODBC tool is upset by some modern data types,
then you could use views so that those columns either don't appear,
or are CONVERT-ed to something more digestible.

For INSERT etc., in such a case, you may be able to define triggers 
on a view that intercept an action that makes excessively simple 
assumptions about the nature of a table where you have actually
used a view, and, instead, the trigger performs complicated data
operations that your client program doesn't need to control directly.

[toc] | [prev] | [next] | [standalone]

#1306

> How about using views on the server on the same table - then you can 
> stick to using "SELECT *" in queries, but you can use different column 
> orders on the same table in different views.  In MS SQL Server,
> I suppose probably in Oracle as well, you can also INSERT, UPDATE,
> and DELETE on a view.

Yes, however only the client uses views, and the client can not update the DB.

> Also, if your elderly ODBC tool is upset by some modern data types,
> then you could use views so that those columns either don't appear,
> or are CONVERT-ed to something more digestible.

That is no problem. The datatypes we use are present already, but for
XML-type. But this is so different from Oracle's version that
we see no common denominator. So we use varchar to store the xml,
and bring it out from the db, and we parse that string,
either with SAX or with Xpath, within the Ada code.

> For INSERT etc., in such a case, you may be able to define triggers 
> on a view that intercept an action that makes excessively simple 
> assumptions about the nature of a table where you have actually
> used a view, and, instead, the trigger performs complicated data
> operations that your client program doesn't need to control directly.

Hmm, yes, but all business logis is in Ada.
It will be a nightmare to implement the same logic in both T-sql and pl/sql

we have 1 or 2 trace triggers, that copies a record when it changes to a 
trace table. That is bad enough to implement twice.
The reason to have that in trigger code is that we catch
events we might miss otherwise.


Thanks for the suggestions though
--
Björn Lundin

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web