Quick SQL question

Quick SQL question

• vito1281

  it has everything to do with the question you are asking. . .
  
  the tells ADO.NET that you will be specifying a parameter for the value
  
  at execution the resolves to whatever value you load in the parameter. . .
  
  you dont need to wrap the in quotes either.
  
  play around with parameters.
  
  run the code in the help file.
  
  it is very explicit.
  
  There was a great msdn magazine article about a year ago on 'SQL injection' which outlines how parameters are evaluated
  
  I am sorry but I dont know how I could expound any further than the help file does already. It is very informative when it comes to parameters.
  
  

• Vinoth

  first. . .
  
  I recommend you not use ODBC unless that is your only option.
  
  is a parameter.
  parameters don't need to be wrapped in quotes even if you are referencing text.
  
  in the help file index under 'parameters [ADO.NET]
  
  'Using Parameters with a DataAdapter'
  &
  'Specifying Parameters and Return Values'
  
  
  
  

• Craig Guyer - MSFT

  exactly!
  
  I found the code in the Dataset.designer.cs file. same place I found the SQL for the update command.
  
  Good luck!
  
  
  

• blemmon

  I have the button but the file is not there. It must be a language-specific thing, i.e., it only exists with C# and not in C++. Thanks anyway!
  Kamen
  
  

• Tom Lake - MSFT

   No three-word post was ever helpful...
   
  If you mean I should read the documentation of the OdbcComand class, its Parameters property, as well as the OdbcParameterCollection class and the related "Developer's Guide" documentation, well I have done that and there is nothing helpful in there, either. If you know the answer, please post it.
  Kamen

  
  
  

• mmmattias

  All right, Blair, we have some progress here I'll do my best to provide as much detail as possible.
  
  First, I'd like to add some background, especially since one of your comments was that I shouldn't be using ODBC. I am trying to expand a legacy software package to support database access. Namely, to replace its current data-logging capability from saving a single ASCII-text file (containing a single table of data with some free-style headers) to a more powerful, versatile, and user-friendly database format. Hopefully, without having to implement code for each native provider. Our customers have various restrictions and I cannot limit them to using a specific DBMS - some will want an Excel spreadsheet, others - an Access *.mdb, while there certainly will be those wanting a full-blown SQL server. Not to mention the few who will expect it to connect to their corporate Oracle server...
  
  The existing product is a nasty mixture of 3-generations-old LabVIEW code, plain C with Windows API calls, together with C++ with MFC. Now I'm about to add some .Net stuff to complete the mess. There are currently three separate processes communicating with each other using various inter-process communication methods. I'll be adding a fourth. Looks ugly but once you consider the alternatives, it's not nearly as bad.
  
  Now some background about me. I have a lot of experience in software engineering but I have never dealt with data access. I have some database management experience but it came with DBMS tools, like MS Access. Since no one in my department has that skill, and I can't hire someone specifically for that purpose, I'll have to learn it myself. It seems fun, anyway. While waiting to purchase the full 2005 Studio, I installed the Express edition - it seems good enough for the learning part. I am currently playing with test data and code. This is why the database I am dealing with is arbitrary. Here's exactly what I did.
  
  I created a database in Access with two tables in it and no relations. An equivalent command to create the database would be:
  
  

  CREATE TABLE Table0 (MyIndex INTEGER NOT NULL PRIMARY KEY, ColumnA VARCHAR (50), ColumnB VARCHAR (50), ColumnC VARCHAR (50))
  
  CREATE TABLE Table1 (MyIndex INTEGER NOT NULL PRIMARY KEY, ColumnA VARCHAR (50), ColumnB VARCHAR (50), ColumnC VARCHAR (50))
  
  Both tables have identical schemas.
  
  After running some code to connect to the database, and fill an adapter to the first table using the "SELECT * FROM Table0" command, I use OdbcCommandBuilder objects to generate INSERT, UPDATE and DELETE commands. Since the command builder in ADO.Net seems to be too weak to be practical, I am simply trying to understand how it works so I can generate my own commands. Here is the text content of the automatically generated commands:
  
  

  DELETE FROM Table0< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  WHERE (

  (MyIndex = ) AND

  (( = 1 AND ColumnA IS NULL) OR (ColumnA = )) AND

  (( = 1 AND ColumnB IS NULL) OR (ColumnB = )) AND

  (( = 1 AND ColumnC IS NULL) OR (ColumnC = ))

  )

   

   

  UPDATE Table0

  SET MyIndex = , ColumnA = , ColumnB = , ColumnC =

  WHERE (

  (MyIndex = ) AND

  (( = 1 AND ColumnA IS NULL) OR (ColumnA = )) AND

  (( = 1 AND ColumnB IS NULL) OR (ColumnB = )) AND

  (( = 1 AND ColumnC IS NULL) OR (ColumnC = ))

  )

   

   

  INSERT INTO Table0 (MyIndex, ColumnA, ColumnB, ColumnC)

  VALUES ( , , , )

  
  For the INSERT and DELETE commands, the content of the parameters as seen when inspecting the command object while in debug mode, is as follows:
  
  

  1 = MyIndex

  2 = ColumnA

  3 = ColumnA

  4 = ColumnB

  5 = ColumnB

  6 = ColumnC

  7 = ColumnC
  
  I based my original simplified example on this. I don't think I omitted anything essential but as requested, I am providing the full information (the best I could understand what is requested).
  
  I realize that I could generate my UPDATE and DELETE commands without the predicate, or just with checking that values match but it seems like the default behavior of the command builder suggests a need to check for the case where the data might have been deleted by another user. Like I originally said, it appears to me that the additional check imposes the requirement for the new data to be NOT NULL before it will be updated to a row where the source data had been deleted. I just wanted to hear a confirmation to this assumption but I would be glad to hear any general recommendations on how to generate my own INSERT, UPDATE and DELETE commands based on a DataSet structure that I have generated.
  
  Kamen

  
  
  

• grissett

  Blair Allen Stark wrote:

  first. . . I recommend you not use ODBC unless that is your only option. is a parameter. parameters don't need to be wrapped in quotes even if you are referencing text. in the help file index under 'parameters [ADO.NET] 'Using Parameters with a DataAdapter' & 'Specifying Parameters and Return Values'

  
  
  That has nothing to do with what I am asking. Which quotes are you talking about If you are referring to 'Some text' than that is not a parameter - it serves just as an example of a value that might be passed via the parameter. ODBC parameters are not named. But that has nothing to do with my question. It's not like I am getting an error and asking why. I am asking what does a construct like the one I listed above mean, i.e., what does the SQL command do when you say " = 1", or whatever " " happens to resolve to. There is nothing about that in the documentation.
  Kamen
  
  

• tonyinbeijing

  I use VS 2005.
  
  There is an Icon at the top of Solution Explorer 'Show All Files.'
  
  here. . .screen capture
  
  dont know if it exists in express
  
  
  
  
  

• Ryan Wade

  look up parameters
  
  

• IceX

  Thanks, again. I don't want to bug you any further, but is that "Dataset.designer.cs" file specific to your environment (programming language, IDE, other tools, etc.) or is it something that I could create - 'cause it sure doesn't exist in my environment
  Kamen
  
  

• lchase4411

  Thank you very much , Blair! Now I think I understand - so the whole check is only about ensuring that the udpate (or delete) happens if the data at the source hasn't changed since the last read. The additional " = 1" is only necessary because the "Column = " cannot check for NULL values. Right
  
  By the way, what tools did you use to obtain the definition and the code And is that C# I am currently using Visual C++ 2005 Express (until we buy the full version; I'm skipping all .Net Studios, coming from VC++ 6.0) and I couldn't find a way to do what you did. The debug watches are not very helpful - most of the interesting objects show as "Cannot view indexed property", "Canceling: exception...", or other errors. At least that's what happened as I was inspecting the contents of the Adapter, looking for the parameters.
  
  Anyway, I am better off now than I was before, thank you again!
  
  Kamen
  
  

• Joseph Kasende

  Ok. . .lets look at the update statement for Table0 and the first " = 1" clause . . .
  
  that ' ' is indexed parameter #5 (parameters are zero based)
  
  here is the definition for it in the .designer.cs file: 

  this._adapter.UpdateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("IsNull_ColumnA", System.Data.Odbc.OdbcType.Int, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ColumnA", System.Data.DataRowVersion.Original, true, null));

  
  
  Its name is "IsNull_ColumnA" and note it is an integer
  
  now,  when the generated adapter goes to make the update here is the code surrounding the setting of the 'ColumnA' associated parameters, look at what it does:
  

  if ((Original_ColumnA == null)) {    this.Adapter.UpdateCommand.Parameters[ 5 ].Value = ((object)( 1 ));    this.Adapter.UpdateCommand.Parameters[ 6 ].Value = System.DBNull.Value } else {    this.Adapter.UpdateCommand.Parameters[ 5 ] .Value = ((object)( 0 ));    this.Adapter.UpdateCommand.Parameters[ 6 ].Value = ((string)(Original_ColumnA)); }

  
  
  Lets look at what this says. . .
  
  If the original value of ColumnA was null, set Parameter #5 to 1 and #6 to NULL
  else
  set Parameter #5 to 0 and #6 to Original_Value
  
  
  ok. . . well lets look at the generated clause. . . remember it is a check clause 
  
   (( = 1 AND ColumnA IS NULL) OR (ColumnA = ))
  
  If the Original value was null, ' = 1' becomes '1 = 1' and the left side is true, if and only if ColumnA is Still Null
  
  else
  
  the right side becomes 'ColumnA = [whatever the orginal ColumnA was])' and evaluates to true, if and only if the Original ColumnA has not changed.
  
  so. . .
  
  The update only occurs if the Data has not changed since the last time you fetched the data, meaning that someone else changed the data in the meantime (Concurrency Violation).
  
  In short, data will be updated if ColumnA is still Null and 1 = 1 (original was null) or
  ColumnA is still equal to the original value)
  
  remember you cannot delect, by default, 'were somevalue = null' 
  
  does that make sense
  
  Now as far as odbc/ado goes, I jsut find problem after problem working with the odbc drivers so I avoid it like the plauge.
  
  You can use the jet OLE Db provider to open text and excel files.
  
  hope that helped!
  
  
  
  

• Derek Lan

  Oh man . . . forgive me kamen!!!
  
  I completely misread your original question!!!
  ================================
  (ColumnIndex = ) AND
  ( ( =1 AND TextColumn IS NULL) OR (TextColumn = ))
  ================================
  
  You are asking why is the comparison =1 being made!!!!
  No wonder I felt a quizzical glance from you!!!
  
  do me a favor (if the table isnt to big) post the schema ddl for the table.
  
  And from the generated adapter, post the sql for the delete command and the code for the delete command parameters. Lets break it down.
  
  Again. . . sorry. . .total idiot here!!!
  
  peace!
  
  
  
  
  
  
  
  
  
  
• Quick SQL question