Jump to content


Click the link below to see the new game I'm developing!


Photo
- - - - -

executing SQL commands


9 replies to this topic

#1 luken

luken

    Member

  • Members
  • PipPip
  • 18 posts
  • Gender:Male

Posted 24 March 2005 - 09:57 AM

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?

#2 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • 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.

#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.

#4 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 28 March 2005 - 02:54 PM

Guys,

In some versions, you must say SQL DYNAMIC if you use variables in your SQL.

hth,

Joseph

#5 Guest_Guest_*

Guest_Guest_*
  • Guests

Posted 29 March 2005 - 10:42 AM

In some versions, you must say SQL DYNAMIC if you use variables in your SQL.

I can't believe that!

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).

#6 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 29 March 2005 - 06:05 PM

Guest,

I can't believe that!


I'm just saying what I've seen. I don't remember which kernels, but I have seen a need to use SQL DYNAMIC for "simple" variables...

Regards,

Joseph

#7 Guest_Guest_*

Guest_Guest_*
  • Guests

Posted 29 March 2005 - 08:00 PM

Joseph,

I'm just saying what I've seen

Fair enough. But are you sure it's not a situation where a programmer confused things by explicitly quoting a string value or something so that only SQL DYNAMIC seemed to work?

#8 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,652 posts
  • 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.

#9 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • 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

#10 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,652 posts
  • 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.



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!