Using Mysql for forum & stored proc. problems

[rayhawk]

I am trying to use the beer house design, but modify it for use with Mysql. I am working now on the forums, and have run into some issues. One is the stored procedures, I don't seem to be able to get stored procedures to work if the command type is commandtype.storedprocedure, but it does work if I use commandtype.text. What am I missing here-or do I just need to use commandtype.text?

My next problem is that the GetThreads routine returns zero rows, can anybody tell what I am doing wrong here? I get through the first GetThreads on loading of the Forums ok, but then when I select a forum it gives me an error stating zero rows were returned. I trimmed the tbh_ off the table names, and modified the routine as follows:

Dim sql AsString = String.Format("SELECT " & _
"posts.PostID, posts.AddedDate, posts.AddedBy, posts.AddedByIP, " & _
"posts.ForumID, posts.ParentPostID, posts.Title, posts.Approved, " & _
"posts.Closed, posts.ViewCount, posts.ReplyCount, posts.LastPostDate, " & _
"posts.LastPostBy " & _
"FROM posts " & _
"INNER JOIN forums " & _
"ON posts.forumid=Forums.forumid " & _
"WHERE ParentPostID = 0 AND Approved = 1 AND posts.forumID = {1} " & _
"ORDER BY {0} " & _
"LIMIT {2},{3};", _
sortExpression, forumID, lowerBound, upperBound)

Like I said, I use basically the same routine in the inital forum loading that appears to work (does'nt give an error) but it does'nt work for me when selecting the forum. Does anybody have an example or experience in what all needs to be changed to use Mysql for the whole site?

[Lee Dumond]

Since you are using text here, CommandType.StoredProcedure won't work, so that's the answer to your first issue.

I believe the second issue could be related to the fact that but you seem to be mIxInG CaSeS all over the place. For example:

"INNER JOIN forums " & _
"ON posts.forumid=Forums.forumid " &

You need to be more careful on how you are constructing the string in terms of casing.

[rayhawk]

Thanks for the help. I don't understand why I can't use stored procedure here. The only change I made was to add the "CALL" to the command.

PublicOverridesFunction GetForums() As List(Of ForumDetails)
Using cn AsNew MySqlConnection(Connection)
Dim cmd AsNew MySqlCommand("CALL tbh_Forums_GetForums()", cn)
cmd.CommandType = CommandType.StoredProcedure
cn.Open()
Return GetForumCollectionFromReader(ExecuteReader(cmd))
EndUsing
EndFunction

When I use this, I get the "There is no row at position 0" error. If I use commandtype.text it works. What do I need to do to call a stored procedure in mysql the right way.

I will work on the table naming issue, thanks. Column names are not case sensitive but table names are, I forgot about that.