Fetching in SQL

[Gregory R. Zilliox]

How would you contruct a statement that would fetch thru records in a SQL table much like you would for a DBF table?
Is this possible in a script?
Greg

[Ray Fernandez]

RE: Fetching in SQL

You do not fetch rows (records) in SQL, you ask for a return record set. Once you get a return records set, which is now at the client workstation, you can do whatever you want.

Fetching Records is for File Servers, not client servers.

Let me know what you are looking for and I will give you the SQL statement. You can also do this in Access or Download the Personal MSSQL from MS. They both will write the SQL statement for you.

Hope this helps,
RF-ARS-Motorola

[Selwyn Rabins]

RE: Fetching in SQL

sure it is possible. there is actually a genie that can generate the stub code for you.

if you open the code editor, right click and select 'genies, ado' a genie will open up.

the genie dialog has a checkbox that says 'include sample code to loop over the recordset'.

let me know if this does what you want.

basically, if something can be done with ado in VB or VBScript for example, then it can be done in A5. So that means that you can pretty much go to any reference source on the web (and there are many) find some ado code, and then exeucte it (make slight adjustments to account for syntax differences between a5 and vb) in a5.

[Ray Fernandez]

RE: Fetching in SQL

It also possible to purchase a Corvette and keep it in second gear while driving. I choose to use what SQL and client-server is meant for, SPEED!

Again, you do not fetch on a client-server, you asked for a return record set. After that you can do whatever you wish, there is tremendous amount of information regarding client-server / SQL operations online.

Best regards,
RF-ARS Motorola

[Selwyn Rabins]

RE: Fetching in SQL

i don't understand what your point it.

there are plenty of reasons why you would want to create a recordset and then fetch through the data one row at a time.

for example, say you were constructing an html page from the results of a query. that code that does this is client side code. you need to loop through the result set. that's just one example. there are endless exampless.

[Ray Fernandez]

RE: Fetching in SQL

Hello Selwyn,

It is best to reread your last statement, it's quite confusing. I agree with current statement, but, I've already made the same statement earlier.

To be claer, you first request a record set, then after that is done, you can do what ever you wish, including fetching.

In case people new to SQL /client-server programming are new to thses concept, the reason it's OK, it's because at this point, the return record set is now at the client computer and not at the server and in no way can impact server performance. In fact you can unplug your client computer at this point with no side effects to the server.

Fetching rows from any SQL / client-server system directly against the server is not recommend because of the speed governing issues. Your last statement was not to clear and implied this.

Hope this clears any misunderstandings,
RF-ARS Motorola

[Tom Cone Jr]

RE: Fetching in SQL

Pardon me for lurking, but this sounds like

"You say 'tomato' and I say 'tomato'" to me.

The ambiguous term in this thread is "fetch" or "fetching". Sometimes each of you mean retrieving a record set from the server, other times the same term seems to be used by both of you to refer to stepping through the result set on the client side, one row at a time.

Not sure what to suggest but perhaps there's some new nomenclature we could use to distinguish these very different things.

-- t

[Ray Fernandez]

RE: Fetching in SQL

Tom,

I say this in the most respectful way, after you get a couple of years of client-server programming, you will see why the terminology is extremely important.

In fact, try going to Microsoft or any other major event were ten of thousands of programmers converge and start using the wrong terminology, you will not even get the time of day.

I can see someone at Alpha going to one of these events and saying that they created a form that almost SDI.

Please!!!
Sorry but, Tomato tomotto are the same things.

Fetching and return record sets are completely different.

If you plan to try to learn something new it's best to stick with the terminology that is used around the world by tens of millions of programmers that have their basis rooted in common specifications.

Respectfully.
RF-ARS-Motorola

[Selwyn Rabins]

RE: Fetching in SQL

For clarification, when you create a RecordSet using ADO, the recordset can be either on the server or on the client. ADO supports both server-side and client-side record sets. (Although not all ADO providers do - so you can create a server-side and client-side recordset with SQL server, for example, but with MySQL, you can only create a client-side recordset)

However, that's really besides the point of John Zaleski's question which was:

"How would you contruct a statement that would fetch thru records in a SQL table much like you would for a DBF table? Is this possible in a script? "

Perhaps he should have phrased his question as:

"How would you contruct a statement that would fetch thru records in a RecordSet, much like you would for a DBF table? Is this possible in a script? "

This is a perfectly reasonable question. True, your code will not operate directly against the 'table', but instead will operate against the 'RecordSet' (which is constructed from the 'table').

But even if he asked about "fetching through records in a table" instead of "fetching through records in a recorset", I think it is clear what he WANTS to do, and the answer is still -- "Yes, it is possible -- the ADO genie will create the necessary sample code for you".

[Ray Fernandez]

RE: Fetching in SQL

I'm in total agreement with you, but would like to add some clarification on cursor types (since you bring this up) with different ADO sources for people following this thread.

For future clarification.

The most common way to interact with any SQL / client-server system is having client cursor types, NOT the server cursor type. Unless this is made clear up front, a SQL programmer will assume that you are refering to client cursors. (Since this is the most likely scenario used today).

In a nutshell this means you ask the server for specific records, the server then sends these records to the client and then disconnects. This is also referred to as a disconnected record sets. The world's largest soure of Information (the Internet) works on this same principle.

And just like you say, not every ADO source has the ability to have server cursors and if they do, then you have to worry about directional and unidirectional cursors movements.

I get very picky when it when it comes to the terminology used. I know from my own personal experience than moving from a file server system to a client-server system, a programmer needs to leave alot of the old way of doing things behind.

Sincerely,
RF-ARS-Motorola

[Gregory R. Zilliox]

RE: Fetching in SQL

Ray thank you for your responses. I have a MSSQL database that has over 1,000,000 rows. To create a record set and bring all that back to the client is a lot to ask the client machine to do. If I want to step through each row and determine if a field needs to be changed how would the client work with a record set of over 1,000,000 rows?
Would cursurs help in with this?
Greg

[Ray Fernandez]

RE: Fetching in SQL

Hi Gregory,

I always recommend a client cursor, BUT YOU CAN DO THE UPDATE AT THE SERVER, regardless of the cursor type.

What is the criteria that you're looking for in that particular field to see if it's need to be updated or not?

The wonderful thing about SQL is leaving the old behind and not reinventing the wheel. The commands in SQL are make for speed, where executing a "file server" command like "fetch" will start to move all those records to the client for processing, when there's no need to.

Even more dangerous, as so many people here can relate to is trying to do a fetch command (with a server side cursor) which means there is a permanent link to the server while this is executing, anything happens to the client and you indices are out of sync. One of the reasons why client side cursors are a great advantage is that you avoid data corruption.

I also highly recommended that you download the FREE MS Personal SQL Server (only 5 users) and start to play with the SQL Manager, open the SQL table in Query mode and start to play with the interface. Extremely powerful and once you're familiar with it extremely easy to use and writes all the SQL code for you. ANY SQL CODE THAT NEED CAN BE CREATED BY THIS MANAGER.

Sincerely,
RF-ARS-Motorola

[Gregory R. Zilliox]

RE: Fetching in SQL

Ray thanks for the response again. I think the key to the update of certain records would be the proper design of the SQL statement for the UPDATE and proper use of the WHERE statements.
Thanks
Greg

[Ray Fernandez]

RE: Fetching in SQL

No Problem,

Here it is.

UPDATE "ENTER TABLE NAME HERE"
SET "ENTER FIELD TO UPDATE HERE" = "ENTER SOME VALUE HERE"
WHERE "ENTER YOUR FILTER HERE";

EX:
UPDATE Customers
SET cust_billing_state = 'FL'
WHERE cust_billing_city = 'Miami';

Does this help you?
RF-ARS-Motorola