When executing a couple of SQL commands in a function , the first command is only executed.
We are running Windows Server2003, Proiv 5.5r345.
I have tried revision 308 as well.
MFC 524.
example
SQL
BEGIN TRANSACTION
DELETE FILE1 WHERE FIELD = :$VAR
INSERT INTO FILE1
SELECT * FROM FILE2 where field = :$VAR
COMMIT
ENDSQL
The delete commmand completes , the session hangs.
I have run the sql profiler to check commands being sent to SQL Server.
The only command sent is the delete command.
I have split the above logic by doing the delete then committing, then executing the insert separately , the session hangs and the delete is the only command that is sent.
I have separated the logic into 2 different logic numbers and the same happens.
It seems that Proiv is only sending the first SQL command and not the rest.
Has anybody experienced this and have any suggestions?
Click the link below to see the new game I'm developing!
executing SQL commands
Started by
luken
, Mar 24 2005 09:57 AM
9 replies to this topic
#2
Mike Schoen
-
- Members
-
- 198 posts
Expert
- Gender:Male
- Location:Guelph, Canada
Posted 24 March 2005 - 01:58 PM
I've run into this as well.
I got around it by wrapping each action in its own SQL statement, eg:
SQL
DELETE FILE1 WHERE FIELD = :$VAR
ENDSQL
SQL
INSERT INTO FILE1
SELECT * FROM FILE2 where field = :$VAR
ENDSQL
I dont use the begin transaction/commit myself, as everything done within the functions where I do this are considered one transaction, and it either commits on function exit or gets rolled back.
I got around it by wrapping each action in its own SQL statement, eg:
SQL
DELETE FILE1 WHERE FIELD = :$VAR
ENDSQL
SQL
INSERT INTO FILE1
SELECT * FROM FILE2 where field = :$VAR
ENDSQL
I dont use the begin transaction/commit myself, as everything done within the functions where I do this are considered one transaction, and it either commits on function exit or gets rolled back.
#3
Guest_Guest_*
Guest_Guest_*
-
- Guests
Posted 26 March 2005 - 05:07 PM
luken,
Note the ProIV documentation warns you not to do transaction handling yourself in SQL.
I don't think SQL..ENDSQL was ever intended to handle multiple SQL statements.
Although, having said that, maybe it can support a SQL "procedure" (such as PL/SQL anonymous block in Oracle).
However - I've only ever seen that used to to invoke a separate stored procedure in the database.
Also hard to guess if host variables would work properly.
Note the ProIV documentation warns you not to do transaction handling yourself in SQL.
I don't think SQL..ENDSQL was ever intended to handle multiple SQL statements.
Although, having said that, maybe it can support a SQL "procedure" (such as PL/SQL anonymous block in Oracle).
However - I've only ever seen that used to to invoke a separate stored procedure in the database.
Also hard to guess if host variables would work properly.
#5
Guest_Guest_*
Guest_Guest_*
-
- Guests
Posted 29 March 2005 - 10:42 AM
I can't believe that!In some versions, you must say SQL DYNAMIC if you use variables in your SQL.
My understanding of the docs is that SQL DYNAMIC is only relevant when you need to use a ProIV alpha variable or variables to specify the entire WHERE clause in the SQL. In that situation, your ProIV variable(s) are not "host" variables in the normal SQL sense (I think). Also SQL DYNAMIC only exists in V5.5.something and later
The "normal", "traditional" use of ProIV variables as SQL host variables that provide a single "scalar" value should never require the use of SQL DYNAMIC (and you should not use SQL DYNAMIC in situations where the "traditional" use is sufficient).
#8
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 30 March 2005 - 07:03 AM
Hi,
Also be careful using SQL DYNAMIC.
It could effect performance, since ProIV / Oracle has to re-parse the SQL command every time if you are calling the SQL frequently... which gives you a bit of a performance hit.
Rob D.
Also be careful using SQL DYNAMIC.
It could effect performance, since ProIV / Oracle has to re-parse the SQL command every time if you are calling the SQL frequently... which gives you a bit of a performance hit.
Rob D.
#9
mdexter
-
- Members
-
- 70 posts
Advanced
- Gender:Male
Posted 31 March 2005 - 02:40 AM
For what it's worth, we use SQL DYNAMIC exclusively, since we need it in many cases and it is confusing switching back and forth between the two sql options (mainly because one needs quotes around the inserted variables and one doesn't). I very much doubt if you would be able to measure any performance difference in the commands. In most of our applications, we only issue a sql command at the beginning of a cycle. It's possible that if you had a function that did one or more SQL commands for every row in the table that you might be able to measure a performance difference, but even here I would doubt it.
So I would use whichever command suits your purposes and not worry about performance. HTH. Mark Dexter
So I would use whichever command suits your purposes and not worry about performance. HTH. Mark Dexter
#10
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 04 April 2005 - 04:39 AM
Hi,
I guess it depends on your application, but we try to embed SQL if we can so that we get the performance out of the DB.
If you use SQL DYNAMIC then Oracle will have to parse each SQL command, because ProIV sends the value and not a bind variable to SQL. But it does depend on how your code is written.
This means that Oracle has to reparse the statement every time, which will give you a performance hit on large systems.
I just always code with the best performance in mind (as long as it does not 'over' complicate the code too much), even is situations that might not seem to need it.
I'm just used to working on large systems, and if your SQL is 1/2 second slower, it could be the reason why the server will only support 400 rather than 600 users.
Rob D.
I guess it depends on your application, but we try to embed SQL if we can so that we get the performance out of the DB.
If you use SQL DYNAMIC then Oracle will have to parse each SQL command, because ProIV sends the value and not a bind variable to SQL. But it does depend on how your code is written.
This means that Oracle has to reparse the statement every time, which will give you a performance hit on large systems.
I just always code with the best performance in mind (as long as it does not 'over' complicate the code too much), even is situations that might not seem to need it.
I'm just used to working on large systems, and if your SQL is 1/2 second slower, it could be the reason why the server will only support 400 rather than 600 users.
Rob D.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
