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 (http://www.elderscrolls.com/home/home.htm) 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]

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]

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]

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'. :)

[aGorilla]

You just couldn't live without 'totrec'. :)
Yep, might as well keep it 'familiar' :)

[Mark Hughes]

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 --

Hi Kent,

To be honest, I would have said that the syntax above was just wrong. But this won't be the first time Lance gives me a tricky question to answer. :) My best guess is that since "miva_variable_value('literal_string')" boils down to "literal_string", that "miva_variable_value('Database_Select_Count.d.COUNT (*)')" boils down to Database_Select_Count.d.COUNT(*) and Empresa interprets it as a function call, because of the letter-no-space-open-parentheses construction. But I am not convinced by my own argument. I can look into it further if need be.

As for doing record counts, the Merchant 5 application makes use of the strategy Lance employs in his "version 2" example, i.e. "SELECT COUNT(*) AS fieldname" to get the # records into the "table_name.d.fieldname" variable.

Mark

[latu]

To be honest, I would have said that the syntax above was just wrong. But this won't be the first time Lance gives me a tricky question to answer. :) My best guess is that since "miva_variable_value('literal_string')" boils down to "literal_string", that "miva_variable_value('Database_Select_Count.d.COUNT (*)')" boils down to Database_Select_Count.d.COUNT(*) and Empresa interprets it as a function call, because of the letter-no-space-open-parentheses construction. But I am not convinced by my own argument. I can look into it further if need be.

Your best guess is correct, at least based on my experience. :rolleyes:

Database_Select_Count.d.COUNT(*) is interpreted by Empresa as a function.

The use of:
<MvASSIGN NAME = "l.count" VALUE = "{ miva_variable_value('Database_Select_Count.d.' $ 'COUNT(*)') }">

was essentially a hack to overcome this issue using Miva scripting rather than using SQL.

The better solution is just what has been identified here - the use of "SELECT COUNT(*) AS fieldname" in the SQL query.

[Kent Multer]

Hey folks -- did anyone ever get this to work with MivaSQL?

I've just been working on a module that needs to retrieve record counts, so I'm using this function, which is based on the the version-2 example posted earlier in this forum. Here's the code:



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


It works fine on MySQL, but on MivaSQL I get:

MvOPENVIEW: Syntax Error: Expected identifier, found 'count'


Any ideas? Thanks --

[cagdas]

In MySQL you can use count as a column name but MivaSQL parser does not allow such a usage. If you use SELECT COUNT(*) AS rowcount FROM ... it should work.