How to find if MySQL table exists

[wcw]

Using mivascript, is there a way to tell if a table exists in MySQL? With mivasql it is a simple task using fexists(whatever.dbf).

[BillBuilt]

One way would be to turn off error reporting for MvOPENVIEW, try an MvOPENVIEW on the table, check the MvOPENVIEW_ERROR for 'not exists' or whatever Empressa reports.

[wcw]

That works but I was hoping for a simple query without having to open the database before I DROP it. The need is I want to be able to DROP a database. But if I run that DROP query and it does not exist, I get an error.

[Nerd Boy Inc]

Dear Bill,

Try doing a MvQUERY with a query of: DROP TABLE IF EXISTS `tablename`

It should return true if it drops the table or if the table doesn't exist and will only return a fail if the table does exist but it can't drop it for some reason, like invalid permissions.

[wcw]

I wonder if MivaSQL can use that same command. If would be a pain to first have to check the database type, then branch and use fexists for one and IF EXISTS for the other.

[wcw]

The command
<MvQUERY NAME = "Merchant"
QUERY = "{ 'DROP TABLE IF EXISTS ' $ g.Store_Table_Prefix $ 'PowerSearchSearches' }">
does not work in mivascript. The MvQUERY returns this error:

Miva Merchant has encountered a fatal error and is unable to continue. The following information may assist you in determining the cause of the error:

Error Code: MER-UTL-POW-00015
Description: Syntax Error: SQL command not properly ended
Other Information:

[BillBuilt]

End the query with a semi-colon.
<MvQUERY NAME = "Merchant"
QUERY = "{ 'DROP TABLE IF EXISTS ' $ g.Store_Table_Prefix $ 'PowerSearchSearches;' }">

[wcw]

Now that is interesting. That semi-colon has not been needed in any other mvquery commands. I wonder why this one.

[Kent Multer]

Yeah ... sort of makes me want to go back and try evrything I ever tried, that didn't seem to work in MivaSQL :^.

[wcw]

You are right Kent. It does not work in MivaSql. The only way to test appears to be the openview with erroroutputlevel set to none. Then if there is no openview_error, go ahead and drop the table. Probably a minor bug in the miva engine.

[wcw]

For others who might wrestle with this. The semi-colon is NOT needed if it is MySQL. If it is MivaSQL the "IF EXISTS" does not work no matter what you do. So you have to test the DBAPI and then either use fexists or IF EXISTS, depending on the database.

<MvIF EXPR = "{'mivasql' CIN Module_Library_Native_DBAPI}">
<MvIF EXPR = "{fexists(g.MerchantPath $ 's' $ padl( g.Store:id, 2, '0' ) $ '/powersearchsearched.dbf')}">
<MvQUERY NAME = "Merchant"
QUERY = "{ 'DROP TABLE ' $ g.Store_Table_Prefix $ 'PowerSearchSearched' }">
<MvIF EXPR = "{ g.MvQUERY_Error }">
<MvFUNCTIONRETURN VALUE = "{ [ g.Module_Library_Utilities ].Error( 'MER-UTL-POW-00011', g.MvQUERY_Error ) }">
</MvIF>
</MvIF>
<MvELSE>
<MvQUERY NAME = "Merchant"
QUERY = "{ 'DROP TABLE IF EXISTS ' $ g.Store_Table_Prefix $ 'PowerSearchSearched' }">
<MvIF EXPR = "{ g.MvQUERY_Error }">
<MvFUNCTIONRETURN VALUE = "{ [ g.Module_Library_Utilities ].Error( 'MER-UTL-POW-00011', g.MvQUERY_Error ) }">
</MvIF>
</MvIF>

What I didn't realize before doing this exercise is that miva merchant is naming the file name (in mivasql) different from what you use in the create function. I used PowerSearchSearched for the table name. But the database created is powersearchsearched.dbf, all lower case. I hope that is the case on all operating systems. Otherwise the fexists() trick will fail.

[BillBuilt]

Now that is interesting. That semi-colon has not been needed in any other mvquery commands. I wonder why this one.

Although I've not dealt with Merchant, this looks like a Merchant error message and not an Empressa error. True, some server's may not need the semi-colon, but i do believe it is part of the syntax for SQL queries. I always end all SQL queries with a semi-colon just in case.

[wcw]

The MvQUERY function in empressa is a mivascript wrapper to go around the regular QUERY. The wrapper function is probably adding the semi-colon for all of its commands. However, for some reason it is not supporting the IF EXISTS when using MivaSQL. As that fails, it must be also not adding the semi-colon that it normally adds with MvQUERY, hence the error message. To extend the IF EXISTS for MivaSQL in the engine, Miva would have to add the fexists similar to what I have done above if the DBAPI was mivasql. I am sure there are thousands of variations of these commands and Miva has only implemented a subset of them for MivaSQL. It is just something we have to test for when writing modules so that we can put in a workaround.

[sebenza]

This will tell you if a table exists or not:

<MvFUNCTION NAME = "Database_Select_Check" PARAMETERS = "tbl_name" STANDARDOUTPUTLEVEL = "" ERROROUTPUTLEVEL = "syntax, expression">

<MIVA MvOPENVIEW_Error = "nonfatal, nodisplay">

<MvASSIGN NAME = "l.query" VALUE = "{ 'SELECT * FROM ' $ l.tbl_name }">

<MvOPENVIEW NAME = "Merchant"
VIEW = "Database_Select_Check"
QUERY = "{ l.query }">

<MvIF EXPR = "{ g.MvOPENVIEW_Error }">
<MvASSIGN NAME = "l.ok" VALUE = "{ 0 }">
<MvELSE>
<MvASSIGN NAME = "l.ok" VALUE = "{ 1 }">
</MvIF>

<MvCLOSEVIEW NAME = "Merchant" VIEW = "Database_Select_Check">

<MvFUNCTIONRETURN VALUE = "{ l.ok }">
</MvFUNCTION>

[wcw]

If there is an error with mvopenview, you will probably get an error with the mvcloseview unless you put it inside the conditional for no error.

I had this approach based on the idea from BillBuilt but I decided it would go faster to use Jeff's idea of IF EXISTS. Since the majority are using MySQL, using the built in command would eliminate an unnecessary opening. Using <MvASSIGN NAME = "l.query" VALUE = "{ 'SELECT * FROM ' $ l.tbl_name }"> on a million record table without limits is not an efficient event. Then it is going back to the miva engine error handler to return that it did not work. I don't know how efficient that code is. So there are two potential slowdowns that can be avoided with IF EXISTS.

Then if they have MivaSQL, the quick check of fexists is probably still faster than trying to open a dbf table that does not exist.

[sebenza]

My example will not output any errors.

[wcw]

Interesting. I have seen closeview errors when developing and I forgot to openview the table in the first place.

[wcw]

My example will not output any errors.

I tested this and it does produce an error. Your example hides the error message for MvOPENVIEW. It does not hide it for MvCLOSEVIEW. So when the MvCLOSEVIEW is issued, there is an error. As I suggested above, the MvCLOSEVIEW needs to go in the conditional when it does open ok.

<MvIF EXPR = "{ g.MvOPENVIEW_Error }">
<MvASSIGN NAME = "l.ok" VALUE = "{ 0 }">
<MvELSE>
<MvCLOSEVIEW NAME = "Merchant" VIEW = "Database_Select_Check">
<MvASSIGN NAME = "l.ok" VALUE = "{ 1 }">
</MvIF>

Error if not done as above.
Runtime error in /mm5/5.00/modules/util/test.mvc @ [00000017:00000278]: test.mv: Line 172: MvCLOSEVIEW: Database 'Merchant' has no view named 'Database_Select_Check'

[sebenza]

No, my function hides any error output for MvCLOSEVIEW too.... otherwise all of our modules would be throwing errors.

[BillBuilt]

I had this approach based on the idea from BillBuilt but I decided it would go faster to use Jeff's idea of IF EXISTS. Since the majority are using MySQL, using the built in command would eliminate an unnecessary opening. Using <MvASSIGN NAME = "l.query" VALUE = "{ 'SELECT * FROM ' $ l.tbl_name }"> on a million record table without limits is not an efficient event.

This would be a good "cross-DBAPI" implementaion. The efficiency could be greatly increased by using a query like:
<MvASSIGN NAME = "l.query" VALUE = "{ 'SELECT `[1_known_small_field_to_test_on_that_always_has_a_v alue_like_ID]` FROM ' $ l.tbl_name$' LIMIT 1;' }">

Using a field that always has a value, like an ID field, this will only return 1 field from 1 record if the table exists.

[wcw]

They probably do. In your example you are doing
<MIVA MvOPENVIEW_Error = "nonfatal, nodisplay">
But that is for the MvOPENVIEW error. It is not doing anything for MvCLOSEVIEW error. I tested it and it threw the error I posted.

[wcw]

This would be a good "cross-DBAPI" implementaion. The efficiency could be greatly increased by using a query like:
<MvASSIGN NAME = "l.query" VALUE = "{ 'SELECT `[1_known_small_field_to_test_on_that_always_has_a_v alue_like_ID]` FROM ' $ l.tbl_name$' LIMIT 1;' }">

Using a field that always has a value, like an ID field, this will only return 1 field from 1 record if the table exists.

LIMIT does not work with MivaSQL.

[sebenza]

They probably do. In your example you are doing
<MIVA MvOPENVIEW_Error = "nonfatal, nodisplay">
But that is for the MvOPENVIEW error. It is not doing anything for MvCLOSEVIEW error. I tested it and it threw the error I posted.

No, they don't Bill. That error has not shown up once in over 2 years of using that function in every single one of our modules. I think I would know if it was a problem.

[eMediaSales]

Hi Bill & Scott,

First a few quick recommendations. Instead of using "select *" in your test, try using "select count(*) as cnt". This does two things different:

1) If there ARE thousands of records, it only prepares a single row result set to be returned to the client.

2) When you're using MySQL and you have a unique index on the table, this operation will be very fast and efficient, regardless of # of records. I suspect the same is true with MivaSQL.

3) Put the MvCLOSEVIEW within the condition for MvOPENVIEW_Error. There's no need to close the view if the open errored.

There ARE times when knowing the dbapi underlying database is important. Abstraction and exact SQL compatibility is important for utility and most programs, but it IS acceptable to use the underlying technology directly when it benefits you, so long as you provide cross-compatible versions of the function on all database types. In otherwords, go ahead and create MivaSQL and MySQL specific code, just always make sure both provide identical end results. For complex operations, it is nearly impossible to write 100% cross-vendor compatible SQL code. Dialects differ. Miva had the benefit of writing their own DB to match as much of MySQL's dialect as was necessary for the base Merchant functionality. Adding to a language is complex, so I'd doubt they'll be adding new features to MivaSQL just for us. :)

For example if you want to write more efficient queries when using MySQL (ie: use mysql specific features such as limit), check the variable g.Library_Filename_Native_DBAPI. This is a simple test, it will either contain "dbapi_mivasql.mvc" or "dbapi_mysql.mvc".

Finally - Bill - to address your question re: Drop table /* IF EXISTS */ ...

1) Placing a semicolon in a programmatic SQL query is NEVER necessary. Doing so should never effect the outcome of the query, but if it's effecting the results/error messages, it's because some underlying database is probably interpreting this as TWO queries separated by a semicolon, the second one empty.

2) If you're NOT worried about data loss, and/or recovery of the "IF NOT EXISTS" clause, just ignore the error you're getting. Set it to nodisplay, nonfatal, and go on about your business without the IF NOT EXISTS clause at all. The end result is no errors displayed, database gone whether or not it existed prior.

3) If you really want to know for sure if a database exists, you could also open up the Merchant5/mivamerchant.dbf "old-school-style", eg:
<MvOPEN NAME = "Merchant" DATABASE = "Merchant5/mivamerchant.dbf" INDEXES = "mivamerchant.mvx, mivamerchanti.mvx">. This is the database structure / metadata for the MivaSQL tables (underlying DBF structures). Just make sure never to change anything here. :)

Regards,
james

[eMediaSales]

Scott's function probably suppresses the display of an MvQUERY_Error via the ERROROUTPUTLEVEL setting. Though the error certainly IS thrown when closing a view that errored on open.

[sebenza]

Scott's function probably suppresses the display of an MvQUERY_Error via the ERROROUTPUTLEVEL setting. Though the error certainly IS thrown when closing a view that errored on open.
Correct, it would throw an error... but not visible due to suppression like you suggest.

[wcw]

Like I said, your example was not correct as written. You have to change the ERROROUTPUTLEVEL setting. In your example <MvFUNCTION NAME = "Database_Select_Check" PARAMETERS = "tbl_name" STANDARDOUTPUTLEVEL = "" ERROROUTPUTLEVEL = "syntax, expression">, I would have thought that would display an error. That said, it is more efficient as I and James said, to close the view only if the open was successful. See my example code that does work without the ERROROUTPUTLEVEL setting being set prior to the check.

[wcw]

Drop table /* IF EXISTS */ fails with MivaSQL, with or without a semi colon. Hence, the DBAPI check in my example at the beginning of page 2 in this thread. That example does work without error.

[wcw]

Scott's function probably suppresses the display of an MvQUERY_Error via the ERROROUTPUTLEVEL setting. Though the error certainly IS thrown when closing a view that errored on open.

James,

Scott says his example which uses <MvFUNCTION NAME = "Database_Select_Check" PARAMETERS = "tbl_name" STANDARDOUTPUTLEVEL = "" ERROROUTPUTLEVEL = "syntax, expression"> does not cause an error with MvCLOSEVIEW when the MvOPENVIEW failed. He said he has used it in all of his modules for two years and never saw an error. I tested the example function and it does yield an error. Is there a difference in empresa versions or the connector? That would be a bug if it behaves differently between engines.

[sebenza]

My example is used to pull one record from one table at module install. Could it be done differently... sure. Will it produce a visible error... no. I have not taken any time to change it since it would not result in any performance increases for my scenario. I posted it in efforts to help you and contribute to the forum. How you dissect is not my worry... I just hope it proved to be helpful in some way or another.

[wcw]

My response two months ago was that it would generate an error with the MvCLOSEVIEW if it was never opened. I thought you would get an error based on your example code.

My original comment two months ago was to let you know there was an error in your code in case you were using it in a module. In most cases that would have gotten a "hey thanks, I missed that". But you said it did not cause an error and have confirmed that even after I tested yesterday and do get an error. Hence, bug in empresa.

[sebenza]

I don't think its a bug in Empressa... because the error is being generated but the example function is suppressing the error. I double checked my build system and my modules are not using the invalid function. So I tested the example function to confirm that the error is present but just suppressed.

[wcw]

If ERROROUTPUTLEVEL = "syntax, expression" suppresses the error, what would you have if you wanted to show the errors? The documentation is a not clear on this point.

It talks about the MvTAGNAME_ERROR="fatal/nonfatal,display/nodisplay" but that would only turn off that specific tag. How is the openview tag turning off display of the closeview error?

[wcw]

I got to the bottom of this. It is the ERROROUTPUTLEVEL = "syntax, expression" that hides the error messages. The MIVA MvOPENVIEW_Error = "nonfatal, nodisplay" has no effect on the error message display as long as the ERROROUTPUTLEVEL is in the function opening tag. Without the individual error tag it is fine. But if you take out the "syntax, expression" at the top, the messages appear.

[RayYates]

When checking a large table, would this be faster

'SHOW COLUMNS FROM ' $ l.tbl_name $ ';'

than this?

'SELECT * FROM ' $ l.tbl_name

[Kent Multer]

Probably ... too bad it doesn't work on MivaSQL :^.

[RayYates]

It seems this would avoid the problem of opening a large table to see if it exsists.

'SELECT * FROM ' $ l.table $ ' LIMIT 0 , 1 '

BUT, Does LIMIT Work In MivaSQL?

[Kent Multer]

BUT, Does LIMIT Work In MivaSQL?

No, at least not yet. Miva said it was on the to-do list back in March; see

http://extranet.mivamerchant.com/forums/showthread.php?t=2803&highlight=limit+mivasql

[RayYates]

As for the original problem...

I may have miss-read some of the postings but, why check to see if the file exists at all? Just drop it with error reporting disabled.

<Miva MvQUERY_ERROR="nonfatal,nodisplay">
<MvQUERY NAME = "Merchant" QUERY = "{ 'DROP TABLE tablename' }">

[Brandon MUS]

I don't know anything about mivasqls short comings, but can you use SHOW TABLE STATUS LIKE 'tablename'; ? I doubt its that easy, but that's what I use that to detect table overhead in mysql.

[Kent Multer]

I know that SHOW COLUMNS doesn't work in MivaSQL. I'm not sure about SHOW TABLES.

[Gary Osborne]

Using mivascript, is there a way to tell if a table exists in MySQL? With mivasql it is a simple task using fexists(whatever.dbf).
<MvFUNCTION NAME = "texists" PARAMETERS = "table_name" ERROROUTPUTLEVEL = "syntax, expression" STANDARDOUTPUTLEVEL = "">
<MvCOMMENT>
Determines if the given table_name exists in the open, default, MySQL database defined by g.default_schema.
Returns true if the table exists.
</MvCOMMENT>
<MvIF EXPR = "{tname(l.table_name, 0, 'LIB-SQL-texists.1', 'Validate table name')}">
<MvASSIGN NAME = "l.sql" VALUE = "{'SELECT COUNT(*) FROM ' $ l.table_name $ ';'}">
<MvASSIGN NAME = "l.ok" VALUE = "{tquery(l.sql, 0, 'LIB-SQL-texists.2', 'TABLE EXISTS ERROR ON TABLE: \'' $ encodeentities(l.table_name) $ '\'')}">
<MvELSE>
<MvASSIGN NAME = "l.ok" VALUE = "0">
</MvIF>
<MvFUNCTIONRETURN VALUE = "{l.ok}">
</MvFUNCTION>


<MvFUNCTION NAME = "tquery" PARAMETERS = "sqlcmd, fatal, error_code, error_msg" ERROROUTPUTLEVEL = "syntax, expression" STANDARDOUTPUTLEVEL = "">
<MvCOMMENT> Executes a MySQL query. </MvCOMMENT>
<MvIF EXPR = "{len(g.default_schema) GT 0}">
<MvQUERY NAME = "{g.default_schema}" QUERY = "{l.sqlcmd}">
<MvIF EXPR = "{len(MvQUERY_Error)}">
<MvCOMMENT> An error here probably means that the sql statement has a syntax error. </MvCOMMENT>
<MvIF EXPR = "{len(l.error_code) EQ 0}">
<MvASSIGN NAME = "l.error_code" VALUE = "LIB-SQL-tquery.1">
</MvIF>
<MvIF EXPR = "{len(l.error_msg)}">
<MvASSIGN NAME = "l.error" VALUE = "{'QUERY ERROR: ' $ encodeentities(l.error_msg) $ '<P>' $ encodeentities(MvQUERY_Error) $ '</P>'}">
<MvELSE>
<MvASSIGN NAME = "l.error" VALUE = "{'QUERY ERROR: ' $ encodeentities(MvQUERY_Error)}">
</MvIF>
<MvIF EXPR = "{g.default_verbose}">
<MvASSIGN NAME = "l.error" VALUE = "{l.error $ '<P><PRE>' $ encodeentities(l.sqlcmd) $ '</PRE></P>'}">
</MvIF>
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "l.ok" VALUE = "{Error(l.error_code, l.error)}">
<MvELSE>
<MvASSIGN NAME = "l.ok" VALUE = "1">
</MvIF>
<MvELSE>
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "l.ok" VALUE = "{Error('LIB-SQL-tquery.2', 'Default Schema is not defined.')}">
</MvIF>
<MvIF EXPR = "{NOT l.ok}">
<MvIF EXPR = "{(0 + l.fatal) EQ 1}">
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "" VALUE = "{Screen_Error()}">
<MvEXIT>
<MvELSEIF EXPR = "{(0 + l.fatal) EQ 0}">
<MvASSIGN NAME = "l.ok" VALUE = "0">
<MvELSEIF EXPR = "{NOT isdigit(l.fatal)}">
<MvIF EXPR = "{l.fatal CIN MvQUERY_Error}">
<MvASSIGN NAME = "l.ok" VALUE = "1">
</MvIF>
</MvIF>
</MvIF>
<MvFUNCTIONRETURN VALUE = "{l.ok}">
</MvFUNCTION>

Best Regards,

music44.com
http://www.music44.com

[RayYates]

<MvIF EXPR = "{tname(l.table_name, 0, 'LIB-SQL-texists.1', 'Validate table name')}">

Very Interesting. Where is the tname() function?

Am I correct in saying SELECT COUNT(*) returns the number of records rather than all the records? That would seem to be faster and use less memory than SELECT *.

[Gary Osborne]

Very Interesting. Where is the tname() function?

Am I correct in saying SELECT COUNT(*) returns the number of records rather than all the records? That would seem to be faster and use less memory than SELECT *.


Yes, that's what select count(*) does. It just queries the index to see how many records are there. It's blazing fast in MySQL.

tname() is a little function I wrote to validate that characters used for a MySQL table name are alphanumeric and underscore. Here it is...


<MvFUNCTION NAME = "tname" PARAMETERS = "table_name, fatal, error_code, error_msg" STANDARDOUTPUTLEVEL = "">
<MvCOMMENT> Returns true if the given table_name can be used for a MySQL table. </MvCOMMENT>
<MvIF EXPR = "{len(l.table_name)}">
<MvIF EXPR = "{len(l.error_code) EQ 0}">
<MvASSIGN NAME = "l.error_code" VALUE = "LIB-SQL-tname.1">
</MvIF>
<MvIF EXPR = "{len(l.error_msg)}">
<MvASSIGN NAME = "l.error" VALUE = "{'TABLE NAME ERROR: ' $ encodeentities(l.error_msg) $ ' ON TABLE: \'' $ encodeentities(l.table_name) $ '\''}">
<MvASSIGN NAME = "l.error" VALUE = "{'TABLE NAME ERROR: ' $ encodeentities(l.table_name) $ '\''}">
<MvELSE>
<MvASSIGN NAME = "l.error" VALUE = "{'TABLE NAME ERROR ON TABLE: \'' $ encodeentities(l.table_name) $ '\''}">
</MvIF>
<MvASSIGN NAME = "l.ok" VALUE = "1">
<MvASSIGN NAME = "l.table_name" VALUE = "{tolower(l.table_name)}">
<MvASSIGN NAME = "l.pos" VALUE = "0">
<MvWHILE EXPR = "{l.ok}">
<MvASSIGN NAME = "l.pos" VALUE = "{l.pos + 1}">
<MvASSIGN NAME = "l.chr" VALUE = "{substring(l.table_name, l.pos, 1)}">
<MvIF EXPR = "{len(l.chr)}">
<MvASSIGN NAME = "l.ok" VALUE = "{(('a' LE l.chr) AND (l.chr LE 'z')) OR (('0' LE l.chr) AND (l.chr LE '9')) OR (l.chr EQ '_')}">
<MvELSE>
<MvWHILESTOP>
</MvIF>
</MvWHILE>
<MvIF EXPR = "{NOT l.ok}">
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "l.ok" VALUE = "{Error(l.error_code, l.error)}">
</MvIF>
<MvELSE>
<MvIF EXPR = "{len(l.error_code) EQ 0}">
<MvASSIGN NAME = "l.error_code" VALUE = "LIB-SQL-tname.2">
</MvIF>
<MvIF EXPR = "{len(l.error_msg)}">
<MvASSIGN NAME = "l.error" VALUE = "{'TABLE NAME MISSING ERROR: ' $ encodeentities(l.error_msg)}">
<MvELSE>
<MvASSIGN NAME = "l.error" VALUE = "{'TABLE NAME MISSING'}">
</MvIF>
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "l.ok" VALUE = "{Error(l.error_code, l.error)}">
</MvIF>
<MvIF EXPR = "{l.fatal AND (NOT l.ok)}">
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "" VALUE = "{Screen_Error()}">
<MvEXIT>
</MvIF>
<MvFUNCTIONRETURN VALUE = "{l.ok}">
</MvFUNCTION>


... and here is a similar function to validate the characters used for a MySQL view name...



<MvFUNCTION NAME = "vname" PARAMETERS = "viewname, fatal, error_code, error_msg" STANDARDOUTPUTLEVEL = "">
<MvCOMMENT> Returns true if the given viewname can be used for a MySQL view. </MvCOMMENT>
<MvIF EXPR = "{len(l.viewname)}">
<MvIF EXPR = "{len(l.error_code) EQ 0}">
<MvASSIGN NAME = "l.error_code" VALUE = "LIB-SQL-vname.1">
</MvIF>
<MvIF EXPR = "{len(l.error_msg)}">
<MvASSIGN NAME = "l.error" VALUE = "{'VIEW NAME ERROR: ' $ encodeentities(l.error_msg) $ ' ON VIEW: \'' $ encodeentities(l.viewname) $ '\''}">
<MvELSE>
<MvASSIGN NAME = "l.error" VALUE = "{'VIEW NAME ERROR ON VIEW: \'' $ encodeentities(l.viewname) $ '\''}">
</MvIF>
<MvASSIGN NAME = "l.ok" VALUE = "1">
<MvASSIGN NAME = "l.viewname" VALUE = "{tolower(l.viewname)}">
<MvASSIGN NAME = "l.pos" VALUE = "0">
<MvWHILE EXPR = "{l.ok}">
<MvASSIGN NAME = "l.pos" VALUE = "{l.pos + 1}">
<MvASSIGN NAME = "l.chr" VALUE = "{substring(l.viewname, l.pos, 1)}">
<MvIF EXPR = "{len(l.chr)}">
<MvASSIGN NAME = "l.ok" VALUE = "{(('a' LE l.chr) AND (l.chr LE 'z')) OR (('0' LE l.chr) AND (l.chr LE '9')) OR (l.chr EQ '_')}">
<MvELSE>
<MvWHILESTOP>
</MvIF>
</MvWHILE>
<MvIF EXPR = "{NOT l.ok}">
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "l.ok" VALUE = "{Error(l.error_code, l.error)}">
</MvIF>
<MvELSE>
<MvIF EXPR = "{len(l.error_code) EQ 0}">
<MvASSIGN NAME = "l.error_code" VALUE = "LIB-SQL-vname.2">
</MvIF>
<MvIF EXPR = "{len(l.error_msg)}">
<MvASSIGN NAME = "l.error" VALUE = "{'VIEW NAME MISSING ERROR: ' $ encodeentities(l.error_msg)}">
<MvELSE>
<MvASSIGN NAME = "l.error" VALUE = "{'VIEW NAME MISSING'}">
</MvIF>
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "l.ok" VALUE = "{Error(l.error_code, l.error)}">
</MvIF>
<MvIF EXPR = "{l.fatal AND (NOT l.ok)}">
<MvDO FILE = "{g.Library_Filename_Utilities}" NAME = "" VALUE = "{Screen_Error()}">
<MvEXIT>
</MvIF>
<MvFUNCTIONRETURN VALUE = "{l.ok}">
</MvFUNCTION>


... then you can put the whole thing together with the next function that validates a table and a view name. You can call it before you do anything in any table to help keep your database intact...


<MvFUNCTION NAME = "tvnames" PARAMETERS = "viewname VAR, tablename_suffix, default, error_code, error_msg" STANDARDOUTPUTLEVEL = "">
<MvIF EXPR = "{NOT len(l.viewname)}"><MvASSIGN NAME = "l.viewname" VALUE = "{l.default}"></MvIF>
<MvASSIGN NAME = "l.ok" VALUE = "{vname(l.viewname, 1, l.error_code, l.error_msg)}">
<MvASSIGN NAME = "l.ok" VALUE = "{tname(l.default $ l.tablename_suffix, 1, l.error_code, l.error_msg)}">
<MvFUNCTIONRETURN VALUE = "{l.ok}">
</MvFUNCTION>



Best regards,

music44.com
http://www.music44.com

[Brandon MUS]

Yes, that's what select count(*) does. It just queries the index to see how many records are there. It's blazing fast in MySQL.Maybe on certain tables, but on large tables I would expect it to be more of a burden than it needs to be. For a simple query, you could always do something like SELECT 1 FROM mytable LIMIT 1. That would still fail if the table didn't exist.

[RayYates]

There are several efficient ways to do this in MYSQL the problem is keeping compatible with Miva-SQL. LIMIT is not implimented.

See the Miva-SQL list: http://extranet.mivamerchant.com/forums/showthread.php?t=19071

[Brandon MUS]

Wow, no limit? I guess I'll bow out of this thread. I've never understood MivaSQLs purpose if it lacks so much functionality.

[Rick Wilson]

MivaSQL's purpose was legacy support for our old Dbase infrastructure. Our future lies with MySQL.

[Gary Osborne]

Maybe on certain tables, but on large tables I would expect it to be more of a burden than it needs to be. For a simple query, you could always do something like SELECT 1 FROM mytable LIMIT 1. That would still fail if the table didn't exist.


SELECT COUNT(*) FROM table;

The speed of this query is not a function of the number of records in the table. Any table, no matter how large, if it has at least one index, has a variable set to indicate the total number of records. It's a fast query even on really big tables.

Yes, no table will will cause an error. But go back an look at my script. It catches the error and returns 1 or 0 depending on whether the table exists or not.

Best Regards,

music44.com
http://www.music44.com

[truXoft]

Well, if you like it complicated, turning off error reporting, using a query, and then checking the error variable and turning back the reporting is certainly an option. Personally though I prefer it simple and fast. And fortunately it is still as simple and as fast in v5 with both MivaSQL and MySQL as it was with the old good dbf files and their totrec variable.

So you ask how to do it? Easy - as easy as with the totrec mentioned previously:
<MvIF EXPR="{len(table.d.EOF)}">
table exists
<MvELSE>
table does not exist
</MvIF>


The EOF flag is either "0" or "1" (hence len = 1) if the table exists, and it is empty string "" (null and hence len = 0) if there is no such table. And of course, it also works if the table existed just a while ago but was closed - EOF will be null (len=0) again.