|
Your query will return 0 rows.
|
|
|
|
|
|
Have you really thought this one through? How can c.ID (and co.CID) equal 123 and c123 simultaneously? Have you come across some new quantum extension to T-SQL that allows uncertainty in equality tests?
|
|
|
|
|
Use substring
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = Substring(co.CID,2,3)
|
|
|
|
|
Try
'c' + c.ID = co.CID
and not
c.ID = 'c' + co.CID
If C.Id is of integer type then cast it
'c' + Cast(c.ID as varchar(20))= co.CID
Try this
Declare @City table (Id int,Name Varchar(20))
Insert Into @City
Select 123,'City1' Union All Select 1,'City2' Union ALl Select 124,'City3' Union ALl Select 5,'City4'
Declare @Country table (Id int,CID Varchar(10),Name Varchar(20))
Insert Into @Country
Select 1,'c123','Country 1' Union All
Select 1,'1','Country 1' Union All
Select 1,'c5','Country 1'
SELECT c.Name, co.Name
FROM @City C
JOIN @Country co ON 'c' + Cast(c.ID as varchar(20))= co.CID
Hope this helps
Niladri Biswas
|
|
|
|
|
I have an SQLite database and I need to load the database as in-memory database. Is it any way to do this?
I am using ADO.NET Data Provider library for SQLite to do all the DB Operations.
Please help.
|
|
|
|
|
|
I already read this documentation, but did't find any solution.. 
|
|
|
|
|
|
|
I don't know. If ADO.NET does not provide backup methods, and does not support SQL backup commands, then you will need to call native code that does perform backups for you. That would require some P/Invoke stuff, whether you would isolate that in a wrapper is up to you.
|
|
|
|
|
Thanks for the input. I have written my own code to implement it.
Please find the bellow steps.
1. Import the below Native methods
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_init(IntPtr pTo, string toName, IntPtr pFrom, string fromName);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_step(int b, int n);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_finish(int backupObject);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_sleep(int interval);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_remaining(int remaining);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_pagecount(int pageCount);
2. Define the below constants
public const int SQLITE_OK = 0;
public const int SQLITE_BUSY = 5;
public const int SQLITE_LOCKED = 6;
3. Above APIs required database as IntPtr. But we have only a connection class [System.Data.SQLite.SQLiteConnection]. So we need to find out database handle from the connection object. I don't know whether it is a right way or not, anyway implement an extension method as shown below
public static IntPtr GetConnectionHandle(this SQLiteConnection connection)
{
IntPtr returnPointer = IntPtr.Zero;
FieldInfo[] fields = typeof(SQLiteConnection).GetFields(
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.DeclaredOnly);
object sqLiteBase = null;
object value = null;
if (connection.State != System.Data.ConnectionState.Open &&
connection.State != System.Data.ConnectionState.Fetching &&
connection.State != System.Data.ConnectionState.Executing)
{
return returnPointer;
}
foreach (FieldInfo info in fields)
{
if (string.Compare(info.Name, "_sql", true) == 0)
{
sqLiteBase = info.GetValue(connection);
break;
}
}
if (sqLiteBase != null)
{
fields = sqLiteBase.GetType().GetFields(
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo info in fields)
{
if (string.Compare(info.Name, "_sql", true) == 0)
{
value = info.GetValue(sqLiteBase);
break;
}
}
}
if (value != null)
{
fields = value.GetType().GetFields(
BindingFlags.Default | BindingFlags.NonPublic |
BindingFlags.Instance | BindingFlags.FlattenHierarchy);
foreach (FieldInfo info in fields)
{
if (string.Compare(info.Name, "handle", true) == 0)
{
returnPointer = (IntPtr)info.GetValue(value);
break;
}
}
}
return returnPointer;
}
4. Now implement the Load method, which will load the schema and data to the memory-db as in the documentation http://www.sqlite.org/backup.html[^]
SQLiteConnection sqliteConnection = null;
public bool Load(string databaseFile)
{
sqliteConnection = new SQLiteConnection("Data Source=:memory:");
sqliteConnection.Open();
if (!File.Exists(databaseFile))
{
return false;
}
IntPtr toHandle = sqliteConnection.GetConnectionHandle();
SQLiteConnection fromConnection = new SQLiteConnection(
string.Format("Data Source={0};Read Only=True", databaseFile));
fromConnection.Open();
IntPtr fromHandle = fromConnection.GetConnectionHandle();
int retValue = sqlite3_backup_init(toHandle,
"main", fromHandle, "main");
if (retValue > 0)
{
sqlite3_backup_step(retValue, -1);
sqlite3_backup_finish(retValue);
}
fromConnection.Close();
return true;
}
5. Do any query operations in the sqliteConnection (memory) and finally save it to the disk as shown below.
public bool Save(string databaseFile)
{
bool isSuccess = false;
SQLiteConnection fileConnection = new SQLiteConnection(
string.Format("Data Source={0};Version=3;New=true", databaseFile));
try
{
fileConnection.Open();
IntPtr pFileTo = fileConnection.GetConnectionHandle();
IntPtr pFileFrom = sqliteConnection.GetConnectionHandle();
int pBackup = sqlite3_backup_init(pFileTo, "main",
pFileFrom, "main");
int rc = 0;
if (pBackup > 0)
{
do
{
rc = sqlite3_backup_step(pBackup, 10000);
if (SaveProgressEvent != null)
{
SaveProgressEvent(
sqlite3_backup_remaining(pBackup),
sqlite3_backup_pagecount(pBackup));
}
if (rc == SQLITE_OK ||
rc == SQLITE_BUSY ||
rc == SQLITE_LOCKED)
{
sqlite3_sleep(250);
}
}
while (rc == SQLITE_OK ||
rc == SQLITE_BUSY ||
rc == SQLITE_LOCKED);
sqlite3_backup_finish(pBackup);
}
isSuccess = true;
}
catch
{
}
finally
{
if (fileConnection.State != ConnectionState.Closed)
{
fileConnection.Close();
}
}
return isSuccess;
}
|
|
|
|
|
Prathapachandran.v wrote: and I need to load the database as in-memory database
Why?
And in terms of that is this going to be a read only database? If not then what happens when you write something to it?
And how are you going to load it when your application starts?
|
|
|
|
|
In-memory database means, all the database schema and data will reside in memory and we can use any supported SQL queries to manipulate data in the memory-db. But when the memory-db connection is closed, that data will be lost.
Normally this concept is used for very fast database operations.
we can use the below connection string to create an in-memory db in sqlite.
public const string sqliteConnectionString = "Data Source=:memory:";
visit the link http://www.sqlite.org/inmemorydb.html[^] for getting more idea about it.
|
|
|
|
|
Prathapachandran.v wrote: In-memory database means, all the database schema and data will reside in memory and we can use any supported SQL queries to manipulate data in the memory-db. But when the memory-db connection is closed, that data will be lost.
I know what the term means.
Prathapachandran.v wrote: we can use the below connection string to create an in-memory db in sqlite.
Do you know the difference between 'how' and 'why'? Because I asked why it was needed.
|
|
|
|
|
Hi,
given the following tables (a student assessment system):
Student:
StudentID
StuAssess:
StudentID, SubjectCode, AssessID, CritID, CritVal
Each student has one row in StuAssess for each subject for each assessment or target. The round/target is identified by the AssessID and CritID. Is it possible to write a query that will return each row in StuAssess for a student where there is either a 'Result' (i.e. AssessID = 10, CritID = 35) AND/OR a 'Target' (i.e. AssessID = 11, CritID = 42), something like this as a resultset:
SubjectCode, ResultCritVal, TargetCritVal
so a null 'ResultCritVal' but populated 'TargetCritVal' would indicate a target without a result, null 'Target' but populated 'Result' means result without a target, and null for both columns means no result or target.
I've resorted to using a 2-pass query and handling the two resultsets in my code, but if it's possible, I'd like to just have the one resultset.
Hope I've managed to explain the requirement.
|
|
|
|
|
|
Thanks David.
Maybe it's getting too late here, but I can't figure out how CASE would help me, given that for any given subjectcode, each student would have EITHER a result (AssessID = 10, CritID = 35) OR a target (AssessID = 11, CritID = 42) OR both OR neither. I've got it in my head that I can only accomplish this through one or more self-joins, but the only common columns I can work with are StudentID and SubjectCode - there's no concept of a left or right table here.
Or am I really suffering brain-fade (it's been a long day) 
Derek
|
|
|
|
|
SELECT DISTINCT
A.StudentID,
A.SubjectCode,
B.CritVal As ResultCritVal,
C.CritVal As TargetCritVal
FROM StuAssess A LEFT JOIN StuAssess B
ON A.StudentID=B.StudentID, A.SubjectCode=B.SubjectCode
LEFT JOIN StuAssess C
ON A.StudentID=C.StudentID, A.SubjectCode=C.SubjectCode
WHERE (B.AssessID=10 AND B.CritID=35) AND (C.AssessID=11 AND C.CritID=42)
I haven't test the code but I did something similar to that sometime back.
BTW... StuAssess ... I'd change that name.
|
|
|
|
|
How about a view that implements one criteria, called Result_view
create view result_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 10 and CritID = 35
;
Then another view that implements the other criteria, called Target_view
create view target_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 11 and CritID = 42
;
Then you query the views
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from result_view
union
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from target_view
;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I think he is looking for something in the form:
StudentID, SubjectCode, ResultCritVal, TargetCritVal
|
|
|
|
|
Very true, but he's failed to indicate how a ResultCritVal or TargetCritVal are calculated or populated. He's only shown what values for the original columns are to be. By using the views I suggested, the conditions for each criteria are provided. How those column values are 'translated' to a ResultCritVal or a TargetCritVal would still be necessary.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hi, I have created one SSIS Package and saved a copy of file as "Enrypt Sensitive Data with Password" Option and saved in file system.
After that I have import package on SSIS server under MSDB folder.
Now I have create one SQL Job and in steps select SSIS Package and I am having one Domain User Name and used PROXY account to run the job.
Now the issue is that when I keep Server Console with login of the same Domain User Name, SQL Job is running fine daily...
But when I logged off on machine then Job fails.
Failed to understand the rights issue with Domain User Name.
User is Power User group member of Machine
and given rights as below as well.
Log on as a batch job
Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment
But still facing issue when there is no session availble on server with that User.
Please help !!
Thankssssss
Thanks,
Sun Rays
To get something you must have to try once.
Rate answers if you like else reply me so can make it liked....
My Articles
|
|
|
|
|
A simple query with a left join sues to run quickly on SQL Server, but only with one parameter changed, it takes almost a minute.
The query is something like:
SELECT Table1.*, Table2.*
FROM Table1 Left JOIN Table2 ON Table1.ID=Table2.Table1ID
WHERE (Table1.Col1=38 OR Table1.Col1=-1) AND Table1.Col2=0
That produces around 280 lines of output in a second.
When we change Col1=38 to Col1=114, it produces some 320 lines of output, in almost a minute!
We looked at the execution plan, and in the second case we found an extra "Lazy Spool" not present in the first query, which was estimate to cost a percent.
When I looked at that "Lazy Spool", I found a striking difference between estimated and actual values:
Actual Number of Rows:75124744
Estimated Number of Rows: 708724
That is more than a hundred times as much as estimated!
Actual Rebinds was 1, and Actual Rewinds 105!
Can you guess what went wrong here?
|
|
|
|
|
Bernhard Hiller wrote: Can you guess what went wrong here?
Is that your question or is this a quiz?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
