MySql Stored Procedure problem

Sorry, the Update statement in the procedure was not correct. Here is the right one:

UPDATE BookQuest SET Title = pTitle, RespParty = pRespParty, Publisher = pPublisher, PubDate = pPubDate, `Subject` = pSubject, Binding = pBinding, PageCount = pPageCount, USListPrice = pUSListPrice, Demand = pDemand, OnOrder = pOnOrder, OnBackorder = pOnBackorder, SpeedStock = pSpeedStock, `Status` = pStatus, BTKey = pBTKey, BTFirstPrinting = pBTFirstPrinting

Select allOpen in new window

i think your problem is in setting a boolean to a numeric value.  use true and false...

SET pIsInsert = true;

Thank you for your answer.
I fixed that, but it didn't help: I still get the same error :(
Maybe other suggestions?

I just removed everything from the body of the procedure between BEGIN and END, and got the same error. So probably it has something to do with parameters, but what?
Here is how I call the procedure:

String odbcConnString = WebConfigurationManager.ConnectionStrings["BookList"].ToString();
            odbcConnection = new OdbcConnection(odbcConnString);

            OdbcCommand odbcCommand = new OdbcCommand("{ CALL Import(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }", odbcConnection);
            odbcCommand.CommandType = CommandType.StoredProcedure;

            for (rowCounter = 0; rowCounter < sheetEntries.GetLength(0); rowCounter++)
            {
                odbcCommand.Parameters.Clear();

                OdbcParameter odbcParameter = odbcCommand.Parameters.Add("pEAN", OdbcType.VarChar, 13);
                odbcParameter.Value = sheetEntries[rowCounter, 0] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 0];
                odbcParameter = odbcCommand.Parameters.Add("pTitle", OdbcType.VarChar, 255);
                odbcParameter.Value = sheetEntries[rowCounter, 1] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 1];
                odbcParameter = odbcCommand.Parameters.Add("pRespParty", OdbcType.VarChar, 255);
                odbcParameter.Value = sheetEntries[rowCounter, 2] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 2];
                odbcParameter = odbcCommand.Parameters.Add("pPublisher", OdbcType.VarChar, 255);
                odbcParameter.Value = sheetEntries[rowCounter, 3] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 3];
                odbcParameter = odbcCommand.Parameters.Add("pPubDate", OdbcType.SmallDateTime);
                odbcParameter.Value = sheetEntries[rowCounter, 4] == "" ? (object)DBNull.Value : Convert.ToDateTime(sheetEntries[rowCounter, 4]);
                odbcParameter = odbcCommand.Parameters.Add("pSubject", OdbcType.VarChar, 255);
                odbcParameter.Value = sheetEntries[rowCounter, 5] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 5];
                odbcParameter = odbcCommand.Parameters.Add("pBinding", OdbcType.VarChar, 45);
                odbcParameter.Value = sheetEntries[rowCounter, 6] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 6];
                odbcParameter = odbcCommand.Parameters.Add("pPageCount", OdbcType.Int);
                odbcParameter.Value = sheetEntries[rowCounter, 7] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 7]);
                odbcParameter = odbcCommand.Parameters.Add("pUSListPrice", OdbcType.Decimal);
                odbcParameter.Value = sheetEntries[rowCounter, 8] == "" ? (object)DBNull.Value : Convert.ToDecimal(sheetEntries[rowCounter, 8]);
                odbcParameter = odbcCommand.Parameters.Add("pDemand", OdbcType.Int);
                odbcParameter.Value = sheetEntries[rowCounter, 9] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 9]);
                odbcParameter = odbcCommand.Parameters.Add("pOnOrder", OdbcType.Int);
                odbcParameter.Value = sheetEntries[rowCounter, 10] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 10]);
                odbcParameter = odbcCommand.Parameters.Add("pOnBackorder", OdbcType.Int);
                odbcParameter.Value = sheetEntries[rowCounter, 11] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 11]);
                odbcParameter = odbcCommand.Parameters.Add("pSpeedStock", OdbcType.Bit);
                if (sheetEntries[rowCounter, 12] == "N" || sheetEntries[rowCounter, 12] == "n")
                    sheetEntries[rowCounter, 12] = "False";
                else if (sheetEntries[rowCounter, 12] == "Y" || sheetEntries[rowCounter, 12] == "y")
                    sheetEntries[rowCounter, 12] = "True";
                odbcParameter.Value = sheetEntries[rowCounter, 12] == "" ? (object)DBNull.Value : Convert.ToBoolean(sheetEntries[rowCounter, 12]);
                odbcParameter = odbcCommand.Parameters.Add("pStatus", OdbcType.VarChar, 45);
                odbcParameter.Value = sheetEntries[rowCounter, 13] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 13];
                odbcParameter = odbcCommand.Parameters.Add("pBTKey", OdbcType.Int);
                odbcParameter.Value = sheetEntries[rowCounter, 14] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 14]);
                odbcParameter = odbcCommand.Parameters.Add("pBTFirstPrinting", OdbcType.Int);
                odbcParameter.Value = sheetEntries[rowCounter, 15] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 15]);

                odbcParameter = odbcCommand.Parameters.Add("pIsInsert", OdbcType.Bit);  // if 1, it is insert, if 0, it is update
                odbcCommand.Parameters["pIsInsert"].Direction = ParameterDirection.Output;

                if (odbcConnection.State == ConnectionState.Closed)
                    odbcConnection.Open();
                int result = odbcCommand.ExecuteNonQuery();

Select allOpen in new window

it is complaining about a data type mismatch, but i can't find that error anywhere.

have you checked all of the fields in the BookQuest table and compared their data types with the input data types on your procedure?  are all of the numeric BookQuest fields being given numeric values in the procedure, and all string BookQuest fields being given string type values?

if that all appears to be in order, then try running the stored procedure in mysql workbench if you have access to it.  call it using the same variable values you are using in your application.  

or if you like, and this may be the simplest thing, just post your BookQuest table definition and a sample of the data being used to make the call, and i will take a look.

Thanks, here it is:

DROP TABLE IF EXISTS `booklist`.`bookquest`;
CREATE TABLE  `booklist`.`bookquest` (
  `ID` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Identifier',
  `EAN` varchar(13) NOT NULL COMMENT 'International Article Number',
  `Title` varchar(255) NOT NULL,
  `RespParty` varchar(255) default NULL,
  `Publisher` varchar(255) default NULL,
  `PubDate` date default NULL COMMENT 'Publication Date',
  `Subject` varchar(255) default NULL,
  `Binding` varchar(45) default NULL,
  `PageCount` int(10) unsigned default NULL,
  `USListPrice` decimal(19,2) default NULL,
  `Demand` int(10) unsigned default NULL,
  `OnOrder` int(10) unsigned default NULL,
  `OnBackorder` int(10) unsigned default NULL,
  `SpeedStock` tinyint(1) default NULL,
  `Status` varchar(45) default NULL,
  `BTKey` int(10) unsigned default NULL,
  `FirstPrinting` bigint(20) unsigned default NULL,
  `BTFirstPrinting` bigint(20) unsigned default NULL,
  `Ingram` varchar(45) default NULL,
  `Licensor` varchar(50) default NULL,
  `OfferDate` date default NULL,
  `LastDate` date default NULL,
  `Comments` varchar(255) default NULL,
  `Note` varchar(255) default NULL,
  `Color` int(10) default NULL,
  `FirstAppearance` date default NULL,
  `Material` varchar(10) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=614366 DEFAULT CHARSET=latin1;

Select allOpen in new window

all i see so far is that you have SpeedStock defined in the table as a tinyint, not a boolean/bit.  so i recommend changing your code back to what it was doing before, setting the value to a 1 or a 0, and changing the stored procedure input def for that argument to read:

IN pSpeedStock TINYINT

the BOOLEAN declaration in the proc might be the problem...

No, it didn't help either. Would you suggest declaring pIsInsert as TINYINT also?
Or maybe changing SpeedStock to be boolean in the table?

Thanks.

when you say it didn't help, what did you do with the code below?  it should set the value to 1 or 0 and convert to an int form if you are trying to use SpeedStock as a tinyint in the target table.

if you would rather keep the app code as is, then i agree that changing SpeedStock to a bit data type would be the next thing i would try.  then you would need to check everything else to make sure that things are consistent: code converts to boolean; stored proc input var is boolean; table field defined as bit.

odbcParameter = odbcCommand.Parameters.Add("pSpeedStock", OdbcType.Bit); 
if (sheetEntries[rowCounter, 12] == "N" || sheetEntries[rowCounter, 12] == "n") sheetEntries [rowCounter, 12] = "False"; 
else if (sheetEntries[rowCounter, 12] == "Y" || sheetEntries[rowCounter, 12] == "y") sheetEntries [rowCounter, 12] = "True"; 
odbcParameter.Value = sheetEntries[rowCounter, 12] == "" ? (object)DBNull.Value : Convert.ToBoolean (sheetEntries[rowCounter, 12]);

Select allOpen in new window

This is what I have done:

if (sheetEntries[rowCounter, 12] == "N" || sheetEntries[rowCounter, 12] == "n")
                    sheetEntries[rowCounter, 12] = "0";
                else if (sheetEntries[rowCounter, 12] == "Y" || sheetEntries[rowCounter, 12] == "y")
                    sheetEntries[rowCounter, 12] = "1";
                odbcParameter.Value = sheetEntries[rowCounter, 12] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 12]);

Select allOpen in new window

When I am trying to change TINYINT(1) TO BOOLEAN using Query Browser, after saving it becomes again TINYINT(1). It seems to be the same thing.

i'm not sure what you mean by query browser, but, in mysql workbench (what i use - it's a gui utility that probably does similar to your query browser), when i use the gui to alter a table def... let's say there is already a column there with type tinyint... i change it to boolean and tab out... it changes it back to tinyint automatically, because boolean isn't a data type in mysql.  if i change it to bit, it sticks.

can you try changing the table to use a bit data type and see if that helps?

I will, but can you tell me this: if BOOLEAN is not a data type in MySQL, should I also make the procedure parameter of type BIT instead of TINYINT?

I made  SpeedStock BIT(1), and pSpeedStock, too... with the same result.
I don't know, should I make pIsInsert a BIT, too?

hmmm... the error is occurring in your app at this line, right?

int result = odbcCommand.ExecuteNonQuery();

Yes :(

ok just making sure :)

well, poking further, i see that your db command is being set up using this:

"{ CALL Import(?, ...

when i have done similar things in the past using vb and sql server, the thing being called - in your case Import - has to match the name of the stored procedure, which i think should be InsertBookQuest.  is there some other reason you are using Import here?

if that's ok... are you sure that all the data in the sheetentries thing (is that an excel spreadsheet?) have the values you expect?  i would put a break at the line mentioned above and check the values of the parameters in the immediate window.  make sure there's nothing unexpected going on.

then the next thing i would check is first-row and last row.  are the rows addressed by rowCounter as you expect?  what happens when you get to the end of the data rows?  is your code trying to access the next row due to a problem with your loop index?

Yes, the procedure name is my silly mistake. I forgot to update it. I just wonder why it didn't complain that it could not find the procedure. I changed it, but it didn't help.
Yes, the values seem to be fine.
The exception is thrown at the very first row... Again, I checked the values.

just noticed another thing...  your sp has 17 arguments including the output arg, and you only have 16 ?'s in your call statement.  these need to match in number.  please add another question mark and give it a go...

Thanks, added. With the same result.
I don't understand why it didn't tell anything about wrong number of arguments or even procedure name.

yeah something's fishy there.  thinking...

ok, i'm the master of seeing only one issue at a time...  lol

i *JUST NOTICED* that your return value is declared in code as an int, but the sp is returning a boolean.  so, please try changing the return type in the sp to bit, to stay consistent and all, and in your code, declare the return as boolean or whatever c# allows for.  and let me know...  

Why do you say it is declared as an int?
                odbcParameter = odbcCommand.Parameters.Add("pIsInsert", OdbcType.Bit);  // if 1, it is insert, if 0, it is update
                odbcCommand.Parameters["pIsInsert"].Direction = ParameterDirection.Output;

It's a Bit, isn't it?
And should parameters in the procedure be declared as Bit? Because I still have them Boolean.

Changed parameters to Bit, with the same result...

this line looks like it's taking the return and assigning it to an int data item:

int result = odbcCommand.ExecuteNonQuery();

and yes, i would change the db-side booleans to bits.

i've been working offline on my next suggestion, which would be modeled off something that i know works in a production environment.  it will be more work than the above, though, so follow through on the two other items first, then if needed i'll post the code i'm working on.

ok, try changing int result to somerthing like boolean result...

Oh, this return is generated automatically, its type doesn't depend on me:
"the return value is the number of rows affected by the command".

ok, then here goes the big change suggestion.  this is based on a working vb / sql server application, and modified to try to get it close for c#.  i inserted comments explaining things where needed.  give this a try and post back...

// starting at the top, for clarity...
odbcConnection = new OdbcConnection(odbcConnString); 
OdbcCommand odbcCommand = new OdbcCommand("{ CALL InsertBookQuest (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }", odbcConnection); 
odbcCommand.CommandType = CommandType.StoredProcedure;
 
// parameter setting stuff...modified here to set some local vars for use down below
// example for one var, the rest are left for you
OdbcParameter odbcParameter = odbcCommand.Parameters.Add("pEAN", OdbcType.VarChar, 13); 
lvarEAN = sheetEntries[rowCounter, 0] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 0];
// repeat above for each argument, storing values into local vars.

// resume where you open your connection ...
odbcConnection.Open(); 
// set the command's activeconnection to point to the connection you just opened
odbcCommand.ActiveConnection = odbcConnection;
// invoke the sp through the connection, supplying a list of the local vars from above
odbcConnection.InsertBookQuest lvarEAN, rest of the local vars you set above goes here;
boolean result = odbcCommand("pIsInsert");

Select allOpen in new window

Can you tell me how the output parameter should look? Because there is no value to set there, obviously.

                odbcParameter = odbcCommand.Parameters.Add("pBTFirstPrinting", OdbcType.Int);
                object BTFirstPrinting = sheetEntries[rowCounter, 15] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 15]);
                //odbcParameter.Value = sheetEntries[rowCounter, 15] == "" ? (object)DBNull.Value : Convert.ToInt32(sheetEntries[rowCounter, 15]);

                odbcParameter = odbcCommand.Parameters.Add("pIsInsert", OdbcType.Bit);  // if 1, it is insert, if 0, it is update
                odbcCommand.Parameters["pIsInsert"].Direction = ParameterDirection.Output;

Select allOpen in new window

And you cannot do that:
odbcConnection.InsertBookQuest lvarEAN...
odbcConnection doesn't know method InsertBookQuest().

I will leave now. I hope to see you tomorrow. Thank you very much for your precious help.

from three comments ago:  nothing needs to be done for the output parm.
from two comments ago:  yes, i was afraid of that.  as i mentioned, the code i sent you is modeled after working visual basic code, not c#.  also it is vb6, not even vb.net.  the trick here would be to find out how you can invoke the stored proc.  i wonder if there is something like odbcConnection.RunProcedure(InsertBookQuest lvarEAN, ...).  if i get a chance i'll muddle around and see what i find.
from last comment:  have a great one, you're welcome, and let's try to nail this tomorrow.

Good Morning,

You see, the way I used to call a procedure is normal. It works for me with a different procedure that has different number and types of parameters...

good morning,

sorry but i didn't get a chance to try to find out what's available with the odbcConnection object.  will check now.

in the meantime, do you have any alternative means of running the stored procedure while supplying the exact same values which are in the first row?  for example, in mysql workbench?

I have some results!
I removed all parameters except varchars, and ran the procedure. It passed. I added integers. It passed. I added Decimal, and it failed. I replaced decimal with double, and it passed!
But when I add my output parameter, I get the following error:
OUT or INOUT argument 17 for routine booklist.InsertBookQuest is not a variable or NEW pseudo-variable in BEFORE trigger.
Can you tell anything about it? What is wrong?
Thanks.

well a trigger is a routine which is executed automatically by the database server when a table row is inserted, updated, or deleted.  they can be "before" or "after".  triggers are defined according to the table, so what you would want to do here is find out if there is a "before update" trigger on the bookquest table.

I know what trigger is, I just don't get what it has to do with my situation. There are no triggers in the table.
Also, the error message has two parts:
OUT or INOUT argument 17 for routine booklist.InsertBookQuest is not a variable
or
NEW pseudo-variable in BEFORE trigger.
Maybe it is the first part that has something to do with me, and not the second.

I changed a bit my code to look like this:
                odbcParameter = new OdbcParameter("pIsInsert", OdbcType.Bit);
                odbcParameter.Direction = ParameterDirection.Output;
                odbcCommand.Parameters.Add(odbcParameter);

But it didn't help.

sorry i got called away and just got back.  so very sorry, but i think it was good being further from the problem, cuz this morning i was staring at a brick wall.  but now...

what i see from your error message is that you are affected by the first part, like you said.  looking at the stored procedure in your original post, you are setting a value for pIsInsert, but you are not selecting it out at the end.  so i think you need this line after the end if:

select pIsInsert;

(fingers crossed...)

Good Morning,

Alas, even fingers didn't help... the result is the same.
I actually made the program work by returning IsInsert in a result set instead of output parameter, but it is too slow.

I don't think I need to made the select your mentioned if we are not going to use result set:

well, i have done some more research.

first thing i found is that you're right!  i don't need to do a "select" to return the value of a local variable, only for other things like table data.  i never knew that, always just did it by habit.

the second thing i found is that if you use the call statement in mysql, you have to use an @ sign to indicate a reference to a variable on the caller (client) side.  so for example, the first thing below works, but the second one receives the weird two-part error message you listed above (btw, i never hit this before cuz that's another habit... when programming in the database, i ALWAYS name variables using @varname and not just varname, especially when calling stored procs):

works:
call mytest(@myvar);
select @myvar;

doesn't work:
call mytest(myvar);
select myvar;

so i was at least able to recreate your error.  but i did it in mysql workbench, not in code.  so i'm not sure what to suggest here, apart from checking your app code to see what you need to do to modify the call to the sp to supply somehow the missing @.  

i'll look around and see what i can come up with...

one further note, the reason this stumps me a little - again - is that i normally use visual basic and sql server, and returned result sets in that environment are accessible in the vb app without having to do any @-sign shenanigans.  (those shenanigans are only required when using the sql server tools.)  

unfortunately, from the fact that your app is receiving that error message, there is something that needs to be tweaked in your app that we don't know yet...

I tried to change parameter name from pIsInsert to @pIsInsert, and received this error:

@Script line: 4      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@pIsInsert BIT

Or you just add @ when you call procedure, not to the parameter name itself?

i dug around a little and there is absolutely no eveidence that the vb apps cared a lick about what was being returned by a stored proc.  the return data could be a local var, items in a table select, whatever.  and there could be none, one, or many.

i'm starting to think that there's a bug in mysql's driver.  you said before:

> You see, the way I used to call a procedure is normal. It works for me with a different procedure that has different number and types of parameters...

does that procedure have any output/return value to your app?  if it does, then never mind...  but if it doesn't, try modifying your sp and app to remove any reference to the output argument and see what happens.

Without the output parameter, everything works. As I said, I changed the code to return result set rather than output parameter, and this way it works. Just slow.

Thanks, I added an index and am testing right now.

Still long, but probably not worse than before.
Thank you very much for your help. I wish I could talk to you again in the future.