New topic: Blob being interpreted as VarChar
<http://forums.realsoftware.com/viewtopic.php?t=29190> Page 1 of 1 [ 3 posts ] Previous topic | Next topic Author Message Jason_Adams Post subject: Blob being interpreted as VarCharPosted: Thu Jul 23, 2009 9:05 am Joined: Fri Nov 10, 2006 4:10 pm Posts: 458 As a follow-up to the Attach Database thread, I ended up abandoning the method and going a different route: record sets and database records. It's not quite as eloquent, but it's solid and fast enough. Everything is working great except, however, one rather interesting detail. One of the columns is being interpreted as the incorrect field type. When creating the database it is declared as a "long binary" -- as it is a picture stored as a blob -- but then when I use the Database.FieldSchema(tableName As String) function, it deciphers said column as Field Type value 5 -- "Text or VarChar" -- instead of Field Type value 15 or 16 -- "Long Text (Blob" and "Long VarBinary (Blob)", respectively). Is there something I'm missing? Is "long binary" actually a text field of sorts? When I read from said record, later, I use Records.Field("picture").StringValue; and normally this works, but not when I process the information through the following code: Code:Function SaveAsDatabase() As Boolean // Note: the following is only an excerpt from the source code. // Assume non-declared variables are dimmed elsewhere. //======== Copy to New Database =======\\ Dim rsSrc, rsColumns, rsRecords As RecordSet Dim recDest As DatabaseRecord Dim sTable, sExecute, sColumn As String Dim nFieldType As Integer rsSrc = DB.SQLSelect("SELECT name, sql FROM sqlite_master WHERE type='table'") Do Until rsSrc.EOF sExecute = rsSrc.Field("sql").StringValue dbNew.SQLExecute(sExecute) sTable = rsSrc.Field("name").StringValue rsColumns = DB.FieldSchema(sTable) rsRecords = DB.SQLSelect("SELECT * FROM " + sTable) Do Until rsRecords.EOF recDest = New DatabaseRecord Do Until rsColumns.EOF sColumn = rsColumns.Field("ColumnName").StringValue nFieldType = rsColumns.Field("FieldType").IntegerValue Select Case nFieldType Case 2, 3 recDest.IntegerColumn(sColumn) = rsRecords.Field(sColumn).IntegerValue Case 4, 5, 18 recDest.Column(sColumn) = rsRecords.Field(sColumn).StringValue Case 6, 7, 13 recDest.DoubleColumn(sColumn) = rsRecords.Field(sColumn).DoubleValue Case 9, 10 recDest.DateColumn(sColumn) = rsRecords.Field(sColumn).DateValue Case 11 recDest.CurrencyColumn(sColumn) = rsRecords.Field(sColumn).CurrencyValue Case 12 recDest.BooleanColumn(sColumn) = rsRecords.Field(sColumn).BooleanValue Case 14, 15, 16 recDest.BlobColumn(sColumn) = rsRecords.Field(sColumn).NativeValue Case 17 recDest.MacPictColumn(sColumn) = rsRecords.Field(sColumn).MacPictValue Case 19 recDest.Int64Column(sColumn) = rsRecords.Field(sColumn).Int64Value End Select rsColumns.MoveNext() Loop dbNew.InsertRecord(sTable,recDest) rsRecords.MoveNext() Loop rsSrc.MoveNext() Loop rsSrc.Close() rsColumns.Close() rsRecords.Close() //==============================\\ End Function Thanks! _________________ Vista Home Premium RB Pro 2009r3 "Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton Top timhare Post subject: Re: Blob being interpreted as VarCharPosted: Thu Jul 23, 2009 11:57 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 5908 Location: Portland, OR USA Quote:When creating the database it is declared as a "long binary" -- as it is a picture stored as a blob I'm assuming this is still a REALSQLDatabase we're talking about. If so, "long binary" isn't one of the recognized data types. Try defining it as "Blob". Refer to http://sqlite.org/datatype3.html Tim Top Jason_Adams Post subject: Re: Blob being interpreted as VarCharPosted: Thu Jul 23, 2009 1:32 pm Joined: Fri Nov 10, 2006 4:10 pm Posts: 458 Thanks, Tim, that is very duly noted. Since I'm using RealSQLDatabase, then, and not the Database class, is the Field Schema function even accurate? Or will it, in fact, be more accurate and recognize the blob column for what it is? Also, this does make a bit of another problem for me. This all means that I have databases which use an improper field type for the database structure, so I need to repair those databases without losing their information. Would it cause problems if I, for example, retrieved a record set of the columns via PRAGMA, edited, and updated the field type from "long binary" to "blob"? Or is there a better method for doing this? Thanks! _________________ Vista Home Premium RB Pro 2009r3 "Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 3 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
