MivaSQL record count

[aGorilla]

Seems the magic database.d.totrec is gone when using MivaSQL. Anybody
have any suggestions for getting a record count without looping
through the db?

Markus suggested I try 'count(id)', so I gave it (and a couple others) a shot. Here are the results:

Just to make sure the 'as' isn't the problem, I tried 'select ID as
total from s01_Categories', and it worked. That said, these did not.
Seems like It's close tho', or maybe not.

Debug : select count("id") as total from s01_Categories
Debug : COUNT with expression is unimplemented

Debug : select count(id) as total from s01_Categories
Debug : COUNT with expression is unimplemented

Debug : select count() as total from s01_Categories
Debug : Syntax Error: ')' unexpected

Debug : select count as total from s01_Categories
Debug : Syntax Error: Expected Open Parenthesis

Any suggestions?

[Mark Hughes]

Any joy with COUNT(*)?

[ids]

Are we sure it's not available? I'm almost sure I've used d.totrec with 5.03 (mia and empressa) on .dbfs of course. Am I missing something about environent?

Scott

[aGorilla]

Any joy with COUNT(*)?

Yes, much. Thanks.

Just so you know, I quit Oblivion because I saw this on my other monitor, and I had to know what it said (Oblivion would crash if alt-tabbed).

Now, I have to go back and see if I can find my horse. I wandered off picking plants for some alchemy experiments, and I forgot where I left him.

[aGorilla]

Are we sure it's not available? I'm almost sure I've used d.totrec with 5.03 (mia and empressa) on .dbfs of course. Am I missing something about environent?

It'll work with dbf, but not using MivaSQL (I'm using MivaSQL on a dbf setup, and 'totrec' isn't an option).

[mvmarkus]

Any joy with COUNT(*)?

Hmm, count(column) and count(*) is not really the same, is it?

Markus

[latu]

Select Count(*)

The SELECT COUNT(*) query can be used to calculate the total number of records in a table.

Getting the resulsts of the query are a bit tricky.

It requires the use of miva_variable_value on the results:
miva_variable_value('Database_Select_Count.d.' $ 'COUNT(*)').

Here's an example function - Database_Select_Count:

<MvFUNCTION NAME = "Database_Select_Count" PARAMETERS = "tbl_name">
	<MvASSIGN NAME = "l.count" VALUE = "{ 0 }">
	
	<MvOPENVIEW NAME = "Merchant"
				VIEW = "Database_Select_Count"
				QUERY = "{ 'SELECT COUNT(*) FROM ' $ l.tbl_name }">
	
	<MvIF EXPR = "{ NOT g.MvOPENVIEW_Error }">
		
		<MvIF EXPR = "{ NOT Database_Select_Count.d.EOF }">
			<MvASSIGN NAME = "l.count" VALUE = "{ miva_variable_value('Database_Select_Count.d.' $ 'COUNT(*)') }">
		</MvIF>
	
		<MvCLOSEVIEW NAME = "Merchant" VIEW = "Database_Select_Count">
		
	</MvIF>
	
	<MvFUNCTIONRETURN VALUE = "{ l.count }">
</MvFUNCTION>

This returns the total number of records in a table.

A WHERE clause could also be added to the query.

[Tongo]

The SELECT COUNT(*) query can be used to calculate the total number of records in a table.

Getting the resulsts of the query are a bit tricky.

It requires the use of miva_variable_value on the results:
miva_variable_value('Database_Select_Count.d.' $ 'COUNT(*)').

Here's an example function - Database_Select_Count:

<MvFUNCTION NAME = "Database_Select_Count" PARAMETERS = "tbl_name">
	<MvASSIGN NAME = "l.count" VALUE = "{ 0 }">
	
	<MvOPENVIEW NAME = "Merchant"
				VIEW = "Database_Select_Count"
				QUERY = "{ 'SELECT COUNT(*) FROM ' $ l.tbl_name }">
	
	<MvIF EXPR = "{ NOT g.MvOPENVIEW_Error }">
		
		<MvIF EXPR = "{ NOT Database_Select_Count.d.EOF }">
			<MvASSIGN NAME = "l.count" VALUE = "{ miva_variable_value('Database_Select_Count.d.' $ 'COUNT(*)') }">
		</MvIF>
	
		<MvCLOSEVIEW NAME = "Merchant" VIEW = "Database_Select_Count">
		
	</MvIF>
	
	<MvFUNCTIONRETURN VALUE = "{ l.count }">
</MvFUNCTION>

This returns the total number of records in a table.

A WHERE clause could also be added to the query.

I have been scratching my head trying to figure this one too. That looks pretty nasty

[Tongo]

can you do 'select count(*) as varname........'?

[Kent Multer]

odd use of miva_variable_value

Mark -- what's going on with miva_variable_value in this example?

It seems to me that the expression:

miva_variable_value('Database_Select_Count.d.' $ 'COUNT(*)')

is equivalent to

miva_variable_value('Database_Select_Count.d.COUNT (*)')

whcih doesn't look correct. Are parentheses and asterisks legal characters in a variable name? Or has miva_variable_value been enhanced somehow for use with SQL?

Thanks --

Here's an example function - Database_Select_Count:

<MvFUNCTION NAME = "Database_Select_Count" PARAMETERS = "tbl_name">
	<MvASSIGN NAME = "l.count" VALUE = "{ 0 }">
	
	<MvOPENVIEW NAME = "Merchant"
				VIEW = "Database_Select_Count"
				QUERY = "{ 'SELECT COUNT(*) FROM ' $ l.tbl_name }">
	
	<MvIF EXPR = "{ NOT g.MvOPENVIEW_Error }">
		
		<MvIF EXPR = "{ NOT Database_Select_Count.d.EOF }">
			<MvASSIGN NAME = "l.count" VALUE = "{ miva_variable_value('Database_Select_Count.d.' $ 'COUNT(*)') }">
		</MvIF>
	
		<MvCLOSEVIEW NAME = "Merchant" VIEW = "Database_Select_Count">
		
	</MvIF>
	
	<MvFUNCTIONRETURN VALUE = "{ l.count }">
</MvFUNCTION>

This returns the total number of records in a table.

A WHERE clause could also be added to the query.

[aGorilla]

can you do 'select count(*) as varname........'?

yep, that's how I did it. 'select count(*) as total', then just use 'list.d.total' where 'list' was my view name.

[Tongo]

yep, that's how I did it. 'select count(*) as total', then just use 'list.d.total' where 'list' was my view name.

Sweet. That's what i was hoping for.

[latu]

Select Count(*) Version 2

This is an improved version of the Database_Select_Count example function.

The use of miva_variable_value has been replaced by a modification to the Query. By using "AS count" in the query, the results are returned as Database_Select_Count.d.count.

<MvFUNCTION NAME = "Database_Select_Count" PARAMETERS = "tbl_name">
	<MvASSIGN NAME = "l.count" VALUE = "{ 0 }">
	
	<MvOPENVIEW NAME	= "Merchant"
				VIEW	= "Database_Select_Count"
				QUERY	= "{ 'SELECT COUNT(*) AS count FROM ' $ l.tbl_name }">
	
	<MvIF EXPR = "{ NOT g.MvOPENVIEW_Error }">
		
		<MvIF EXPR = "{ NOT Database_Select_Count.d.EOF }">
			<MvASSIGN NAME = "l.count" VALUE = "{ Database_Select_Count.d.count }">
		</MvIF>
	
		<MvCLOSEVIEW NAME = "Merchant" VIEW = "Database_Select_Count">
		
	</MvIF>
	
	<MvFUNCTIONRETURN VALUE = "{ l.count }">
</MvFUNCTION>

[aGorilla]

A minor variation on the theme, with a major variation of the style.

Edit: Added 'g.Store_Table_Prefix' so I could (lazily) call it with Totrec('Categories','')

<MvFUNCTION NAME="Totrec" PARAMETERS="table, where">
  <MvIF EXPR="{l.where}">
    <MvASSIGN NAME="l.where" VALUE="{' ' $ l.where}">
  </MvIF>

  <MvOPENVIEW
    NAME="Merchant"
    VIEW ="Table"
    QUERY="{'select count(*) as totrec from ' $ g.Store_Table_Prefix $ l.table $ l.where}"
  >
  <MvIF EXPR="{g.MvOPENVIEW_Error}">
    <MvASSIGN NAME="l.totrec" VALUE="-1">
  <MvELSE>
    <MvASSIGN NAME="l.totrec" VALUE="{Table.d.totrec}">
  </MvIF>
  <MvCLOSEVIEW NAME="Merchant" VIEW="Table">

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

[Tongo]

You just couldn't live without 'totrec'. :)