Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 5th December 2003, 19:53
nopadon's Avatar
nopadon nopadon is offline
Member
 
Join Date: Mar 2002
Location: Thailand
Posts: 54
nopadon is on a distinguished road
Baan: Infor ERP LN FP3 - DB: Oracle and SQL 2008 - OS: Windows 2008
(Embedded SQL) and (Dynamic SQL)

Dear Sir,

I alway use embedded SQL on program script. but I never use dynamic sql. I think embedded sql is good enough for customization.

what's advantage about dynamic sql ? and what's case we cannot use embedded sql?

Thanks
Nopadon
Reply With Quote
Sponsored Links
  #2  
Old 5th December 2003, 20:34
lbencic's Avatar
lbencic lbencic is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, IL, USA
Posts: 1,148
lbencic will become famous soon enough
Baan: 2.2d - LN - DB: most - OS: most
I don't believe there is any performance difference between the 2, it's just a matter of syntax. I do a lot of customization, and yes, 99 % of the time we use embedded SQL.

You will know when you need dynamic SQL. The main reason is when you have various syntaxs for your SQL, depending on conditions above. With dynamic SQL You can write the sql statement into a variable (thus having a different value depending on what your situation dictates). Then the Main sql can just execute what you structured, whatever that is.

For (very simple) example.. you want to get the item description. in Baan IV, the items are stored in the Item Master (tiitm001) and also in the Customized Item Master (tipcs021...i think). Depending on if you are customized or not, you can fill the sql syntax using sql.parse to read either:
Code:
   "select   tiitm001.dsca:item.dsca
   from     tiitm001
   where  tiitm001._index1 = {:my.item}"
OR
   "select   tipcs021.dsca:item.dsca
   from     tipcs021
   where  tipcs021._index1 = {:my.cprj, :my.item}"

Then, later, you can execute this sql, whichever one it filled, using the sql.exec and related fetch functions. Either way, your item.dsca will get filled, and you don't have to keep writing those 2 select statements everywhere. You saw the write-up for the syntax - this is just a general overview.

This can also be used to determine the syntax of the main SQL of a report or process session, depending on the form fields they have filled out. For instance, you may want to read by index1 if they have filled out the item range, or by index2 if they have filled out a customer range, etc. You can set that syntax, and just call the 1 SQL during your process.

Also, since you store the syntax in a variable, if you have table and field names stored as variables, dynamic SQL will help translate that to actual SQL syntax.

Hope that helps, others may have more to add.
Reply With Quote
  #3  
Old 6th December 2003, 07:55
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
response

I also agree that most of the customization requirements can be satisified with embedded SQL , dynamic SQL is required in cases where the user requirements are extremely general (which can not be confined within the routine walls of if-the-else,for-next, while-endwhile loops)

A good example of dynamic SQL in Baan standard is SQL queries (ttadv3180m000) .

Similarly we also have developed a tool which uses dynamic SQL substantially.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Howto update fields in tables accessed with dynamic SQL. baanware Tools Development 12 29th November 2003 14:58
Question about dynamic sql drifter Tools Development 1 17th October 2003 17:05


All times are GMT +2. The time now is 22:01.


©2001-2017 - Baanboard.com - Baanforums.com