Question Status

Suggested Answer
IamET asked a question on 17 Aug 2009 2:22 AM

Hi,

i made a form in Dexterity and i try to delete rows from two talbes but only the second delete is executed.

It looks like this:

  SQL_COMMAND[1]  = "DELETE FROM table1 ";
  SQL_COMMAND[2]  = "WHERE ITMCLSCD = 'something'"; 
  status = SQL_Execute(SQL_connection, SQL_COMMAND[1]);

  SQL_COMMAND[1]  = "DELETE FROM table2 "; 
  SQL_COMMAND[2]  = "WHERE ITMCLSCD = 'something'"; 
  status = SQL_Execute(SQL_connection, SQL_COMMAND[1]);

 i also checked the value of status and is equal to 59 after it passes through the first delete and zero after the second. I guess it should've been 0 for both.

 thanks

Reply
David Musgrave [MS] responded on 17 Aug 2009 3:03 AM

Hi there

I am not exactly sure what error 59 means, but you can try running the code on a SQL Query window to test it.

But first, the code you have shown in the example wll run only the first SQL_COMMAND array element which will delete the entire table contents as the where clause has not been applied.

I would suggest changing your method of building the SQL command to execute.

 

local text SQL_Commands;

clear SQL_Commands;
SQL_Commands = SQL_Commands + "DELETE FROM table1 ";
SQL_Commands = SQL_Commands + "WHERE ITMCLSCD = 'something'";

status = SQL_Execute(SQL_connection, SQL_Commands);

 

Also have a look at the articles on http://blogs.msdn.com/developingfordynamicsgp/pages/dexterity-articles.aspx in the SQL Server section, in particular:

How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity (KB 910129) 

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;910129

David

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David.Musgrave@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights. 

 

 

Reply
IamET responded on 17 Aug 2009 3:50 AM

I have used it like this many times before. It is actually running all lines.

I also tried the sql in an sql query and it works.

Reply
Rashid Farooq responded on 17 Aug 2009 4:31 AM

Using following code create a Dex Procedure

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------{---------------------------------------------------------------
Auther   :  Rashid Farooq
Project   :  Haier Company
Company   :   Ovex Tech - Pakistan
Date Modified :  14 April 2009
----------------------------------------------------------------
Copyright (c) Ovex Technlogies (Pvt) Limited
----------------------------------------------------------------}
pragma(disable warning LiteralStringUsed);

in text txtQuery;
local text SQLStatement;
local long lngResult,
 SQL_connection,
 GPS_error_number,
 SQL_error_number;

local string SQL_error_string,
 ODBC_error_string;
try
 lngResult = SQL_Connect(SQL_connection);     {Connection to SQL Server}
 if lngResult <> 0 then exit try; end if;
 SQLStatement = "use " + 'Intercompany ID' of globals;   {Crating SQL statement to change the Database}
 lngResult = SQL_Execute(SQL_connection,SQLStatement);  {Executing SQL Statements}
 if lngResult <> 0 then exit try; end if;
 SQLStatement=txtQuery;          {Passed Query}
 lngResult = SQL_Execute(SQL_connection,SQLStatement);  {Executing SQL Statements}
 if lngResult <> 0 then exit try; end if;
else
 error "Unable to retrieve SQL error information.";  
end try;
if lngResult <> 0  and lngResult <> 31 then
 lngResult = SQL_GetError(SQL_connection,GPS_error_number, SQL_error_number,SQL_error_string, ODBC_error_string);
  if lngResult = 0 then
   warning "GPS Error: " + str(GPS_error_number) + char(13) +
     "SQL Error: " + str(SQL_error_number) + " " + SQL_error_string + char(13) {+
     "ODBC Error: " + ODBC_error_string};
  else
   error "Unable to retrieve SQL error information.";
  end if;
end if;
lngResult = SQL_Terminate(SQL_connection);

pragma(enable warning LiteralStringUsed);
{---------------------------------------------------------------
End Of Script
----------------------------------------------------------------}

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now, call ExecuteQuery,"DELETE TABLE1 WHERE PERSON = "ABC";

Now, call ExecuteQuery,"DELETE TABLE2 WHERE PERSON = "ABC";

 

 

Reply
IamET responded on 17 Aug 2009 6:06 AM

i re-arranged the order of sql queries and somehow it works

thank u

Reply
Suggested Answer
Pam Robertson responded on 5 Feb 2014 3:11 PM

I was getting the same results on a script that contained several SQL_Execute statements in a row; every other SQL_Execute returned status 59.  I found that if I did a SQL_Terminate and then SQL_Connect in between the SQL_Executes all executed properly.  I hope this will help someone else.

Reply
Suggested Answer
Pam Robertson responded on 5 Feb 2014 3:11 PM

I was getting the same results on a script that contained several SQL_Execute statements in a row; every other SQL_Execute returned status 59.  I found that if I did a SQL_Terminate and then SQL_Connect in between the SQL_Executes all executed properly.  I hope this will help someone else.

Reply