SQL tools & syntax 3

[ajetrumpet]

Will someone please provide me with some definitions?
I would like to know the difference between the following tools:
1)MySQL, 2)Oracle, 3)Microsoft Query

How do these relate to writing code in MS Access? Are there any differences, even minor ones? What is the connection between these tools and the SYNTAX concept? How do I found out the connection between these tools and the language that works in Access? thanks guys.

-J

 

[JoeAtWork]

There really isn't any direct relation between SQL and VBA. I could write this:

   Dim rs As ADODB.Recordset
   
   Set rs = New ADODB.Recordset

   'Let's assume that CN is a connection to an MDB
   'file we've already opened.
   'ADO is passing on the request to retrieve data to
   'the JET database engine.  JET understands SQL, so we
   'are giving it an SQL statement.  VBA doesn't understand
   'SQL, this string is meaningless to it.
   rs.Open "SELECT LastName FROM Employees", CN

   'Now that Jet has retrieved the data for us, let's do
   'whatever we oringally wanted it for - let's put it
   'in a textbox
   txtName.Text = rs("LastName")

Let me say again, they are two different things. SQL doesn't need VBA to exist, and VBA doesn't need SQL. SQL is used by databases, so if in my VBA program I need something from the database, I connect to it and in the language it understands (SQL) I request the information.

The "bridge" in this case are the ADO objects.

If my programming language was C#, I would be using ADO.NET objects, which despite the name simularity are quite different from ADO. In C#, I would be working with DataSets, DataTables, DataRows, etc. instead of recordsets. But ultimately, somewhere in my C# code I would be passing one of the ADO.NET objects the exact same SQL string, i.e. "SELECT LastName FROM Employees"

// Again, let's assume connection is already open
using (SqlCommand cm = cn.CreateCommand())
{
  cm.CommandText = "[COLOR=red]SELECT LastName FROM Employees[/color]";
  cm.CommandType = CommandType.Text;

  using (SqlDataReader dr = new SqlDataReader(cm.ExecuteReader()))
  {
    dr.Read();
    txtName.Text = dr.GetString("LastName");
  }
}

Looks quite different from VBA, doesn't it? But notice that the SQL statement is exactly the same. Just like VBA, C# has no notion of what SQL is. It is just blindly passing the string to the database engine.

The art of programming is often an exercise in dividing the work into components. If it's done well, the different components are black boxes to each other. The components collaborate together, but do not know how each does its work. The programming language (VBA, C#, etc.) has absolutely no awareness of SQL. You as the programmer hopefully have some idea what it means, but it is gibberish to the programming language.

What I suggest is you start working through one of your books in the language of your choice (I guess it's VB or VBA, you might want to think about VB.NET or C#). If the book is any good, it will have at least one chapter on working with databases. Hopefully at that point you will have realized that the programming language is just one way to interface with the database.

 

[MichaelRed]

I like joe's expliniation ... following the extensive dialog preceediing it his expliniation seems to be more or less on the mark of your overall inquiry.

I prefer to think of SQL simply as the argument to another intrinsic (e.g. already built in) function. Of course it is a bit more complex than your sample sub to force the content of a cell to be shown as a string, but the overall functionallity of using a 'black box' object to retrieve, format nad/or manipulate some information is the same.

In that vein, the database engine (MySQL, SQLServer, Oracle, Jet, ...) are simply functions with specific requirements for their arguments. Consider your sample procedure from above. It would be poossible to modify it to have an arguments passed to it instead of directing you (the user) to change the range reference as required. Then, the call to your procedure would include the range. The argument(s) would, necessarily be of a type appropiate to the call. Just as the "call" to the database enging necessarily includes the appropiate argument "type", which it considers to be a properly formatted (for the specific database enging) string.




MichaelRed

 

[ajetrumpet]

Thank you both for your information.
Well, with everyone's information on this page, I think I could put together a tutorial on the ART of programming. This is essentially the idea I was getting at, and I have the understand now that I needed to go forward with it. Thanks to everyone who replied, I got something from all of you.

 

[lespaul]

Well, if that was your purpose, you didn't address that at all...the ART in programming is the logic and the analysis...The puzzle solving...the coding is the work required to actually create the vision.

you're presented with a problem, a need or an improvement...you look at the way the process is currently done...you envision a better way...you think about all the different ways it COULD be done and determine what's the best way...then you put together all the ideas and present to the user this new great thing you're going to give them that's going to make doing their job easier/better/more efficient...then you get to the drudgery and work of writing the code that will make your vision a reality. Debugging...troubleshooting...and while there is still some of the logic and problem solving required in the coding...the ART is in the design.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ajetrumpet]

I agree with you Lesli.

I happen to think that ART + TECHNIQUE = MASTERY in any discipline, thus I inquired about the technical aspect and the knowledge base I need to undertake this.

-J

 

[lameid]

While I think Joe has said a lot and I think you have it, I think there is a fundamental basis for the confusion if you are starting with Access.

Access is an atypical product as it combines both a DBMS (Database Management System) and a method to manage the presentation to the user or interface layer. Typically One product is the DBMS, and the other the tool for creating the interface layer. This is an over simplification and does not address where the LOGIC lives. DBMS's support a way to retrieve an update information and ways to create tables to store the data.

What is interesting about this is that DBMS's (typically) have an interface tool included that allows you to perform operations and interface with the DBMS, through a flavor of SQL (and in the grey area of this, more). Microsoft query is a simple version of such a tool and is analogous to the query designer in Access. This tool is not equivalent to building an interface which all the "programming languages" mentioned here do. I use quotes because SQL is a programming language. It is merely a different kind. SQL is a DECLARATIVE programming lanugage. In other words you say, hey I want XYZ and it goes and gets XYZ without much hassle. The other programming languages are not Declarative. All programming languages use logic.

These other languages have followed an evolution and to the best of my recollection listed below; come to think of it may have been jargon specific to a specifc product...

First generation - Machine code, think old plugin wire telephone operator, you have to physically make the connection. Eventually there were better ways to develop and load machine code like...

Second Generation... Assembly. A programming language that allows you to use basic words to accomplish tasks... Put this value in this memory location. Perform this operation on these two memory locations.

Third generation - Procedural programming language. Performs a series of operations linearly and uses contructs like loops to perform repetitive operations. I.E. Basic (before Visual Basic).

Fourth Generation - Object oriented programming. Objects have procedural programming behind the scenes but have hooks to make them more mangeable. Think Visual Basic and VBA.

Now if I have a 4GL (4th generation language) and I want to work with a database, I could write one but it is much more efficient to talk to a DBMS and send it commands in SQL.

To confuse matters, large DBMS's blur the line. MS SQL server for example supports purely Declarative programming through SQL. However it also has a superset called Transact SQL (T-SQL) which has the key elements of a 3GL and arguably pieces of 4GL. However even in this it does not have an interface layer to the server. You have to use an interface to interact with it. Sure it has an interface Tool prebuilt but it is an interface. So just as I can pass SQL to the server I can just as easily pass T-SQL.

I hope this provided a little more insight for you.

Although you did ask a question about learning about the differences between languages and how they do things. I think it is safe to say there are three principle ways of programming that you may see... 3GL, 4GL and Declarative. 4GL is more of a superset of 3GL and so agurably there are just 2, 4GL and Declarative. Interestingly all Declarative languages I can think of are SQL languages. They are not only similar in structure and functionality, they are also similar in syntax or where certain kinds of puntuation used in the language goes and key words that do things. All "go get me this record" syntax has a select clause, from clause and optional clauses (Where, Group By, Having). As you add to the root SQL language, they begin to diverge. On the other hand, all 4GL languages tend to have similar structures but the syntax differences between them are more like going from one written language to another (Spanish to French). Some words and grammar are similar and some are different. These Programming Languages are based on English (in our case) so once you know one reading another is usually intuitive even if you are not familiar enought to write in it.

In the Generation speak I was using I would have to say that .NET is a Fifth generation. It makes a common code base for all functions so different languages can share the same funtionality more easily. I am just starting to learn it myself so take this paragraph with 2 grains of salt instead of the usual 1.

 

[ajetrumpet]

Nice post Lameid, thank you for the information. I'm sure I'll pick this up quickly, the only things left to figure out are the differences between the languages. I know they are small, but they ARE different, it'll be more of an annoyance than anything. Whoever created these DBMS programs though was obviously pretty smart. Don't want to copy another flavor of language! =) That's not good business, or rather its not LEGAL business. It might spell disaster...

-J

By the way, I am not a beginner, per se, with the Access program, but I've done just about everything you can possibly do with the program without knowing advanced coding functions.