Avatar of tantormedia
tantormedia
Flag for United States of America asked on

MySql Stored Procedure problem

I am trying to update records using MySQL stored procedure run through ODBC and ADO.Net. On execution, I get the following error:
Restricted data type attribute violation(SQL_C_NUMERIC)

I have attached my stored procedure code. Can anybody please tell what is wrong?
Thanks.


DELIMITER $$

DROP PROCEDURE IF EXISTS `InsertBookQuest` $$
CREATE PROCEDURE `InsertBookQuest`(
IN pEAN VARCHAR(13),
IN pTitle VARCHAR(255),
IN pRespParty VARCHAR(255),
IN pPublisher VARCHAR(255),
IN pPubDate DATE,
IN pSubject VARCHAR(255),
IN pBinding VARCHAR(45),
IN pPageCount INT,
IN pUSListPrice DECIMAL(19,2),
IN pDemand INT,
IN pOnOrder INT,
IN pOnBackorder INT,
IN pSpeedStock BOOLEAN,
IN pStatus VARCHAR(45),
IN pBTKey INT,
IN pBTFirstPrinting INT,
OUT pIsInsert BOOLEAN
)
BEGIN
DECLARE vCount INT;

SELECT COUNT(1) FROM BookQuest WHERE EAN = pEAN INTO vCount;

IF vCount = 0 THEN
  INSERT INTO BookQuest (EAN, Title, RespParty, Publisher, PubDate, `Subject`, Binding, PageCount, USListPrice, Demand, OnOrder, OnBackorder, SpeedStock, `Status`, BTKey, BTFirstPrinting)
  VALUES (pEAN, pTitle, pRespParty, pPublisher, pPubDate, pSubject, pBinding, pPageCount, pUSListPrice, pDemand, pOnOrder, pOnBackorder, pSpeedStock, pStatus, pBTKey, pBTFirstPrinting);
  SET pIsInsert = 1;
ELSE
  UPDATE BookQuest SET Title = pTitle, RespParty = pRespParty, Publisher = pPublisher, PubDate = pPubDate, `Subject` = pSubject, Binding = pBinding, PageCount = pPageCount, Demand = pDemand, OnOrder = pOnOrder, OnBackorder = pOnBackorder, SpeedStock = pSpeedStock, `Status` = pStatus, BTKey = pBTKey, BTFirstPrinting = pBTFirstPrinting
  WHERE EAN = pEAN;
  SET pIsInsert = 0;
END IF;

END $$

DELIMITER ;

Open in new window

.NET ProgrammingMySQL Server

Avatar of undefined
Last Comment
tantormedia

8/22/2022 - Mon
tantormedia

ASKER
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

Open in new window

ViaTom

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

SET pIsInsert = true;
tantormedia

ASKER
Thank you for your answer.
I fixed that, but it didn't help: I still get the same error :(
Maybe other suggestions?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
tantormedia

ASKER
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();

Open in new window

ViaTom

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

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

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ViaTom

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

ASKER
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.
ViaTom

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]);

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
tantormedia

ASKER
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]);

Open in new window

tantormedia

ASKER
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.
ViaTom

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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
tantormedia

ASKER
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?
tantormedia

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

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

int result = odbcCommand.ExecuteNonQuery();
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
tantormedia

ASKER
Yes :(
ViaTom

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ViaTom

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

ASKER
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.
ViaTom

yeah something's fishy there.  thinking...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ViaTom

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

ASKER
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.
tantormedia

ASKER
Changed parameters to Bit, with the same result...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ViaTom

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

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

ASKER
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".

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ViaTom

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");

Open in new window

tantormedia

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

Open in new window

tantormedia

ASKER
And you cannot do that:
odbcConnection.InsertBookQuest lvarEAN...
odbcConnection doesn't know method InsertBookQuest().
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
tantormedia

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

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

ASKER
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...
Your help has saved me hundreds of hours of internet surfing.
fblack61
ViaTom

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

ASKER
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.
ViaTom

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
tantormedia

ASKER
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.
ViaTom

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...)
tantormedia

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
tantormedia

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

ViaTom

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

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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
tantormedia

ASKER
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

tantormedia

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

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
tantormedia

ASKER
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.
ASKER CERTIFIED SOLUTION
ViaTom

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
tantormedia

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.