|
|
Dear all,
We have a solution which makes use of the mirroring feature of SQL Server 2005 for high availability and automatic failover. But, because of the high cost of SQL Server this is not always an option for all our clients.
My question is, what is the best approach or architecture to implement a custom replication / mirroring feature between two SQL Server Express databases. I have read about a trigger-log approach on the master database with some service synchronizing to the slave? But how to go about when the master and slave switches roles? What about replicating structural changes also? Automatic failover is also totally something else, but not that important for now?
Any ideas, resources, etc. would be appreciated.
Kind regard!
|
|
|
|
|
I am using MS Access DB With MFC as programming platform. I have question about WHERE clause in SELECT query
"SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = 'get_cat' "
get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
i removed qoutes and tried still it doesnt work it throws exception like “Too few parameters, Expected 1” I think it might be ignoring the unquoted get_cat
Quesion is--->
Is there any method of enclosing variables in the SQL queries while using MS Access as database?
Like any syntax like ---> ' ". variable ." '
In other words:: I want to use variable in my SQL query and there must be some syntax provided by SQL or MS Access to identify such variable which is been used in the query and use the value inside that variable and not the variable itself as a string.
Plz help me out with this problem
San
San
|
|
|
|
|
i am new to sql and such stuff....
did you tried concatination?? or something like
"SELECT field FROM table WHERE field='".$var['var']."'"
hope this works!!
|
|
|
|
|
madeelch1986 wrote: did you tried concatination??
Not wise. You're setting yourself up to be attacked through a SQL Injection attack by concatinating values into a SQL String.
|
|
|
|
|
May be you are true but this is not what i needed actually
My requirement is, i am trapping some long value at runtime and i storing it into some long variable and i want to fetch the data from MS Access DB which corrosponds to this value only . So i want to use this variable in my SQL in WHERE clause for searching.
If you can help then please tell me how to do it.
Thanks
san
|
|
|
|
|
Yes thanks for you help
but i am sorry to say that it was not useful
I used to write this when i was working on PHP
i think i need to check some different way to write and use variables in my SQL query
Thanks
San
sazsas
|
|
|
|
|
santoshsan wrote: get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
That is because the database has no idea of the variables you have in your C++ applicaiton. They are separate applications, running in separate processes. The only way they communicate is through SQL. You have to tell the SQL the value you want. It cannot peer into another process and pull the value out.
It has been over 7 years since I did MFC and C++ so your best bet is to find out how to generate parameterised queries in MFC and put the value in as a parameter to the SQL query.
|
|
|
|
|
To Colin Angus Mackay
The para you wrote that is very true. One process cant predict about the variables which are generated in some other process unless there is some intermediate one like SQL to do it.
I used to write SQL queries which uses variables created in PHP. When I was working on PHP and MYSQL. It was like -->
“ SELECT field FROM table WHERE field = ‘”.$variable.”’ “
I tried to quote like this but MFC does not take the above statement as one whole string but it counts it upto-->
“ SELECT field FROM table WHERE field = ‘ ”
There must be some way that MS Access has provided to specify that the value which is been used in SELECT query is a variable. E.g. it has #date# to include date value.
I am still searching for it, if you find something helpful please help me out with this problem.
san
|
|
|
|
|
santoshsan wrote: "SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = 'get_cat' "
get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
You probably need to do something like this:
"SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = " + get_cat
Do keep in mind the SQL injection attacks warning from Colin, though.
Hope this helps.
Chandra Ram
|
|
|
|
|
I tried to create database independant using new feature of visual .net(factories)the problem happen when passing parameters for orcale and sql server the shape of this passed parms differs that will genrate error when passing parameter or i will have to write code for diffrent diffrent database cases & that will be on the contrary of the reason of factories and exisance.
thanks alot
Hassan amaar.
Hassan Amaar
|
|
|
|
|
I think you're maybe missing the point of the factory pattern - in what way do the passed parameters change?
|
|
|
|
|
Thanks alot for yuor replay,
it differs in the the way the prameter are passed for instance when passing aprameter to orcale prvider it will be like this :p + name
for Sqlprovider it will be like this :@ + name
so in this case according to what I understood I will have to create aclass for each data provider.
I will do appreciate if you told if there is better solution,and please give me an example.
Hassan amaar
Hassan Amaar
|
|
|
|
|
Well yes, this is really the idea behind the factory pattern, something like:
DalFactory fact = new DalFactory();
fact.GetDal();
then in the factory class:
GetDal()
{
IDal aDataLayerClass;
switch (source)
{
case "SqlServer":
aDataLayerClass = new SqlServerDal();
break;
case "Oracle":
aDataLayerClass = new OracleDal();
break;
etc...
}
return aDataLayerClass;
}
This means that whatever is calling your data layer, it doesn't matter to them what the datasource is in the background, as they are just dealing with a class like IDal, and you can write provider specific code for each datasource.
|
|
|
|
|
Hi,
In fact they are different :
SQLServer provider : @ + name
Oracle : p + name
OleDB : ? (without a name)
ODBC (i think) : ? (without a name)
Me too i don't know why MS decided to change parameter names with providers. It's as is it and we must deal with that.
There are many other differences :
- Data Types
- Quotes : []in sql and access , "" in Oracle,....
- And Queries are different so use only standard SQL (SQL-92 or SQL-99) to be sure that u don't need to rewrite your application when changing provider.
I think u must create a class for each provider and declare diffrences as variables that u change in each class. I am sure that's the better way.
HTH.
Hayder Marzouk
|
|
|
|
|
Thanks you for your replay
I think what you said is the only way to deal with this case
Hassan Amaar
Hassan Amaar
|
|
|
|
|
Hi Guys,
I had my database for my website in the remote webhost server.But the whole database got deleted suddenly.I could not track from where it happened.Now I am having a new webhost. I would like to give maximum protection, so that the database doesn't get deleted.
Can anybody help me with the necessary steps that has to be taken so that my database is secure.
Thanks
Jith
|
|
|
|
|
Check you application for possibility of injecting sql:
e.g. do you concatenate your sql strings together like
"select * from a where something = " + textbox1.text;
|
|
|
|
|
Hi all!
i have writing an stored procedure in sql server 2005 which are given below ...
Create procedure [dbo].[DGV_UpdateUser]
(
@uId nvarchar(100),
@uName nvarchar(300),
@uEmail nvarchar(300),
@uState nvarchar(300),
@Picture Image
)
AS
BEGIN TRAN
DECLARE @SQL_Query varchar(8000)
IF(EXISTS(SELECT * FROM Users WHERE Uid = @uId))
BEGIN
SET @SQL_Query = 'UPDATE Users SET Name = ''' + @uName + ''', Email = ''' + @uEmail + ''''
IF(@uState IS NOT NULL)
SET @SQL_Query = @SQL_Query + ', State = ' + @uState
IF(@Picture IS NOT NULL)
SET @SQL_Query = @SQL_Query + ', Picture = ' + @Picture
SET @SQL_Query = @SQL_Query + ' WHERE Uid = ''' + @uId + ''''
EXEC(@SQL_Query)
--print @SQL_Query
END
IF(@@error<>0)
ROLLBACK
ELSE
COMMIT
When i press F5 to create this procedure, i have faced following error:-
Msg 402, Level 16, State 1, Procedure DGV_UpdateUser, Line 21
The data types varchar and image are incompatible in the add operator.
----------------------------------------------------------------------
i have try to cast @Picture into Varchar but i have not successed to remove error. Can any one help me?
Thanks & Regards,
SAMir Nigam,
Software Developer,
STPL, Lucknow, India.
|
|
|
|
|
You have DECLAREd @SQL_Query as varchar. All your other variables are defined as nvarchar. Choose one and stick with it.
|
|
|
|
|
Sorry friend! error is still there.
Thanks & Regards,
SAMir Nigam,
Software Developer,
STPL, Lucknow, India.
|
|
|
|
|
So you've changed every varchar to an nvarchar or vice versa? If yes, then it musr be a different error.
|
|
|
|
|
Yes friend! same error is still there. it is because Image data type cann't be implicitly or explicitly converted to string data type. and in in exec() method , parameter should be string. actually i want solution of this problem. if u have any idea[different], then please tell me.
Thanks & Regards,
SAMir Nigam,
Software Developer,
STPL, Lucknow, India.
|
|
|
|
|
 Sorry - I misread your query.
You cannot inject an image into a string.
You might want to look at sp_execsql stored procedue - it will help you get the image in. Alternatively, use a number of UPDATE statements inside the transaction so you are not injecting values into the SQL (it also makes your code safer as it is less susceptable to a SQL Injection Attack)
|
|
|
|
|
Thanks You sir!
Thanks & Regards,
SAMir Nigam,
Software Developer,
STPL, Lucknow, India.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
