Subscribe to this thread
Home - General / All posts - Drop Table if exists
yves61
272 post(s)
#15-Mar-21 17:45

I am looking for the right SQL syntaxis to use Drop Table If Exists in a M9 project.

I have searched the site http://www.manifold.net/doc/mfd9/ for examples but did not found any.

joebocop
472 post(s)
#15-Mar-21 18:26

Send that in as a suggestion, it has come up before.

lionel

769 post(s)
#15-Mar-21 18:28

DROP TABLE [Labels] IF EXISTS [Labels] ;

you can have a look to many websites sources like postgresql, mysql,SQLite , SL Server .... 

Attachments:
deleteifexist.map


union, doc , APIand most important deepl & keyboard shortcut

KlausDE

6,393 post(s)
#15-Mar-21 20:37

I'm surprised this works to drop an existing table. But it throws an error when the component doesn't exist.

BTW IF is not documented in help TMO.


Do you really want to ruin economy only to save the planet?

Dimitri


6,713 post(s)
#16-Mar-21 05:20

I'm surprised this works

It doesn't work. There is no IF in SQL in 9. (Can check that by entering IF into the filter box of the query builder in the Command Window)

KlausDE

6,393 post(s)
#16-Mar-21 14:38

That's why I'm surprised. The nonexistant IF doesn't throw an error and the command drops the component.


Do you really want to ruin economy only to save the planet?

Dimitri


6,713 post(s)
#16-Mar-21 14:50

The nonexistant IF doesn't throw an error and the command drops the component.

That's really puzzling. I just tried it for a drawing table that had a record in it, and this is what I got:

> DROP TABLE [Drawing Table] IF EXISTS [Drawing Table] ;

Cannot parse query.

yves61
272 post(s)
#15-Mar-21 20:39

EXIST <Table> : only for tables with at least one record ?

Dimitri


6,713 post(s)
#16-Mar-21 05:21

EXIST <Table> : only for tables with at least one record ?

Correct. From the SQL Operators topic:

EXISTS <table>

Returns True (1) if the argument contains at least one record.

tjhb

9,824 post(s)
#15-Mar-21 20:02

A script is needed to do this in Manifold 9 SQL.

The reason is that SQL9 requires that the schema of the result be knowable at compile time. Tests at runtime can determine results, but not control execution.

A script can look for the component in question, then (compile and) execute an SQL command if necessary, otherwise not.

yves61
272 post(s)
#15-Mar-21 20:37

Thank you Tim.

So trivial within most SQL flavours , but lacking in M9 ....

Will dig into M9 script for now.

Dimitri


6,713 post(s)
#16-Mar-21 07:25

How Manifold implements EXISTS is standard SQL. As Chris Fehily puts it:

EXISTS and NOT EXISTS don’t compare values; rather, they simply look for the existence or nonexistence of rows in a subquery result.

EXISTS isn't a test for whether a table exists or doesn't exist. It is a test for whether a subquery returns any rows. Since a subquery returns a table, you can also use a table as a "subquery". But all you're asking is whether that table has any rows or not.

Strictly speaking, a construction such as EXISTS [no table by this name] should throw an error just like using EXISTS without any name of a table or any subquery at all. It's just as wrong as writing SELECT * FROM; without naming a table. That it works at all in some DBMS packages is a hackish convenience.

It's one of those deals like having an IF. SQL is a declarative language, not a procedural language. As Fehily puts it:

If you’ve programmed in a language such as C or JavaScript, then you’ve used a procedural language, in which you specify the explicit steps to follow to produce a result. SQL is a declarative language, in which you describe what you want and not how to do it; your database system’s optimizer will determine the “how.” As such, standard SQL lacks traditional control-flow constructs such as if-then-else, while, for, and goto statements.

So sure, in 9 there is no IF, and there isn't a GOTO either.

But that's not going to stop some SQL implementations from doing hackish things that try to make SQL a procedural language, at least in a few small ways (keywords like IF and GOTO are tipoffs). Those small things can be convenient, and Manifold is happy to do things like that also if they make sense and they don't go too far afield, but they also can take you away from learning how to use SQL. It's a balance.

Another way of approaching the same thing is to say, OK, Manifold makes it exceptionally easy to use either a script or SQL whenever one or the other is a better choice, even mixing them. For doing procedural language things, use a procedural language. You can always call a short snippet of SQL, like DROP [table name], from within a script.

It seems that making it easy to use procedural languages as well as SQL is a better approach than, for lack of convenient integration with procedural languages, hacking SQL to try to make it a procedural language. But I don't think this is a matter worth making any sort of purist stand on principle. It's just a question of balance, tradeoffs between procedural conveniences and the fundamentally declarative nature of SQL.

danb


1,822 post(s)
#15-Mar-21 21:58

That is a shame this would be really useful. I was going to request it but wanted to see if it was possible achieve by interrogating the 'System Data' tables using SQL first.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

yves61
272 post(s)
#16-Mar-21 07:18

I have this SQL code which returns the tablename 'myTable' if the corresponding table name is found in mfd_root (if corresponding mfd_id > 0) but do not know how to integrate this further in a (inline ?) script in order to pass the result of this query (1 record, name= 'myTable' ) to a parameter and drop the table 'myTable'.

SELECT name FROM (

EXECUTE WITH (@tblname nvarchar = 'myTable')

[[ SELECT mfd_id, name FROM [mfd_root]

WHERE [Name] = @tblname ]]

)

WHERE mfd_id> 0

tjhb

9,824 post(s)
#16-Mar-21 07:45

You need to start with the API.

Any SQL required will be a trivial command.

No need to complicate things!

sga7 post(s)
#23-Mar-21 14:19

In case somebody is interested, here is how I do it (almost all credits goes to the API documentation http://manifold.net/doc/api/scripts-net.html#database_delete where I found most of the C# function used down here)

Hope this helps

In a Query:

-- Function definition

FUNCTION dropTableIfExists(@s NVARCHAR) INT32 AS SCRIPT [dropTableIfExists]

ENTRY 'Script.dropTableIfExists';

-- Call of the function (the variable @p will get a value of 0 if the table did not exist, 1 if it existed and got deleted)

VALUE @p INT32 = dropTableIfExists('myTable');

In a script called dropTableIfExists

// C#

class Script

{

static Manifold.Context Manifold;

static int dropTableIfExists(string tableName)

{

Manifold.Application app = Manifold.Application;

using (Manifold.Database db = app.GetDatabaseRoot())

{

// delete component tableName if it exists

string type = db.GetComponentType(tableName);

if (!string.IsNullOrEmpty(type))

{

db.Delete(tableName);

app.Log("Deleted table: " + tableName);

return 1;

}

else {

return 0;

}

}

}

static void Main()

{

}

}

yves61
272 post(s)
#23-Mar-21 14:40

This VBScript (credits to examples on this forum and the manual ) , which I changed a little to my needs is working for me.

' VBScript

Sub Main

ComponentsToDrop = "mycomp1 , mycomp2 , mycomp3 "

Set app = Manifold.Application

Set db = app.GetDatabaseRoot() ' opened MAP file

Set table = db.Search("mfd_root")

' get list of records

Set records = table.SearchAll(Array("mfd_id","Name", "Type"))

If (records Is Nothing) Then

Exit Sub ' no records

End If

Do While records.Fetch

' read record values

Set values = records.GetValues()

mfd_idV = CDbl(values(0).Data)

nameV = values(1).Data

'typeV = values(2).Data' careful with this

If Instr(ComponentsToDrop, nameV) > 0 Then

text = "DROP "+typeV+" ["+nameV+"];"

db.Run(text)

End if

Loop

' app.OpenLog

End Sub

artlembo


3,201 post(s)
#24-Mar-21 13:01

this seems overly complex for those looking for a quick bang-bang solution.

I agree with Dimitri that one can always add a special compromise and the slippery slope it creates (when do you stop creating special commands). But, some of that already exists in much of what we do in a spatial context - those are extensions to the SQL language.

In the case of DROP TABLE IF EXISTS, we see this implemented in Postgres, SQLite, and also SQLServer as of 2016. And, Oracle, with an IF statement, does appear to have a way to do this without needing to write a script.

I don't know about anyone else, but I find myself writing SQL code where I am constantly creating a new table, and when I run the code numerous times as part of testing (and even implementation), I want to make sure the table doesn't already exist. So, I would vote to add this exception to the SQL syntax in 9.

yves61
272 post(s)
#24-Mar-21 14:15

DROP TABLE IF EXISTS ... I would vote to add this exception to the SQL syntax in 9.

I fully second your vote.

artlembo


3,201 post(s)
#24-Mar-21 14:21

In this case, the next step is to send the request into sales@manifold.net

apo
147 post(s)
#24-Mar-21 15:56

one does not prevent the other. Yes for me M9 should provide a "IF EXIST" clause as it is more than useful, but in the meantime and just because it is already useful to me today, thanks to sga and Yves for the solutions. Her (sga) solution is complete and works like a charm staying in my SQL environment.

tjhb

9,824 post(s)
#24-Mar-21 16:54

I would “third” the vote (if that is English, I doubt it), except for one thing:

The IF EXISTS syntax is manifestly redundant.

All we need changed is for DROP TABLE to make the check itself (silently and tolerantly), rather than throwing an error if we try to drop a non-existent table.

We might make a typo, but when is that a problem with this statement? If the supposed table does exist, it is dropped. If not, it can’t be, so (um) not.

All the same, while throwing an error in the latter case is an overreaction, a warning in the Log would be more than welcome, to mean “check your assumptions (and spelling)”.

In my opinion, on reflection, that is all that needs changing. We don’t need new syntax, just a different response.

I agree wholeheartedly with Art’s description of the main use case for this. Similar workflow, all the time.

joebocop
472 post(s)
#24-Mar-21 17:49

If we're forced to extend functionality by using scripts, let's also acknowledge that Viewer will not be usable for demos, etc. It's a bummer.

adamw


9,738 post(s)
#25-Mar-21 14:37

Agree.

We'll try to do this.

yves61
272 post(s)
#25-Mar-21 15:30

What about having a similar function for the other components : DROP Drawing, Image, Surface ... IF EXISTS . Not just limitating to DROP TABLE IF EXISTS ?

adamw


9,738 post(s)
#25-Mar-21 15:33

We'll alter DROP in general, for any component type.

artlembo


3,201 post(s)
#10-Sep-21 13:05

on a similar note, Postgres allows:

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

it would be nice to issue an ADD COLUMN statement, and if the column already exists, ignore the error warning. I'm writing a function that adds a column, but if it already exists, the SQL will fail. It would be nice to only add the column if it exists.

adamw


9,738 post(s)
#12-Sep-21 10:41

These folks have [IF EXISTS] / [IF NOT EXISTS] everywhere. We'll think about adding this.

One problem we have with adding a field like that is that, fine, let's say, a field with the name you want to use exists, but what if it has the wrong type? The query will continue and could just end up producing garbage (eg, you dutifully compute some strings and then they get converted into an INT which converts them to NULL), all without any errors. So, IF NOT EXISTS helps repeat runs of the same query (do not have to DROP / re-ADD), but it does so at the cost of potentially harming runs of different queries, or different versions of the same query. Then again, if you know what you are doing, being able to run a query that adds fields and never drops them repeatedly, as you write and adjust it, is useful, no questions.

PS: We did alter DROP some time ago, just in case. DROP <type> x; does nothing if x does not exist.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.