|
Good evening from Brazil. I granted select and update to the user but I still can't run the procedure below. What are the other privileges I need to grant to him?
delimiter
drop procedure if exists sp_optimistic_locking
create procedure sp_optimistic_locking
(
p_from_account int,
p_to_account int,
p_transfer_amount numeric(10,2),
p_status int,
p_message varchar(100)
)
sql security invoker
modifies sql data
begin
declare l_account_balance1 numeric(10,2);
declare l_account_balance2 numeric(10,2);
declare l_account_timestamp1 timestamp;
declare l_account_timestamp2 timestamp;
select balance, account_timestamp into l_account_balance1, l_account_timestamp1
from account_balance where account_id = p_from_account;
if l_account_balance1 >= p_transfer_amount then
start transaction;
-- call long_running_validation(p_from_account);
select balance, account_timestamp into l_account_balance2, l_account_timestamp2
from account_balance where account_id = p_from_account for update;
if (l_account_balance1 <> l_account_balance2 or
l_account_timestamp1 <> l_account_timestamp2) then
rollback;
set p_status = -1;
set p_message = concat('Transaction canceled due to previous update of account ', p_from_account);
else
update account_balance set balance = balance - p_transfer_amount where
account_id = p_from_account;
update account_balance set balance = balance + p_transfer_amount where
account_id = p_to_account;
commit;
set p_status = 0;
set p_message = 'transaction ok';
end if;
else
set p_status = -1;
set p_message = 'Transaction canceled due to account balance less than desired withdrawal';
end if;
select 'Transaction code', p_status;
select 'Transaction message', p_message;
end;
|
|
|
|
|
Good morning from Brazil. I'm running MySQL with root privileges and with the read and write permissions set but I still can't load a file with load_file:
delimiter //
drop procedure if exists sp_loadfile //
create procedure sp_loadfile
(
p_file_name varchar(128)
)
begin
declare my_text text;
set my_text = load_file(p_file_name);
select concat(p_file_name, ' has ', length(my_text), ' bytes.') as 'Output';
end;
//
-rw-rw-rw- 1 eduardo eduardo 5 Set 1 11:04 test.txt
mysql> call sp_loadfile('/var/www/mysql/PL_loadfile/test.txt');
+--------+
| Output |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
|
|
|
|
|
Hi,
I have SQL 2008 express R2 installed.
I have visual studio express 2010 and 2012 also installed.
Windows 8.
I'm trying to create a new table but I get this strange message:
Quote: This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)
I googled the net but didn't find a solution to the problem. (replacing the dsref80.dll of 2012 with the one from 2010)
Did anybody encountered this issue and solved it?
|
|
|
|
|
I have never used database tools in Visual Studio and I see no reason to.
I prefer to just write the SQL statement or use the designer in SSMS.
|
|
|
|
|
I'm using the 'SQL Server Management Studio' (=SSMS).
he error that I get appears while I'm trying to create a table there.
|
|
|
|
|
Ah, then I don't know. Just write some SQL.
"Would you like some help with that?" -- Clippy
|
|
|
|
|
Thanks, but I'm looking to solve this problem so I could have access to the designer.
|
|
|
|
|
Right. I've been using the Express versions too and had no trouble with the designers. SQL Server 2012 Express has been causing other trouble though.
Did you try a repair install?
|
|
|
|
|
OK. So I removed the current installation but right now
I can't install the management studio because of compatibility issues. (Windows 8)
Besides going back to Windows 7, Any advice??
|
|
|
|
|
Nope, I'm on Win 7.
You could possibly use a virtual Win 7 system. Hopefully someone else will have something more useful.
|
|
|
|
|
So I managed installing Management studio but now I'm trying to create a database and I get:
Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)
It looks like I need to update something but I don't know what...
|
|
|
|
|
That could be an access problem. I forgot exactly when I've seen that.
|
|
|
|
|
|
Excellent! I may need that too.
|
|
|
|
|
edit
I found the source of the problem. I forgot to close the cursor;
Hello. My second SP is printing the last row twice and I don't know what to do about it. The second one is calling the first.
delimiter //
drop procedure if exists sp_overdue_sales //
create procedure sp_overdue_sales()
begin
drop temporary table if exists overdue_sales_tmp;
create temporary table overdue_sales_tmp as select
sales_id, customer_id, sale_date, quantity, sale_value
from sales where sale_status = 'A' limit 10;
end;
//
delimiter //
drop procedure if exists sp_issue_invoice //
create procedure sp_issue_invoice()
begin
declare var_sales_id int;
declare last_sale int default 0;
declare sales_csr cursor for
select sales_id from overdue_sales_tmp;
declare continue handler for not found set last_sale = 1;
open sales_csr;
sales_repeat: repeat
if last_sale then
leave sales_repeat;
end if;
fetch sales_csr into var_sales_id;
select var_sales_id;
until last_sale end repeat;
end;
//
| var_sales_id |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
+--------------+
| var_sales_id |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
+-------+------+-----------------------------------------------------+
modified 29-Aug-13 11:38am.
|
|
|
|
|
-------------------
1 | 2 | 3 |
-------------------
1|2|3|1|2|3|1|2|3|
-------------------
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
How TO Get data This Format Using Select Query
First is Main Header Like 1 2 3
Or Second Is Sub Header Like 1|2|3 is Sub Column For Each Main Header
|
|
|
|
|
Kailashkumar Raghvani wrote: First is Main Header Like 1 2 3 Or Second Is Sub Header Like 1|2|3 is Sub Column For Each Main Header
I'm not sure, I understand you correctly! You want columns with subcolumns? That's not possible. SQL is not for spreadsheets it's for databases. A column is a column and each row of a query has the same number of columbs. Of course you can combine different queries but you can't group columns to be subcolumns.
|
|
|
|
|
I would have a look a Pivot Querys[^]
that would be the closest thing that you could do.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
it would be a pivot query or custom solution based in table schema
|
|
|
|
|
im facing problem while making connection with mongol db to nodejs project please can any one help ???
|
|
|
|
|
Please update your question with what the actual problem is, and what you have tried.
We can't read your mind nor your monitor.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
please help me, i have been having sleepless nights on this code
Select max(convert(int,SubString(Employee_ID,PATINDEX('%[0-9]%',Employee_ID),Len(Employee_ID)))) from Employee
this code returns a value of 20, when i used sql server 2012
from a column(varchar()) that contains the following;
emp001
emp005
emp020
emp018
but now, in sql server compact 4.0 column(nvarchar()), the following errors are thrown;
Error Code: 80040E14
Message : Data conversion failed. [ OLE DB status value (if known) = 2 ]
Minor Err.: 26306
Source : SQL Server Compact ADO.NET Data Provider
Num. Par. : 2
i am using vb in vs 2012
please what am i not understanding?
thanks a lot
|
|
|
|
|
Reduce the select statement to its base components, something like 'select max(employee_id) from Employee). Then add in each clause one at a time until it breaks.
Understandable, 'select max(employee_id) from Employee' is not what you want, but it may help find the issue.
Tim
|
|
|
|
|
i found my way out by creating a new column, ID as a int data type,
i then used
Select Max(ID) from Employee
to get the maximum value.
finally i just send a string value of Emp"&ID to Employee_ID Column
Thanks for your Reply
|
|
|
|
|
I have found my way out
|
|
|
|