My application has been running with a SQLite database for over 8 years. Now there is a requirement to switch it to MSSQL. I am currently implementing this in a class. I have stored the data migration from SQLite to MSSQL in a method. The new database has a different structure than the old DB.
I try to add the fields with
MSSQL_DB.AddRow(xxx,yyy)
to the new database.
But now I have a problem with the properties of the DatabaseColumn.
From the Mac with ODBCDatabase I get the message
“[Actual][SQL Server] Invalid character value for cast specification” to the third table. However, it is not clear to me which field is meant. It seems to be a BooleanValue or a DoubleValue field.
For example, the assignments look like this:
On Windows with MSSQLServerDatabase I get the message “The implicit conversion from varchar data type to varbinary(max) is not allowed. Use the CONVERT function to execute this query.” to the second table. Here the assignment
Table 1 consists only of string variables and does not cause any problems.
Table 2 consists of integer, string variables and a BLOB field. The BLOB field causes problems.
Table 3 consists of integer, string, boolean and double variables. Here, Boolean and/or Double fields make a problem.
The DoubleValue I have defined in MSSQL all with “decimal(5,2)” - actually I need mostly only “decimal(3,2)” .
Does anyone have an idea how I get the values into BlobValue, DoubleValue and BooleanValue fields?
Try integervalue for boolean in mssql and set it to 1 or 0
Perhaps the conversion is done when writing to the mssql blob with “dr_MSSQL.Column(“DocArea”).StringValue =”
Or you write the insert by hand and use the mssql CONVERT()
Another solution would be to use a tool like Navicat. They have both SQLite and MSSQL capabilities and at least one of the editions has a comprehensive data migration tool.
It looks like you could use their premium product (which would cover MSSQL and SQLite and migration) for a month for $35.
Don’t have much time these days. That’s why I can’t continue working on the program right away.
@Marius Dieter Noetzel
I will try it with the integer value 0 or 1.
At first I had no idea how to proceed. But I will first disable all DoubleValue and then try the BooleanValue. Then for the next try I will enable the DoubleValue again.
BlobValue doesn’t seem to be a problem because MSSQLServerDatabase is deprecated since 2023r2. With ODBCDatabase the transfer works.
If I can’t do it with AddRow, I’ll have to try INSERT and CONVERT.
@Greg O
Since I have structural changes in the new database, which I have already taken into account in my migration routine I want to leave it at my routine.
I have changed the boolean fields to tinyint. It works.
Since I am no longer using MSSQLServerDatabase, but only ODBCDatabase, I don’t seem to have any problems with BLOB fields either.
After migrating the BLOB fields from SQLite to MSSQL Server, they looked good in the database editor. Now that I am testing the fields, they no longer do.
The result: If I have images in the BLOB fields, they are no longer displayed in the canvas.
Example 1:
If the BLOB field contains an image, nothing is displayed.
Example 2:
I save RTF documents from the Formatted Text Control in one of these BLOB fields. Such a document consists of text, text plus image or just an image - it depends on the user. If the document has only text or only an image, nothing is displayed. If the document has a text followed by an image, only the text is displayed, the image is no longer displayed (sometimes an alphanumeric jumble is displayed instead of the image). If the image is in the middle of the text, I get a conversion error from the FTC.
Originally I thought that if I use dr_mssql.Column("DocArea").BlobValue = dr_sqlite.Column("DocArea").BlobValue
and transfer the value with the final DB_mssql.AddRow(DB_Table_Customer,dr_mssql)
to the database, then I migrate the data perfectly.
The version with the SQLite DB still works without errors. The version with the MSSQL server does not show me any images in the canvas. The SELECT and the writing of new data into the database is the same statement for SQLite and MSSQL Server.
picture should be stored as data in binary field.
picture class have ToData and FromData.
to display it into canvas you convert data to picture.
or may use .PictureValue instead of .BlobValue
Hi Markus,
I save the images in the SQLite-DB as BLOB with .BlobValue . It works.
I save the images in MSSQL-DB as varbinary(max) with .BlobValue.
All images that my app receives via TCP/IP, drag & drop or from the clipboard are immediately written to a MemoryBlock (.ToData). When saving in the DB and when reading from the DB, MemoryBlock (.FromData) is used.
My app keeps all images in a buffer as a MemoryBlock.
I have just checked the buffer contents in the debugger:
If the image comes from SQLite, the memory block has a size of 231478.
If the image comes from MSSQL, the memory block has a size of 233500.
Somehow something seems to be going wrong in the MSSQL DB when saving/reading. The image/memory block is getting bigger.
Now I am looking for the cause
if you use picture .ToData you create a new “file”. ToData(format As Formats, jpegQuality As Integer = Picture.QualityDefault) As MemoryBlock
whereby .FromData load a file and create a picture object to use in xojo.
I try to transfer binary data from SQLite to MSSQL. I always thought that if I used .BlobValue in conjunction with DB.AddRow then it would be the right way.
To convert the data to MSSQL means build a picture with .FromData and store the picture in the DatabaseRow as .PictureValue? If that’s the case, then I still have a lot of work ahead of me.
Blobs should never “convert” anything, they are raw bytes. Maybe Xojo is doing it?
For example, it’s a known fact that if we store raw bytes into a Xojo Constant, Xojo messes its content interpreting it as some kind of text and changing things as &u0a to &u0d for example.
Xojo blob API may be doing something wrong like that too.
I use MBS plugin to convert pictures to a JPEG string and then store in a MySQL BLOB field and back to a picture .
// Open and convert picture to JPEG String
Var mediumres_strng As String
Var medrespic As Picture
medres = New FolderItem(medrespath,FolderItem.PathModes.Native)
medrespic = medres.OpenAsPicture
mediumres_strng = PictureToJPEGStringMBS(medrespic,100) // no compression quality set to 100
// now store JPEG picture as string in the DB Blob field
// Retrieve JPEG string from DB: (DBPictureData As String)
Var pic As Picture
pic = JPEGStringToPictureMBS(DBPictureData)
If you are successfully retrieving the picture from SQLLite you may want to consider storing the picture as a JPEG string where you can set the image quality/compression
In case of fail, open an Issue Report with the sample code.
In case of success, you need more research looking for where things are getting broken.
One additional test is based on larger content. Make a 50Kb of this repeating sequence, store, retrieve and inspect. Some bugs are triggered on size. I do remember one affecting streams after like 10k, for example.
