|
Hi
To the best of my knowledge SQLite supports auto-incrementing even for a table with a
single PRIMARY KEY column where no AUTOINCREMENT clause has been defined.
The AUTOINCREMENT clause only changes the incrementation algorithm that gets used by SQLite.
Can you describe the problem in more detail?
Liron
|
|
|
|
|
Hi,
When you reference INFORMATION_SCHEMA views you need to make them UPPER CASE. In case sensitive systems right cased reference will fail.
Apart from that cool app, ta
Greg
|
|
|
|
|
I had this problem - it's easily fixed by making all of the refs (2 I think) uppercase, make sure the full table ref is uppercase ie: INFORMATION_SCHEMA.COLUMNS
|
|
|
|
|
I've added a fix to this problem in version 1.7. Thanks for the bug report.
Liron
|
|
|
|
|
It is right was i looking for, you saved me time!
|
|
|
|
|
Error: If regional settings is Turkish , Application can not find information_scheme.
Solution: You must change to uppercase "INFORMATION_SCHEMA" text in SqlServerToSQLite.cs
Future Request : Can you add convert support for Views?
|
|
|
|
|
Hi Mustafa
I'm currently swamped with work so I'll probably get to this later next week.
I'll check Views support and if its not too much work I'll probably add it
to the next version.
Thanks for the bugfix. I'll include it in the next version.
Liron
|
|
|
|
|
When I try this it says "Could not load file or assembly 'System.Data.SQLite, Version 1.0.48.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An attempt was made to load a program with an incorrect format."
Any advice would be greatly appreciated. I'm probably missing something obvious.
|
|
|
|
|
After messing around with everything I could think of, I switched over to XP, and sure enough, worked like a charm. Great code - it was fun to look through. Thank you very much for the contribution. As for Vista... I want to like it, really... but that's for another time and place. Again, thanks!
|
|
|
|
|
Great!
Nice to know it helps. If you have any problem or question - don't hesitate to ask.
Liron (liron.levi@gmail.com)
|
|
|
|
|
I'm getting the same error, but only when I run the .exe in a release build. Running the debug release .exe, no problem. Unfortunately, I don't have the option of going to XP and the app needs to be able to run on XP, Vista & Windows 7. Any ideas of what's going on?
Thanks
BTW... studying the source code has been very instructive, thanks again!
|
|
|
|
|
Hi
I'm currently in a vacation and out of country so I don't have much of an internet access these days.
However - the utility creates a log file. If you can send it to me I may be able to help you in the next few days.
Sorry for the delays
Liron
|
|
|
|
|
I was converting the SQL Server Northwind database, here's the contents of the log. I've removed the DEBUG stuff for the sake of brevity.
ERROR 2010-09-04 12:02:49,344 (C:\SqlConverter_v1_16\DbAccess\SqlServerToSQLite.cs:69) - Failed to convert SQL Server database to SQLite database
System.BadImageFormatException: Could not load file or assembly 'System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An attempt was made to load a program with an incorrect format.
File name: 'System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139'
at DbAccess.SqlServerToSQLite.CreateSQLiteDatabase(String sqlitePath, DatabaseSchema schema, String password, SqlConversionHandler handler, FailedViewDefinitionHandler viewFailureHandler)
at DbAccess.SqlServerToSQLite.ConvertSqlServerDatabaseToSQLiteFile(String sqlConnString, String sqlitePath, String password, SqlConversionHandler handler, SqlTableSelectionHandler selectionHandler, FailedViewDefinitionHandler viewFailureHandler, Boolean createTriggers) in C:\SqlConverter_v1_16\DbAccess\SqlServerToSQLite.cs:line 111
at DbAccess.SqlServerToSQLite.<>c__DisplayClass1.<ConvertSqlServerToSQLiteDatabase>b__0(Object state) in C:\SqlConverter_v1_16\DbAccess\SqlServerToSQLite.cs:line 69
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].
BTW, I've made a minor addition to the AddSQLiteView() method. This additional information will help the user determine which views probably
need to be converted to SQLite syntax:
if (sql == null)
{
_log.Warn(string.Format("user discarded the \"{0}\" view\n\n", vs.ViewName.ToString()));
return;
}
modified on Monday, September 6, 2010 11:18 AM
|
|
|
|
|
It seems that you have a different System.Data.SQLite library installed in the GAC than the one in the project folder.
I would try to remove the System.Data.SQLite reference from the project and add another one to the GAC version (by using the .NET tab in the References dialog). If this doesn't work - remove that reference and add another one to the local System.Data.SQLite.dll (found in the project folder) using the 'Browse...' option in the References tab.
Hope this helps.
Liron
|
|
|
|
|
The first suggestion worked... thanks!
|
|
|
|
|
I've just been tasked with creating a data abstraction layer to allow our existing SQL Server code to work with SQLite.
This is exactly what I was looking for to get a 'copy' of my SQL Server database into an SQLite database.
Thanks.
|
|
|
|
|
Great
If you have any problems - send me the logs and I'll provide a fix asap.
Note: the utility does not currently handle SQL Server stored procedures or views. I plan to add support for views soon. Stored procedures are not supported in SQLite to the best of my knowledge.
Liron (liron.levi@gmail.com)
|
|
|
|
|
I'm new to SQLite so maybe these suggestions are wrong but here goes:
1) SQL Server data types aren't converted to base SQLite data types.
i.e. (bit -> bit) not to integer. There is no bit data type in SQLite correct?
2) Foreign keys from SQL Server table show up as Primary keys in SQLite table (at least that is what SQLite Mangager tells me).
Thanks,
|
|
|
|
|
Hi lushdogg
Some points that may interest you:
SQLite does not mind that you declare a field as BIT. It treats it as a general purpose field (SQLite has a very loose type-system - you can store any value in any field). It does not enforce it to be boolean though. This is up to you (which is unfortunate IMO but that's the way it is). In my code I insist that boolean fields only store 0 (for FALSE) or 1 (for TRUE) and nothing else.
I could convert any boolean field to an INTEGER field but then I would lose the ability to drag the generated table to the VS.NET designer and see it declared as a boolean field automatically.
So converting BIT fields from SQL Server to BIT fields in SQLite seems to be the optimal choice even though there is no actual BIT type in SQLite...
To the best of my knowledge - SQLite does not support foreign keys at all. I cannot do anything with that information when converting from SQL Server to SQLite (and it is not the only type of information that gets lost in the process unfortunately).
Liron
|
|
|
|
|
Thanks for the info and thanks for the app. Take care and good luck.
|
|
|
|
|
Now the utility knows how to convert from SQL Server's GETDATE() function usage in DEFAULT clause
to SQLite CURRENT_TIMESTAMP clause.
For example, in SQL Server you could write:
CREATE TABLE test
(
datetime CreateDate DEFAULT (GETDATE())
)
which will cause SQL Server to insert the current time as the default value for the [CreateDate] column.
Now - it will be translated to SQLite's equivalent:
CREATE TABLE test
(
timestamp CreateDate DEFAULT (CURRENT_TIMESTAMP)
)
Hope it helps
|
|
|
|
|
Version 1.1 works fine. The previous error has gone. Thank you for sharing this useful tool!
|
|
|
|
|
Just added support for GETDATE function which appears in DEFAULT clause of CREATE TABLE DDL.
For example:
<sql server="" db="">
CREATE TABLE xxx
(
datetime CreateDate DEFAULT (GETDATE())
)
is now translated to SQLite as
CREATE TABLE xxx
(
timestamp CreateDate DEFAULT (CURRENT_TIMESTAMP)
)
Hope it helps
|
|
|
|
|
When I tried to convert a database, the conversion failed with the following error:
...
AddSQLiteTable failed
System.Data.SQLite.SQLiteException: SQLite error
default value of column [CreateTime] is not constant
at System.Data.SQLite.SQLite3.Prepare(String strSql, SQLiteStatement previous, String& strRemain)
at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
...
Any idea about the cause? Thanks!
|
|
|
|
|
SQLite does not support using functions to provide for default values. In your specific case - I guess you've used such an SQL Server function to provide default date. Something like:
datetime [CreateTime] DEFAULT (GETDATE())
In the CREATE TABLE clause for the DB table.
Until version 3.1.0 this was totally unsupported in SQLite. If you are using SQLite version 3.1.0 or above - you may be able to use a modified version that I'll upload shortly to codeproject which uses CURRENT_TIMESTAMP as the default value whenever it detects a GETDATE function.
If you did something else - please let me know what was the original SQL Server schema so I can know for sure the cause for the problem.
Liron
(liron.levi@gmail.com)
|
|
|
|