|
Hi Even then rows effected i'm getting -1 in select statement .
What i'm observing is that when i put SET NOCOUNT ON before sql statement even then it is returning -1. I guess it shud return 0 .
I'm having sql server 2005 . Please help.
Thanks
|
|
|
|
|
Hi .
I want to know how can i increase the session existence time in SQL server. I have a software that uses a connection to SQL server . when it remains idle for some minutes then the new session will be created to SQL server from the application side . This produces me me a big problem . Because i use the connection_Id for auditing the user . in my login table i have applicationID that refers to connection_Id in sys.dm_exec_connections .
when the software authenticates the user , It updates the connection_ID in login table and through this column i can get my user's username in my triggers .
here is the problem
when my software becomes idle , then another connection_id is created and i can not trace my user because i can not reach my user in logins table
this is my login table
CREATE TABLE [dbo].[Logins](
[LoginUser] [nvarchar](50) NOT NULL ,
[Password] [nvarchar](50) NOT NULL,
[IsDefaultPassword] [int] NULL,
[AppID] [uniqueidentifier] NULL,
CONSTRAINT [PK_Logins] PRIMARY KEY CLUSTERED
(
[LoginUser] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and this is my authenticate procedure
CREATE PROCEDURE [dbo].[Authenticate] (@username nvarchar(30) , @password nvarchar(30))
AS
declare @Authenticated int
set @Authenticated = 0
select @Authenticated = 1
from Logins l
where l.LoginUser =@username and l.Password =@password
if @Authenticated = 1
begin;
declare @AppID uniqueidentifier
select @AppID=p.connection_id from sys.dm_exec_connections p
where p.session_id = @@SPID
update Logins
set AppID = @AppID
where Logins.LoginUser = @username and Logins.Password = @password
end;
select @Authenticated ,@AppID
|
|
|
|
|
yousefshokati wrote: when my software becomes idle , then another connection_id is created
Easiest way out is to not create a new connection, but re-use the existing one. Otherwise, keep the Id around in a variable after your first connect, and pass it as a param on the idle-connection.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
yousefshokati wrote: This produces me me a big problem
Increasing session timeout so you can track users is unlikely to ever be a good idea.
As one example exactly what do you think is going to happen to your system if a 'user' goes on vacation for two weeks but that user remains logged in?
Also it appears that you are attempting to manage permissions by user rather than role. And that is going to become a problem with more than a couple of users.
|
|
|
|
|
I am using this code to save file to MySQL but when I try to open the file after saving it I get an error saying:
Word was unable to read this document. It may be corrupt.
here is the code to write:
file_name = Path.GetFileName(uploadResume.PostedFile.FileName);
file_extension = Path.GetExtension(uploadResume.PostedFile.FileName);
switch (file_extension)
{
case ".pdf": document_type = "application/pdf"; break;
case ".doc": document_type = "application/vnd.ms-word"; break;
case ".docx": document_type = "application/vnd.ms-word"; break;
case ".gif": document_type = "image/gif"; break;
case ".png": document_type = "image/png"; break;
case ".jpg": document_type = "image/jpg"; break;
case ".jpeg": document_type = "image/jpg"; break;
}
int file_size = uploadResume.PostedFile.ContentLength;
byte[] document_binary = new byte[file_size];
uploadResume.PostedFile.InputStream.Read(document_binary, 0, file_size);
and then passing it as parameters:
sql_command.Parameters.AddWithValue("param_resume_format", document_type).MySqlDbType = MySqlDbType.VarChar;
sql_command.Parameters.Add("param_resume_data", MySqlDbType.Blob, file_size).Value = document_binary;
and here is how I am retrieving it:
sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_get_resume_by_id", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("param_resume_id", MySqlDbType.Int32).Value = resume_id;
sql_reader = sql_command.ExecuteReader();
sql_reader.Read();
if (sql_reader.HasRows)
{
file_name = sql_reader["resume_id"].ToString() + sql_reader["resume_ext"].ToString();
byte[] document_binary = (byte[])sql_reader["resume_data"];
FileStream file_stream = new FileStream(@"C:\Temp\" + file_name, FileMode.Create);
file_stream.Write(document_binary, 0, document_binary.Length);
file_stream.Close();
file_stream.Dispose();
txtResume.Visible = true;
}
|
|
|
|
|
I suggest you check out the BINARY datatype[^] from MySql, as a file is not simply a TEXT that you can save.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
so i should not use blob nor LongBlob?
|
|
|
|
|
Should work too, depending on size, but a VARCHAR won't work.
--edit;
Typo, forgotten an "o".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I tried it with blob, longblob and binary but all giving same problem
|
|
|
|
|
Code & exact error-message - you know the drill
--edit;
I assume that the db-parameter has been set to a binary type, and is no longer the varchar from the first post?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
modified 21-Jul-12 15:31pm.
|
|
|
|
|
code and exact error mentioned in my first post..
Thanks in advance
|
|
|
|
|
jrahma wrote: code and exact error mentioned in my first post..
Then as you were already told the type that you are using IN THAT CODE, is wrong because it is a varchar. A varchar is not binary.
|
|
|
|
|
the VARCHAR was for the document format which is text representing the file extension.
The document itself is the param_resume_data...
|
|
|
|
|
Stating it again....
The type must be binary.
The code must reflect that.
The code in your first post does NOT reflect that.
The request was made that you post your code that has been UPDATED to use binary.
|
|
|
|
|
..and this happens for "all" files, without exception? Tried a very small picture?
If that's possible, consider the below modification;
file_stream.Write(document_binary, 0, document_binary.LongLength);
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
same problem with small pic. it's all coming to 1 byte file.
i tried changing it to your code like this:
file_stream.Write(document_binary, 0, document_binary.LongLength);
but getting this error:
The best overloaded method match for System.IO.Stream.Write(byte[], int, int) has some invalid arguments
Argument 3: cannot convert from 'long' to 'int'
|
|
|
|
|
jrahma wrote: i tried changing it to your code like this:
LongLength could be quite big; I suggest you write it in "chuncks" of int.MaxValue;
jrahma wrote: it's all coming to 1 byte file.
I hope that there's more than one byte in the database?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
the field in the database is binary datatype with 255 length
|
|
|
|
|
255 byte? Sounds a bit small. Manual is talking about a LONGBLOB [^].
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
1.
I don't recall ever having seen a Word document that would fit in 255 bytes.
I just created a Word document containing a single letter ("a"), saved it to disk, and found a file size of 29KB. That was Word 2007 BTW.
2.
The only type that is suited for storing binary data IMO is a "blob".
MySQL offers blob, and some size variants thereof. Use those. I never used "binary".
3.
Yes, saving and retrieving data to/from a database is tricky; as long as it doesn't work, it is hard to tell where the problem lies; it could be in the saving part, or in the retrieving part. And when you have several bugs at once (I'm sure you do!) fixing any one of them doesn't seem to help at all, until you get to the last one.
The good thing is, you have to solve it only once, as it would apply to any kind of data, as long as it fits a byte array model, it is all the same.
And the best thing is, millions of people have done this before, so the solution is bound to be available everywhere you look.
|
|
|
|
|
I changed to BLOB. now it's working for small files but when i try larger files it doesn't work
i tried an image with 34kb and it was fine
i tried an image with 118 kb but half of the image was black when i downloaded it
i tried an image with 1MB but it was not downloaded properly.. less than 100 kb were downloaded from the file with no error!
|
|
|
|
|
Thank you.
|
|
|
|
|
BIG thank you
But........
Still having a problem..
|
|
|
|
|
Current code & message.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Dear experts,
I need your helps now.
I setup merge replication from Server A to server B and Server C and then i want to do the transaction replication from server A to another server D but i got a problem:
--------
Publication cannot be subscribed to by Subscriber database because it contains one or more articles that have been subscribed to by the same Subscriber database at merge level.
Changed database context to (.Net SqlClient Data Provider)
-------------
how to resolve it?
Thanks for your helps.
|
|
|
|