Stored Procedures

[John A. Bailo]

Ted wrote:

> 1) How do you deal with the possibility that a call may be made to a
> given stored procedure before the previous call is finished?

If you're executing them in SQL -- they are not threaded or asynchronous
(they can be if you're running them from a java program).

Each statement runs and completes before the next one.

Every statement in a stored procedure finishes before the next sproc can
be run in the main script.

> 2) How do you use data from one SQL statement in a second within the
> procedure?

> I need to retrieve that value and use it in a second insert, into the
> second table, as the foreign key. I haven't found an illustration of
> the SQL required to do this within a stored procedure.

You should be able to do this all in one statement with a SELECT...INTO

Otherwise, use a CURSOR and iterate through it.

[Axel Schwenke]

"John A. Bailo" <jabailo@texeme.com> wrote:

> Ted wrote:

>> 1) How do you deal with the possibility that a call may be made to a
>> given stored procedure before the previous call is finished?

>
> If you're executing them in SQL -- they are not threaded or asynchronous
> (they can be if you're running them from a java program).

What are you talking about?
What has Java to do with MySQL stored procedures?

> Each statement runs and completes before the next one.
>
> Every statement in a stored procedure finishes before the next sproc can
> be run in the main script.

This has nothing to do with some "main script". A stored procedure is
executed in a MySQL session (vulgo: client connection). And yes, this
session is blocked as long as the SP is running. However, other SP
(or even the same SP) can be executed in another session concurrently.

After all there is no difference if you put some statements into a SP
and execute it or if you execute those statements directly.

>> 2) How do you use data from one SQL statement in a second within the
>> procedure?

>

>> I need to retrieve that value and use it in a second insert, into the
>> second table, as the foreign key. I haven't found an illustration of
>> the SQL required to do this within a stored procedure.

Use variables. SELECT <column names> INTO <variable names>
To access an AUTO_INCREMENT value, better use LAST_INSERT_ID().


XL

[John A. Bailo]

Axel Schwenke wrote:

> This has nothing to do with some "main script". A stored procedure is
> executed in a MySQL session (vulgo: client connection). And yes, this
> session is blocked as long as the SP is running. However, other SP
> (or even the same SP) can be executed in another session concurrently.

Ok, I re-read the OP and it wasn't clear exactly what he was saying.

What he was saying is (paraphrased):

I have a stored procedure A that increments the fields in 10 records by
a value of 1.

It then executes a second step on those records.

If that same exact stored procedure is called twice, the increment will
run simultaneously and that will conflict with the execution of the
second step.

So, what he is really talking about is using record locking to block
increments while the sproc is running, and also, using Transaction blocks.

[Harald Fuchs]

In article <1135225890.095670.58280@g43g2000cwa.googlegroups. com>,
"Ted" <r.ted.byers@rogers.com> writes:

> Thanks. That is just what I need. I didn't know LAST_INSERT_ID()
> existed. None of my database/SQL books mention it. Is it standard SQL
> or something unique to MySQL?

The latter.

> Would I, for example, find it, or
> something similar, in e.g. Postgres?

Most DBMSs have something like that. PostgreSQL calls it "currval".

[Craig77]

hi i am using microsoft access and i need a stored procedure, currently i am
using coldfusion to display the following below,

this is running a bit slow so i was told a stored procedure would be better, i
know access pretty well but have never delt with stored procedures, could you
give me an example of a stored procedure i would use for this instant.

many thanks
Craig

<cfquery name="ResACAT" datasource="Dgy">
SELECT *
FROM Aged_Care_Facility_Details
WHERE
High_Residential_Care_Total_No_Beds= Yes OR
High_Residential_Care_Booked_Respite_Program= Yes OR
High_Residential_Care_Secure_Dementia_Care_Unit= Yes OR
High_Residential_Care_Extra_Services= Yes OR
High_Residential_Care_Couples_Accommodation= Yes OR
HighCare_Ethno_Specific= Yes OR
LowCare_Ethno_Specific= Yes OR
Low_Residential_Care_Total_No_Beds= Yes OR
Low_Residential_Care_Booked_Respite_Program= Yes OR
Low_Residential_Care_Secure_Dementia_Care_Unit= Yes OR
Low_Residential_Care_Extra_Services= Yes OR
Low_Residential_Care_Couples_Accommodation= Yes OR
Retirement_Living_Total_No_of_Residencies= Yes OR
Retirement_Living_Resident_Funded_Villas= Yes OR
Retirement_Living_Service_Apartments= Yes OR
Retirement_Living_24hr_OnSite_Supervisor= Yes OR
Retirement_Living_Communal_Facilties= Yes OR
Retirement_Living_Residential_Aged_Care_OnSite= Yes OR
Supported_Residential_Services_Total_No_Beds= Yes OR
Supported_Residential_Services_Booked_Respite_Prog ram= Yes OR
Supported_Residential_Services_24_Hour_OnSite_Supe rvision= Yes OR
Supported_Residential_Services_Couples_Accommodati on= Yes OR
Supported_Residential_Services_Secure_Dementia_Car e= Yes OR
Supported_Residential_Services_Private_Room_Ensuit es= Yes
ORDER BY FacilityName
</cfquery>

[The ScareCrow]

Because of the driver you can't call a ms access function (as far as I know)

The number of "OR" staements will be causing the slow response.

I would also suggest not using "*" (this will also slow response)
List the column names even if you need them all.

Try changing the "yes" to 1 (may improve response)

Another option maybe to have a seperate query for each "OR" and then "UNION"
them together.

Ken

[Craig77]

ok thanks

when you said to do a seperate query for each, how would i union them together?

could you show me an example

Craig

[The ScareCrow]

can't guarentee it will make it quicker, but it may

Like so, note you should also change "*" to the list of actual columns (for
every "*")
<cfquery name="ResACAT" datasource="Dgy">
SELECT *
FROM Aged_Care_Facility_Details
WHERE High_Residential_Care_Total_No_Beds= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE High_Residential_Care_Booked_Respite_Program= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE High_Residential_Care_Secure_Dementia_Care_Unit= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE High_Residential_Care_Extra_Services= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE High_Residential_Care_Couples_Accommodation= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE HighCare_Ethno_Specific= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE LowCare_Ethno_Specific= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Low_Residential_Care_Total_No_Beds= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Low_Residential_Care_Booked_Respite_Program= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Low_Residential_Care_Secure_Dementia_Care_Unit= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Low_Residential_Care_Extra_Services= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Low_Residential_Care_Couples_Accommodation= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Retirement_Living_Total_No_of_Residencies= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Retirement_Living_Resident_Funded_Villas= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Retirement_Living_Service_Apartments= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Retirement_Living_24hr_OnSite_Supervisor= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Retirement_Living_Communal_Facilties= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Retirement_Living_Residential_Aged_Care_OnSite= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Supported_Residential_Services_Total_No_Beds= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Supported_Residential_Services_Booked_Respite_Prog ram= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Supported_Residential_Services_24_Hour_OnSite_Supe rvision= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Supported_Residential_Services_Couples_Accommodati on= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Supported_Residential_Services_Secure_Dementia_Car e= 1
UNION
SELECT *
FROM Aged_Care_Facility_Details
WHERE Supported_Residential_Services_Private_Room_Ensuit es= 1
ORDER BY FacilityName
</cfquery>

Ken

[paross1]

Believe it or not, I have actually called an Access "stored procedure" from
ColdFusion using CFSTOREDPROC and returned results via CFPROCRESULT. You can
even send parameters using CFPROCPARAM. You save your query in Access, and you
add a PARAMETERS statement if you pass any IN parameters from CF. However, I
don't know if this particular query would run any faster from within Access
than it is from CF.

Phil