Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Stefan Wuebbe
  Where is Stefan Wuebbe?
 Hamburg
 Germany
 Stefan Wuebbe
 To: joe Degorio
  Where is joe Degorio?
 Tacloban City
 Philippines
 joe Degorio
 Tags
Subject: RE: Delete table from Mysql
Thread ID: 345385 Message ID: 345403 # Views: 38 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Wednesday, May 23, 2012 7:35:24 AM         
   


> Hello everyone!
> I need to delete a particular table from Mysql database...but my code is not good.
>
>
> LOCAL d1
> IF h>0
>     d1="XROOM"
>     TEXT TO lcSQLScript TEXTMERGE NOSHOW PRETEXT 7
>        DROP TABLE <<m.d1>> 
>     ENDTEXT  
> Endif
>    x=SQLEXEC(h,lcSQLScript)
>    IF x>0
>    WAIT WINDOW "Room name deleted..." nowait
>    ELSE
>    MESSAGEBOX("Room name not deleted",64,"Admin")
>    Endif
> 

>
> What am I missing in my code?
>
> Thanks,
> joe


Hi Joe,

If there is an SQL command, e.g. "Drop Table xroom" in your case, then I'd do the following:

- Set a break point or put a "Set Step On" at the top of the code and watch it in the debugger.
- See whether each line runs successfully or what line gives an unexpected result.
- In this case, watch the m.lcSQLScript line in particular, and copy its content in the Debuggers "Watch" or "Locals" window.
- And if m.x < 0 in your case, paste the m.lcSQLScript content into your DB admin tool, like "MySqlAdmin", "Toad for Oracle", MS "SQL Server Management Studio" or so, and see whether it runs successfully there.
- And on the VFP side, see what goes wrong by using AError() to determine the server's error message

In addition, it's a common thing to Delete rows from tables, but quite unusual to Drop entire tables, so that I'd suspect the current user might perhaps not have required privileges (and I'd think twice about the design in this case).

However, if you use AError() on the local VFP-code side, you'd get an explanation from the server on the Why SqlExec() would have failed.


As for the code you posted, the IF/ENDIF looks suspicious:
The lcSQLScript content is getting created only if h>0, but then the SQLExec() runs anyway.

As for an example of how I would do it:

Local llSuccess, lcErrorMessage
llSuccess = .T.
lcErrorMessage = ""

Local lnSqlConnection
If m.llSuccess
    lnSqlConnection = SqlConnect(...
    llSuccess = ( m.lnSqlConnection > 0 )
    lcErrorMessage = YourAerrorFunction()
Endif

Local lcSqlCommand, lnSuccess
If m.llSuccess
    lcSqlCommand = "...
    lnSuccess = SqlExec(...
    llSuccess = ( m.lnSuccess > 0 )
    lcErrorMessage = YourAerrorFunction()
Endif

....

Try
    SqlDisconnect(m.lnSqlConnection)
Catch
EndTry

Assert m.llSuccess Message m.lcErrorMessage
Return m.llSuccess



... where YourAerrorFunction() would be a class.method() or function so that you'd need to write the AError() multi-line code only once, so that it would perhaps look like

FUNCTION YourAerrorFunction()
	LOCAL laSQLErrors[1]
	AERROR(laSQLErrors)
	RETURN ;
		TRANSFORM(laSQLErrors[1]) + ", " + ;
		TRANSFORM(laSQLErrors[2])
ENDFUNC




hth
-Stefan

ENTIRE THREAD

Delete table from Mysql Posted by joe Degorio @ 5/23/2012 5:16:07 AM
RE: Delete table from Mysql Posted by David Kurniawan @ 5/23/2012 5:38:45 AM
RE: Delete table from Mysql Posted by joe Degorio @ 5/23/2012 7:48:21 AM
RE: Delete table from Mysql Posted by Lyrad Bangoy @ 5/23/2012 7:24:48 AM
RE: Delete table from Mysql Posted by joe Degorio @ 5/23/2012 7:48:02 AM
RE: Delete table from Mysql Posted by Lyrad Bangoy @ 5/23/2012 8:29:17 AM
RE: Delete table from Mysql Posted by joe Degorio @ 5/23/2012 8:52:43 AM
RE: Delete table from Mysql Posted by Lyrad Bangoy @ 5/23/2012 10:13:26 AM
RE: Delete table from Mysql Posted by Stefan Wuebbe @ 5/23/2012 7:35:24 AM
RE: Delete table from Mysql Posted by joe Degorio @ 5/23/2012 7:58:24 AM
RE: Delete table from Mysql Posted by Kazi Hasan Mahamud @ 5/23/2012 9:23:42 AM