Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: Database: MySql
 "Exception occurred" error when updating counts		  New Topic  Topic Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 15 October 2006 :  15:45:03  Show Profile  Send ruirib a Yahoo! Message
We have no solution for this, nor MySQL... it seems.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

tsnyder91
Starting Member

3 Posts
Posted - 11 January 2007 :  13:33:11  Show Profile
http://bugs.mysql.com/bug.php?id=10527

The issue is the MyODBC 3.51 and using the SUM() function in a MySQL query.

It will not work on values that are not set to a data type of DOUBLE.

A post mentions changing Integer field types to Double. I tried it and it WORKS!!

Below are the tables that are query'd in steps 4 and 5. The fields just need to be changed to a datatype of DOUBLE and they worked for me!


FORUM TABLE
===========
F_TOPICS
F_COUNT
F_A_TOPICS
F_A_COUNT

TOPICS TABLE
============
T_LAST_POST_AUTHOR
T_REPLIES

A_TOPICS TABLE
================
T_REPLIES
T_LAST_POST_AUTHOR * Not query'd but done for consistency when archiving data.
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 11 January 2007 :  15:47:17  Show Profile  Send ruirib a Yahoo! Message
Thx for the info, tsnyder91.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

Davio
Development Team Member

Jamaica
12217 Posts
Posted - 11 January 2007 :  16:04:29  Show Profile
Yeah thanks tsnyder91. Will check test it out and report back here.
Support Snitz Forums
Go to Top of Page

tsnyder91
Starting Member

3 Posts
Posted - 17 January 2007 :  12:17:21  Show Profile
Any word on if this worked for you guys?
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 17 January 2007 :  15:18:35  Show Profile  Send ruirib a Yahoo! Message
Must say I haven't tested it yet.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

Hermes
Junior Member

Croatia
113 Posts
Posted - 14 April 2007 :  19:16:12  Show Profile

It works. I have tested it just now.



ASP Snitz Forum Upute za instalaciju
http://www.kairos.com.hr http://www.hermetizam.com Forum

not so newbie any more :)
Go to Top of Page

binu_ji
Starting Member

2 Posts
Posted - 03 January 2008 :  02:52:44  Show Profile
Its working, thanks tsnyder91
Free 8051 Microcontroller projects
Free 8085 Microprocessor projects
Free AVR Microcontroller projects
Go to Top of Page

modifichicci
Average Member

Italy
787 Posts
Posted - 05 January 2008 :  11:12:26  Show Profile  Visit modifichicci's Homepage
I have tested update forum counts on the same db in mysql4 and mysql5.0.4
mysql4 no problem
mysql5 error

changing tables as sad before no errors in mysql 5

Sometimes mysql5 give error in SELECT COUNT AS (variable) function so the count varible as to be cLng(variable) anywhere, but it affects only some occourrences and I don't know why in some lines it give a correct value and in someothers I get error.
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum
Go to Top of Page

Davio
Development Team Member

Jamaica
12217 Posts
Posted - 05 January 2008 :  13:49:43  Show Profile
Ok so you tried the database table changes posted by tsnyder91 and it fixed the problems in MySQL 5?
Did you try the database table changes with MySQL 4? See if the changes worked ok with it?

You also saying you getting errors in SELECT COUNT AS (variable) queries even with the changes suggested by tsnyder91?
Support Snitz Forums
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 05 January 2008 :  13:54:15  Show Profile  Send ruirib a Yahoo! Message
Double type works ok with both 4 and 5.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

modifichicci
Average Member

Italy
787 Posts
Posted - 05 January 2008 :  14:03:31  Show Profile  Visit modifichicci's Homepage
I get select count errors somewhere in code, as in forum stats mod (it uses its table of course)
I don't know now where such a function is called using that tables.. no errors in counting topics and reply in default and forum (but that isn't a select count function) but I get a 0 value always in these function:

if strDBType = "access" then
	  strSqL = "SELECT count(M_TO) as [pmcount] "
	else
	  strSqL = "SELECT count(M_TO) as pmcount "
	end if
	strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS , " & strTablePrefix & "PM "
	strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.M_NAME = '" & strDBNTUserName & "'"
	strSql = strSql & " AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID = " & strTablePrefix & "PM.M_TO "
	strSql = strSql & " AND " & strTablePrefix & "PM.M_READ = 0 "
	Set rsPM = my_Conn.Execute(strSql)
	pmcount = rsPM("pmcount")
	rsPM.Close

to get private mess

if you can suggest where I have to look, I will look.
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 05 January 2008 :  14:27:06  Show Profile  Send ruirib a Yahoo! Message
The PM count doesn't work on 5.0? It works for me...

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

modifichicci
Average Member

Italy
787 Posts
Posted - 05 January 2008 :  14:33:47  Show Profile  Visit modifichicci's Homepage
we get a 0 value, but that doesn't occour in every forum.. strange.. changing to Clng it disappears
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum
Go to Top of Page

Davio
Development Team Member

Jamaica
12217 Posts
Posted - 07 January 2008 :  12:42:28  Show Profile
Testing these problems against the latest version of the MyODBC driver version 3.5.22, it seems to have fixed the problem as was previously stated.

But with that, there was some minor Type Mismatch errors that showed up on step 4. Lines 407 and 427 complained of Type Mismatch.

Line 407: intF_COUNT = rs1("cnt") + rs1("SumOfT_REPLIES")

Line 427: intF_A_COUNT = rs1("cnt") + rs1("SumOfT_REPLIES")

Added the CLng() functions to them and the Type Mismatch went away.

But now, the rs1("SumOfT_REPLIES") was returning a null value which CLng() function doesn't like. The SUM() function will return a null value if no results where found. Unlike the COUNT() function will return a 0.

So lines 407 - 408 was changed from this:
intF_COUNT = rs1("cnt") + rs1("SumOfT_REPLIES")
intF_TOPICS = rs1("cnt")
to this:
if IsNull(rs1("SumOfT_REPLIES")) then
	intF_COUNT = rs1("cnt")
else
	intF_COUNT = CLng(rs1("cnt")) + CLng(rs1("SumOfT_REPLIES"))
end if
intF_TOPICS = rs1("cnt")


And lines 427 - 428 was changed from this:
intF_A_COUNT = rs1("cnt") + rs1("SumOfT_REPLIES")
intF_A_TOPICS = rs1("cnt")
to this:
if IsNull(rs1("SumOfT_REPLIES")) then
	intF_A_COUNT = rs1("cnt")
else
	intF_A_COUNT = CLng(rs1("cnt")) + CLng(rs1("SumOfT_REPLIES")
end if
intF_A_TOPICS = rs1("cnt")


I know many web hosts might not be running the latest MyODBC drivers, so changing the database fields to use DOUBLE type can be done to fix those cases.
Support Snitz Forums
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000 Version 3.4.07