hello i need solution for to create a insert/select/delete/update stored procedure in mysql and how to call them from php currently i am using xampp 1.6.6 version of software thank you

Member Avatar
Member Avatar
+0 forum 4

# MySQL Stored Routines: Another Useful Scenario # [In a past guide](http://blog.bobbyhensley.com/sql-procedures-functions-authentication-example/) I discussed MySQL stored routines within MySQL. Now I’m back with another guide on MySQL stored routines. And this time it’s a more concrete case; one that you may run into yourself in the future (or maybe you already have)! Let’s jump right on, shall we? ## The scenario… ## You’re implementing a comments system to your application. Users will be able to comment on articles you and your staff post. A concern arises: **explicit comments**. It’s something all web developers have to deal with: people being crude …

Member Avatar
Member Avatar
+2 forum 1

Hi. I am Sorry that I don't know how to ask question for my Scenario. Thats why I just used "How to update qty into table2 from table1? " This is the scenario of one of my customer Table-1 Subscription Master(News Paper) - Received Subscription CustCode Name Copies date Agent 0001 XXX 10 10.05.2015 AKL 0001 XXX 5 15.05.2015 AKL 0001 XXX 7 18.05.2015 AKL System has to instruct the Agent for delivery just like below So I insert data from Table-1 to table-2 Using below code INSERT INTO [dbo.Table-2] ( Custcode,Copies,Date,Agent) SELECT CustCode,Copies,Date,Agent FROM [dbo.table-1] Result is like below …

Member Avatar
Member Avatar
+0 forum 1

Hi I have a stored procedure with a cursor which check if a field is filled in after a certain deadline. DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `controleDeadlines`() BEGIN #declaratie variabelen die nodig zijn declare cId int; declare cDeadline text; declare cUitgevoerd date; declare cOpmerking text; declare cStatus text; declare DONE int default FALSE; declare tempdate date; #declaratie cursor declare cursorDeadlinesIndicatoren cursor for select id,deadline,status,opmerking,uitgevoerd from deadlines_indicatoren d join indicatoren i on d.ind_id = i.IND_Id where i.Type_Uitgevoerd="datum"; DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE; #cursor starten OPEN cursorDeadlinesIndicatoren; read_loop: loop fetch cursorDeadlinesIndicatoren into cId,cDeadline,cStatus,cOpmerking,cUitgevoerd; IF DONE THEN leave …

Member Avatar
Member Avatar
+0 forum 2

I'm implement if else in stored procedure with and oprator . If both Conditions are true the if statement should return 1 else -1. here username and email should not be duplicate please review query below. alter proc spUserdetailss @name Nvarchar(50), @passwords Nvarchar(100), @emailadd Nvarchar(50), @Gender nvarchar(50) as begin declare @Ucount int declare @Ecount int declare @returncode int select @Ucount = count(Username), @Ecount= Count(Email) from userdata where Username=@nameUsername = @name and Email=@emailadd Email = @emailadd if @Ucount > 0 and @Ecount >0 Begin set @returncode =-1 end else begin set @returncode = 1 insert into userdata values(@name, @passwords, @emailadd, @Gender) …

Member Avatar
Member Avatar
+0 forum 5

I have this query as part of a stored procedure, and this query as it is listed here works: INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=G:\KF\GBSData.xls;', 'SELECT * FROM [Sheet1$]') SELECT * FROM [KWF_GBSData].[dbo].[tblKWF_WorkTable2] WHERE invoiceNumber IN ( SELECT invoiceNumber FROM [KWF_GBSData].[dbo].[tblKWF_WorkTable2] WHERE stockNumberShipped LIKE 'CCCOMBO%' ) I would like to change the `'SELECT * FROM [Sheet1$]'` to something a little more dynamic so that I can use a parameter for the Excel worksheet name rather than it always using Sheet1$. I would also like to use a parameter for the `WHERE stockNumberShipped LIKE 'CCCOMBO%'` so that I can pass in the …

Member Avatar
Member Avatar
+0 forum 4

** Hello i am newbiw to MYSQL .... iwant to create stored procedure in PHPMYADMIN . But when i am same procedue od MS SQL in MYSQL it is giving error . #1064 - 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 '@'companyna' nvarchar(150) begin select Company_Name from companynames where ' at line 2 this is my MS SQL Stored Procedure create proc spgetcompanynames @companyna nvarchar(150) as begin select Company_Name from companynames where Company_Name Like @companyna + '%' end this is my MYSQL …

Member Avatar
Member Avatar
+0 forum 1

0 down vote favorite I have a table: CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `score` int(11) NOT NULL, PRIMARY KEY (`id`) Now i want to create a stored procedure that will insert new column into this table with the next id (which is automaticly created), name (value which I will write), and score (which i will write). > Quoted Text Here I have tryed DELIMITER ;; CREATE PROCEDURE insertStudent (IN name varchar(100),IN score int(11)) BEGIN insert into student (name,score) values (newName,newScore); END ;; But it doesnt seem to work. I know this is …

Member Avatar
Member Avatar
+0 forum 4

Good day, Just want to ask some help, Please! I always encounter "Cannot find Table 0" when i call Stored Procedure in DB2 using VB .Net with Ole DBConnection Provider. Here is my code: Private Sub cmdPost_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPost.Click rsSearch = clsDAOMngr.getConfigParam(2) For i = 0 To rsSearch.Tables.Count Step 1 With rsSearch.Tables(0) MsgBox(.Rows(i)(0).ToString()) End With Next End Sub '------------------------------------------------------- Public Function getConfigParam(ByVal strParameter As String) As DataSet getConfigParam = Nothing Dim sProcedure As String = "" sProcedure = "EDMS.SAMPLE_SELECT2" getConfigParam = clsDBMngr.executeStoredProcedure_split(sProcedure, strParameter, 1) End Function '------------------------------------------------------- Public Function executeStoredProcedure_split(ByVal spname As String, …

Member Avatar
Member Avatar
+0 forum 1

I am trying to create stored procedure which will insert one row in the first table and identity of inserted row in other two tables. For some reason I am receiving error "Procedure or function expects parameter '@id', which was not supplied.". My code is as follows: ALTER PROCEDURE InsertProf ( @id int output, @added datetime2, @addedby nvarchar(50), @ime nvarchar(50), @prezime nvarchar(50), @fakultet int ) AS BEGIN SET NOCOUNT ON; declare @IdentityOutput table(ID int) INSERT INTO np_Profesori (Ime, Prezime) output inserted.IDProfesor into @IdentityOutput VALUES (@ime, @prezime) SET @id=(select ID from @IdentityOutput) INSERT INTO np_GdjeRadi (IDProfesor, IDFakultet) VALUES (@id, @fakultet) INSERT …

Member Avatar
Member Avatar
+0 forum 6

Hi, is it possible to combine Stored Procedure and Text Command Type in Select Statement? I have a stored procedure that compute available items, it quite a bit long select statement, now i need to call this statement in another select statement which is in text command type, how will i do it? something like `Select Barcode, ItemCode, Get_itemCount From Product` where Get_itemCount is my storedProc Im using vb.net and Sql server 2008 R2 thanks in advance

Member Avatar
Member Avatar
+0 forum 14

This SP is working well. But when recordcount in dbo.PTSGELEN is over 1000000 approx. it comes timeout error. ALTER PROCEDURE [dbo].[PTS2Tabloya] @KOLETIK nvarchar(40), @SIL tinyint , @EKLENEN int OUT AS BEGIN SET NOCOUNT ON; set @EKLENEN = 0 IF @SIL=0 AND LEN(@KOLETIK)=20 begin INSERT INTO dbo.URUNGIR (GTIN,SN,XD,BN,CLABEL,SLABEL) SELECT GTIN,SN,XD,BN,CLABEL,SLABEL FROM dbo.PTSGELEN WHERE (CLABEL = @KOLETIK OR SLABEL = @KOLETIK); set @EKLENEN = @@Rowcount ; END; IF @SIL=1 AND LEN(@KOLETIK)=20 BEGIN DELETE FROM dbo.URUNGIR WHERE (CLABEL = @KOLETIK OR SLABEL = @KOLETIK) ; set @EKLENEN = @@Rowcount ; END; END

Member Avatar
Member Avatar
+0 forum 1

Dear friends, I am using Wordpress 3.6 . I need to integrate an alert system(to send email notification) to users when a preference value (Reach price of onion 40 $/kg). Is stored procedure can do any thing with it? Please advise how it is feasible. Thanks, Anes

Member Avatar
Member Avatar
+0 forum 1

I faced to a huge problem when i coding some stored procedure. How to combine some string to sql string in stored procedure??? This is what i did upto now. @WhereCondition = ' WHERE id = 102 ' DECLARE recoredSet CURSOR FOR SELECT SUM(Amount) as AmountSum FROM sale + @WhereCondition OPEN recoredSet --After above code I wrote fetch commands and it has no any problem. In above code, @WhereCondition is assinged some value. Eg: @WhereCondition = ' WHERE id = 5 ' But the problem is plus mark (+) is not supported. If Plus mark is not there, the code …

Member Avatar
Member Avatar
+0 forum 5

I currently have $tableName="thenameofthetable"; $someid=$_GET["someidthruget"]; $result = mysql_query("SELECT name,age FROM ". $tableName. " WHERE idp=". $someid); Which looks really insecure. How can I do it using a stored procedure which I think will make it a lot more secure? Thanks

Member Avatar
Member Avatar
+0 forum 5

Hi, Please look at my code Declare @tableName varchar(100), @total int, @sql nvarchar(2000),@id varchar(4) set @tableName='details'; set @id='C'; SELECT @sql= ' select @total = count(*) from details where id='+@id exec sp_executesql @sql, N'@total int output', @total output select @total Error: Msg 207, Level 16, State 1, Line 2 Invalid column name 'C'. Please help me out.

Member Avatar
Member Avatar
+0 forum 1

Hi guys, I'm still new to .NET and your answers to my questions have been so helpful. I have another question yet again, however. This time my question has to do with sql server and handling it with code behind. I am creating a stored procedure which is supposed to check if a record exists in the table and if it does, return a value so that I may tell the user that the record already exists. If it does not exist, and insert statement is executed. Here is my stored procedure: @UserID VARCHAR(50), @PlayerID VARCHAR(50) AS BEGIN -- SET …

Member Avatar
Member Avatar
+0 forum 6

CREATE OR replace FUNCTION fn_findraise (dept_in IN VARCHAR2, salary_in IN FLOAT) RETURN NUMBER IS salaryraise NUMBER; BEGIN IF dept_in = 'MIS' THEN salaryraise := salary_in * .10; ELSIF dept_in = 'SALES' THEN salaryraise := salary_in * .15; ELSIF dept_in = 'HR' THEN salaryraise := salary_in * .20; END IF; RETURN salaryraise; END; / Now I want to call that from within a stored function; lil lost on how to pass the variables, thanks for the help!

Member Avatar
Member Avatar
+0 forum 1

HI All, I'm writing an application in Visual Basic .NET but the application will use a SQL backend. If the user runs the application and the backend database is not set up, I want the user to select a SQL Database Instance, supply an administrator user ID (sa) and Password and then the application will connect to the SQL Instance via ADO.NET and Create the Database. The app will then create a Database user for the application to use (so we don't have users using the Admin account,) and then create the Tables and Stored procedures in the database and …

Member Avatar
Member Avatar
+0 forum 2

The procedure for Insert/update.I use datagridview get and display the data. I get the error message "Procedure or function Updatedata has too many arguments specified." // this is my Procedure ALTER PROCEDURE [dbo].[Updatedata] @item char(30), @uom int,@group int,@orqty int,@avgcons int,@reorder int AS BEGIN SET NOCOUNT ON; IF EXISTS( SELECT * FROM P_Itemmaster WHERE Item=@item ) UPDATE P_Itemmaster SET Uom=@uom,PGroup=@group,Minorqty=@orqty,avgcons=@avgcons,reorder=@reorder where item=@item ELSE INSERT INTO P_Itemmaster (Item,Uom,PGroup,Minorqty,Avgcons,reorder)Values(@item,@uom,@group,@orqty,@avgcons,@reorder) END //call the procedure in the program Private Sub Updatetable() conn = New SqlConnection(frmstlogin.Constr) conn.Open() cmd = New SqlCommand(cmdstr, conn) cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "Updatedata" Dim MUom As Integer, Mgroup As Integer, Morqty …

Member Avatar
Member Avatar
+0 forum 1

Hi, I am in urgent need for converting sql procedure to postgre sql procedure.Please help me ASAP. CREATE FUNCTION sp_get_id_des (IN cellid INT, IN mcc VARCHAR(3), IN mnc VARCHAR(3), IN nwtype INT, IN mlac INT , IN mrac INT ) returns SETOF record AS' declare pscnt int ; declare cscnt int; declare cnt1 int ; declare cnt2 int; declare cscnid int ; declare pscni int; declare cscnpoolid varchar(20); declare pscnpoolid varchar(20); **DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @x2 = 1;** BEGIN drop table TEMP_CNINFO_TBL; drop table RESULT_TBL; create temporary table TEMP_CNINFO_TBL select * from FGW_DBS_CNINFO_TBL where CNINFO_MLAC=mlac and CNINFO_MRAC=mrac …

Member Avatar
+0 forum 0

Hi, Needs to construct the tables with the meta info retrived by another stored procedure I wrote, given the table id or name. There could be some flaws in the logic, not sure. The flow is - - iterate through one table which stores tbl name, id info - pass above info to sp which would return meta info to calling cursor as result set - store this result set into temp tbl so that another cursor can iterate through them, to construct "create table query" as a string - end inner cursor - execute the concatenated string query - …

Member Avatar
Member Avatar
+1 forum 13

i created a dll file using vb.net then it has a shared function that will execute a stored procedure of mysql but my code is something long and when accessing it, it has a big risk that it can produce an error if there is an incorrect string input, i am asking an advice how i can make it much better or can point me something much better than my code as my reference. here is my code first i created a function named mysqlParamDatatype wiith 1 parameter, this function will detect the mysqldatatype of the mysql sproc parameter so …

Member Avatar
Member Avatar
+0 forum 1

Ahoy, I'm having a bit of trouble executing a stored procedure using PHP's SQLSRV package thing. It's pretty much just a simple select query, with the parameters being passed through a php (it's from a property search). SqlSrv is a tough nut to crack as docuemtnation is either non existent, or there are a various different methods on how to execute a Stored Procedure. Here's the one I've gone with: ` $bedrooms = '3'; $pageno = '1'; $branchID = '1844'; $postcode = 'BL1'; $mint = '0'; $maxt = '1000000000'; $params = array( array($bedrooms, SQLSRV_PARAM_IN), array($mint, SQLSRV_PARAM_IN), array($maxt, SQLSRV_PARAM_IN), array($postcode, SQLSRV_PARAM_IN), …

Member Avatar
Member Avatar
+0 forum 6

Hello, I am getting the contents on my website from database using SQL Stored Procedure (C#). Now, I want to change the output look with XML / XSLT. How can I do this? Something like this http://forums.asp.net/t/1230093.aspx/1 but am not sure how to go about it, am new to C# .NET My sample code. SqlConnection objConn = new SqlConnection(); objConn.ConnectionString = "server=172.16.8.444\\SQL2005;database=********;Integrated Security=false;user=********;pwd=*********;"; objConn.Open(); SqlCommand objCmd = new SqlCommand("getContent", objConn); objCmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter result = new SqlDataAdapter(objCmd); DataSet selectResults = new DataSet(); result.Fill(selectResults); // get dataset objCmd.ExecuteReader(); foreach (DataRow row in selectResults.Tables[0].Rows) { Literal1.Text = (selectResults.Tables[0].Rows[0]["title"].ToString() + "<br />" …

Member Avatar
Member Avatar
+0 forum 2

Hi, Is there a way i can pass variable column names to a stored proc? Here's what i want to do: i have lots of tables and for each of those i want to select out a few columns. e.g from table titles i want to pick out title_name and cost then maybe i want to go to publishers and pick out mean cost and country. I have the table names and the column names in a perl script. Can i pass these as variables to a generic stored proc? here's what i tried: [CODE] create procedure test_proc @file VARCHAR(256) …

Member Avatar
Member Avatar
+0 forum 1

So I'm trying to figure out the best way (if possible) to combine these two stored procedures. Basically these are the same, the only difference is one is looking at a 'Spot' table, the other is looking at the 'Retail' table. However, in both cases the column count is the same and most of the column names are even the same (with the exception of RetailID vs SpotID and RName vs SName). Any help would be much appreciated! [CODE]/* Procedure structure for procedure `EventsNear1` */ /*!50003 DROP PROCEDURE IF EXISTS `EventsNear1` */; DELIMITER $$ /*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `EventsNear1`(Lat1 float,Long1 …

Member Avatar
+0 forum 0

I recently moved a script I created for Google Maps over from my development site and I changed the information in the mysqli line and now I'm recieving an error mesage -- any idea what's going on here? Here's the error message: [CODE]Warning: mysqli::mysqli() [mysqli.mysqli]: (28000/1045): Access denied for user 'Username'@'localhost' (using password: YES) in /home/cigardig/public_html/XXXX.php on line 259[/CODE] Here's the relevant code: [CODE]$mysqli = new mysqli( "localhost", "[I]Username[/I]", "[I]Password[/I]", "[I]DB Name[/I]" );[/CODE] I also don't know if this has anything to do with it but this script is using stored functions which grant as follows: [CODE]/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION …

Member Avatar
Member Avatar
+0 forum 10

Hi folks, this is my first post in danniweb, please excuse me if I am in the wrong place or the thread title lacks self explanation. I am very disoriented in the field since im new in MS SQL. Before going into the problem, I'm going to give some context: As an assigment, I'm required to create a small simple DB in MS SQL 2008 for a store. It is supposed to have 3 entities: Customers, Products and Sales. This is the design I came out with: *Customer (customerId (PK), name, lastname, birthdate, birthplace) *Customer-phone# (Customer-phone#Id(PK), customerId(FK), phone-number, phone#Id(FK)) *Phone# …

Member Avatar
Member Avatar
+0 forum 5

Hi everyone, Can anyone tell my why this does not work? I created a stored procedure that uses a variable / input value as table name, but it does not do what it's supposed to do. Here is the code: [CODE] delimiter // DROP PROCEDURE IF EXISTS userpages.respondtoconnectionrequest// CREATE DEFINER = 'sithembiso'@'localhost' PROCEDURE userpages.respondtoconnectionrequest(IN tname VARCHAR(23),IN response INT,IN ouserid INT(14),IN fuserid INT(14),IN fftable VARCHAR(23)) BEGIN SET @u = CONCAT('UPDATE `userpages`.`',tname,'` SET `status`=',response,' WHERE `',tname,'`.`userID`=',fuserid); PREPARE stmtu FROM @u; EXECUTE stmtu; DEALLOCATE PREPARE stmtu; IF (response = 1) THEN SET @i = CONCAT('UPDATE `userpages`.`',fftable,'` SET `status`=',response,' WHERE `',fftable,'`.`userID`=',ouserid); PREPARE stmti FROM …

Member Avatar
+0 forum 0

The End.