Android Question Saving sound in the BLOB field

Lakhtin_V

Lakhtin_V

Active Member
Licensed User
Longtime User
In my application, I successfully save short fragments of sound recordings from a microphone to a PCM file. For convenience, I want to save the most important fragment of the recording in the SKL database using the BLOB format field. But so far I can’t do it. Maybe someone can help me, what is the error in my code.
lstFinish contains a sequence of lines from the recording buffer. I successfully save this list to a PСМ file and can play it through an audio streamer. But I can’t send part of this list to the BLOB field of the SQLight database
Save Sound to BLOB field SQLight: 
       Dim b() As Byte    
        For i=i0 To i1
            b(y)=lstFinish.Get(i)
            y=y+1
        Next
        strQuery = "UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?"
        Log(strQuery)
        Dim bc As ByteConverter
        Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(bc.HexFromBytes(b), CRC))
 
Sort by date Sort by votes
DonManfred

DonManfred

Expert
Licensed User
Longtime User
Lakhtin_V said:
Dim b() As Byte For i=i0 To i1 b(y)=lstFinish.Get(i) y=y+1 Next
Click to expand...
So only a BYTe is set in b(y)

Lakhtin_V said:
Dim bc As ByteConverter Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(bc.HexFromBytes(b), CRC))
Click to expand...
(bc.HexFromBytes(b) will convert 2 bytes into the Database.

I do not believe this can be a VALID PCM-File!
 
Upvote 0
E

emexes

Expert
Licensed User
Longtime User
This is similar, saves images as BLOB to SQLite database and reads them back again.

How to click a button to select an image and determine if the image size is greater than 600KB......

How to click a button to select an image and determine if the image size is greater than 600KB. If it is, compress it. If not, load it directly into the ImageView1 control.Here is my current code： Private Sub Button1_Click CC.Show("image/*", "Choose image") 'CC.Show("audio/*", "Choose...
www.b4x.com

Images are found by Bing and downloaded from internet.
 
Upvote 0
DonManfred

DonManfred

Expert
Licensed User
Longtime User
emexes said:
This is similar
Click to expand...
My suggestion is the TO would DEBUG first what he try to write to the Database. I´m positive the LOG can be surprising.

Based on the Informations provided (nearly nothing) it is really hard to give any congrete advice. All we can do is to fish...
 
Upvote 0
Lakhtin_V

Lakhtin_V

Active Member
Licensed User
Longtime User
DonManfred said:
So only a BYTe is set in b(y)


(bc.HexFromBytes(b) will convert 2 bytes into the Database.

I do not believe this can be a VALID PCM-File!
Click to expand...
In my code there is a variable y, it increases and therefore the size of the array b - Buffer, gradually fills up and increases. I changed the code according to your recommendations, tried to make it more informative.
Sound save to BLOB: 
        Dim b() As Byte, bc As ByteConverter
        y=0
        Log("CRC  " & CRC & "    lstFinish.size " & lstFinish.size & "    i0 " & i0 & "    i1 " & i1)
        For i=i0 To i1
            Log(bc.HexFromBytes(lstFinish.Get(i)))
            b(y)=lstFinish.Get(i)
            y=y+1
            Log(bc.HexFromBytes(b))
        Next
        strQuery = "UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?"
        Log(strQuery)
        Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(bc.HexFromBytes(b), CRC))
But an error appears
Save to BLOB: 
CRC  778E0A    lstFinish.size 944    i0 33    i1 1
UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?
java.lang.RuntimeException: android.database.sqlite.SQLiteException: no such column: Sound (code 1 SQLITE_ERROR): , while compiling: UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?
    at anywheresoftware.b4a.keywords.Common$14.run(Common.java:1750)
    at android.os.Handler.handleCallback(Handler.java:883)
    at android.os.Handler.dispatchMessage(Handler.java:100)
    at android.os.Looper.loop(Looper.java:214)
    at android.app.ActivityThread.main(ActivityThread.java:7388)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:980)
Caused by: android.database.sqlite.SQLiteException: no such column: Sound (code 1 SQLITE_ERROR): , while compiling: UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:986)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:593)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:33)
    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1121)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery2(SQL.java:85)
    at sound.kz.starter._sqlsavesound(starter.java:519)
    at sound.kz.sample$ResumableSub_btnSave_Click.resume(sample.java:685)
    at anywheresoftware.b4a.keywords.Common$14.run(Common.java:1748)
 
Upvote 0
josejad

josejad

Expert
Licensed User
Longtime User
Lakhtin_V said:
no such column: Sound
Click to expand...
The error is clear, it can't find a column in your DB named 'Sound'.

Try removing the commas or try pasting your logged strQuery (Log(strQuery)) in a DB Manager to test it.
 
Upvote 0
DonManfred

DonManfred

Expert
Licensed User
Longtime User
Lakhtin_V said:
Caused by: android.database.sqlite.SQLiteException: no such column: Sound (code 1 SQLITE_ERROR): , while compiling: UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?
Click to expand...
The field seems not to exists
Lakhtin_V said:
Log(strQuery)
Click to expand...
I did not suggested to log this query!
I suggested to LOG the Output of
B4X: 
bc.HexFromBytes(b)

But based on the error the problem sems to be another.
 
Upvote 0
Lakhtin_V

Lakhtin_V

Active Member
Licensed User
Longtime User
I corrected the interval boundaries to the correct ones ( i0 and i1). Now Errors.....
BLOB errors: 
CRC  D082A0    lstFinish.size 1697    i0 126    i1 156
39FF3FFF49FF62FF78FF98FFC9FFF3FF130037005F008300AE00EE0022013C0159017E019901B001C301CF01D701D901CE01C301D201E901E701DE01E901F301F401FC010902170229023A0245025702730289029302970296028E02890290028E02820287028D027F0277027F027F027402730271026A02730289028F02850281028102750267025E02480232022102FB01C601A2018C01600127010301EF00D600C700CC00D200D400D100C500CA00EF000F010C010801170123011B011001170118010B01F900DE00C600B6009700680042002100EFFFC0FFACFF95FF70FF56FF3DFF1EFF07FFF5FED6FEB1FEA3FE9BFE86FE81FE8AFE75FE48FE32FE32FE31FE30FE


java.lang.RuntimeException: java.lang.NumberFormatException: For input string: "[B@357e4ec"
    at anywheresoftware.b4a.keywords.Common$14.run(Common.java:1750)
    at android.os.Handler.handleCallback(Handler.java:883)
    at android.os.Handler.dispatchMessage(Handler.java:100)
    at android.os.Looper.loop(Looper.java:214)
    at android.app.ActivityThread.main(ActivityThread.java:7388)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:980)
Caused by: java.lang.NumberFormatException: For input string: "[B@357e4ec"
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043)
    at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
    at java.lang.Double.parseDouble(Double.java:538)
    at anywheresoftware.b4a.BA.ObjectToNumber(BA.java:695)
    at sound.kz.starter._sqlsavesound(starter.java:507)
    at sound.kz.sample$ResumableSub_btnSave_Click.resume(sample.java:685)
    at anywheresoftware.b4a.keywords.Common$14.run(Common.java:1748)
 
Upvote 0
DonManfred

DonManfred

Expert
Licensed User
Longtime User
Lakhtin_V said:
at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043) at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110) at java.lang.Double.parseDouble(Double.java:538) at anywheresoftware.b4a.BA.ObjectToNumber(BA.java:695) at sound.kz.starter._sqlsavesound(starter.java:507)
Click to expand...
Upload a full code to get further help. I´m not interested in Fishing anylonger.

Based on the error you are trying to get an Double based on the inputstring.

I´m out now
 
Upvote 0
Lakhtin_V

Lakhtin_V

Active Member
Licensed User
Longtime User
DonManfred said:
Upload a full code to get further help. I´m not interested in Fishing anylonger.

Based on the error you are trying to get an Double based on the inputstring.

I´m out now
Click to expand...
B4X: 
Public Sub SQLsaveSound(CRC As String, i0 As Int, i1 As Int)
Dim i As Int, y As Int, l As Int, strQuery As String, DT As Long = DateTime.Now
    Nlst=lstFinish.Size
'    Nbyte=lst.Size
    Log("!!!!!!!!!!!!!  SOUND FRAGMENT lstFinish.Size  " & Nlst )
    
    If Nlst>0 Then
        Dim b() As Byte, bc As ByteConverter
        y=0
        Log("CRC  " & CRC & "    lstFinish.size " & lstFinish.size & "    i0 " & i0 & "    i1 " & i1)
        For i=i0 To i1
            Log(bc.HexFromBytes(lstFinish.Get(i)))
'            b(y)=bc.HexToBytes(lstFinish.Get(i)).ToUpperCase
'            b(y)=bc.HexFromBytes(lstFinish.Get(i))
'            b(y)=lstFinish.Get(i)
            y=y+1
'            Log(bc.HexFromBytes(b))
        Next
        strQuery = "UPDATE `ZSampl` SET `ZSampl`.`Sound`= ? WHERE `ZSampl`.`crc`= ?"
        Log(strQuery)
        Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(bc.HexFromBytes(b), CRC))
    End If

End Sub
 
Upvote 0
Lakhtin_V

Lakhtin_V

Active Member
Licensed User
Longtime User
DonManfred said:
Upload a full code to get further help. I´m not interested in Fishing anylonger.

Based on the error you are trying to get an Double based on the inputstring.

I´m out now
Click to expand...
I need time to collect the code fragments that form the LIST containing the sound. But I can repeat that the information inside the list consists of a large number of buffers 260 bytes long that are obtained when reading the PCM file.
B4X: 
CRC  1725EA    lstFinish.size 794    i0 83    i1 157

9100AC00C700D800ED0007011C01290139014501440137012A011C010201E100C800B10095007700610055004C004C0054005A005C006300770088008B009400A900B000A700AA00B400AE009F009B0097008000650058004F0042003D003E0037002C0030003B003C003F0048004C00490048004D004700310023001900FEFFDEFFC5FFB0FF96FF77FF5EFF4AFF36FF24FF15FF03FFF2FEEEFEF2FEECFEE4FEE7FEE6FEDCFED9FEDAFEC8FEA8FE97FE84FE5BFE33FE19FE00FEDCFDBFFDB3FDA1FD86FD83FD90FD87FD83FD93FD92FD87FD92FDA8FDABFD9BFD9EFDAEFDA8FD96FD95FD94FD80FD79FD89FD95FD9AFDADFDCBFDE8FD06FE33FE64FE89FEB0FEE2FE12FF
2DFF4BFF81FFAEFFC8FFE7FF08002300350046005F0079008C00A100B500BF00CB00E50003011B0138015A0175018A01A401C001CC01D001D801DD01D001C301C701C501B601AF01A80193018701880188018A018D01890185017F017C0181017F01770176016E015A014E0149013C012B01180112011101060101010E0119011E01290133012E01260123011D0113010101F000D700B4009900850067004A0037001F00F7FFD8FFC4FFACFF95FF84FF78FF66FF54FF4CFF40FF29FF1DFF20FF14FFF9FEECFEE9FEE4FEDDFED9FED6FECFFEC7FEC3FEC2FEC2FEC4FED0FEE1FEF1FE06FF17FF28FF3EFF53FF62FF66FF6EFF80FF8EFF96FF9FFFAAFFB7FFC3FFC9FFD0FF
DCFFE9FFFCFF15002D004900640078008D00A600BB00CA00D900EA00FD0013012D014B015E01670177018B019901A401AC01B601B901B401AF01AC01A801A1019D019F0199018B0184017B017301730176017101650160015D01510146013E012C010E01F600EA00D700BB00A20090007A005E004C003E0029001B00180013000B000500FBFFE9FFDAFFCFFFC3FFB2FFABFFABFF9EFF88FF7BFF72FF60FF42FF28FF14FF00FFECFEDFFED4FEC0FEA8FE9FFEA2FE9FFE97FE9EFEA8FEACFEB3FEBEFEC8FECEFED1FECFFEC3FEB3FEABFEA3FE8BFE77FE73FE63FE4AFE41FE3EFE37FE32FE3AFE47FE51FE67FE88FE9CFEA5FEBBFED1FED2FECFFED7FED4FEBDFEACFEA5FE
97FE82FE6EFE5EFE4BFE40FE41FE3EFE3AFE3EFE4DFE5FFE6FFE86FEA9FEC9FEDFFEF8FE16FF34FF4CFF62FF78FF8CFF9AFFAEFFC7FFCEFFD1FFDFFFECFFF9FF1200320049005E007D009F00BD00DA00F90010011E013201440140013801390134012401170110010701FD00FD000101FD00FC000E011E011E012401390150015B0160016E01760169015C0159014F013E01340131012B0127012E01320131013E015601610169018401A601BA01C901DC01E801E301DC01DA01D101C001B901B2019D018601790171015D014C014B01490145014E015A015D0165017501820180017D01810184017801670161015301330119010201E200BE00A10089006E0059004A00
3B002F0026001D0011000200F6FFE9FFD0FFAFFF97FF7EFF54FF2EFF14FFEFFEC0FE9DFE7BFE4DFE27FE0EFEF0FDCDFDBAFDAFFD95FD7FFD7CFD7AFD6DFD68FD73FD7DFD80FD8DFDA8FDBDFDC8FDDDFDF5FD08FE19FE22FE26FE2CFE31FE34FE35FE32FE32FE37FE39FE38FE3FFE4BFE4BFE46FE4FFE61FE68FE70FE83FE91FE8BFE85FE88FE83FE75FE68FE5BFE44FE2FFE27FE1DFE10FE0DFE16FE1AFE1AFE25FE33FE3EFE53FE71FE8FFEA8FEC5FEE6FEFFFE15FF35FF55FF67FF72FF84FF99FFA6FFB0FFC5FFDAFFE5FFF6FF0D001F002F004F00730085009E00C700E100F0000E012E0142014F015D0167016601630160015A01480130011F011401070101010501
08010F0121013A014C015E017301870195019F01B301C601CC01CE01CF01CA01BD01AD01A0018D01760169016301540142013301270120011D0113010301FA00FD0003010201F700E300CA00B600A00081005A0036001400F1FFD0FFB1FF94FF7EFF68FF56FF56FF5AFF62FF78FF8FFFA3FFBBFFD2FFE3FFECFFECFFE5FFD7FFC5FFB1FF9DFF87FF68FF50FF4AFF4DFF4DFF51FF67FF7EFF8AFF9BFFB4FFCEFFE2FFF3FFFFFF0000F8FFEFFFDFFFC2FFA9FF97FF77FF50FF38FF25FF0CFF04FF0DFF0EFF11FF25FF44FF60FF80FFAEFFD8FFEEFF060027003B003E00400049004D003F002E0026001A000E000E000D00010001001500280035004E0073009000A700CC00
F300060112012901350129011D011101FC00E000C600AF00960081007600720073007F009800B100C200DA000101290145015C0173017F017C0177016801480128010F01EC00B6007E0050001E00F2FFE2FFDBFFCAFFC0FFC2FFC8FFCAFFCEFFD8FFD9FFCDFFCFFFD9FFCBFFB1FFA5FF97FF76FF53FF38FF15FFECFEC6FE9EFE6DFE42FE25FE10FEFDFDEBFDE7FDE9FDE1FDE3FDF8FD00FEF9FDFBFDFBFDE8FDD9FDD5FDC6FDADFD99FD84FD65FD45FD33FD2EFD20FD13FD22FD37FD3CFD4CFD73FD92FDA5FDCDFD07FE2BFE3AFE56FE78FE87FE97FEBBFED0FECCFED4FEE9FEF3FEFAFE08FF1EFF2AFF2FFF4FFF7BFF93FFAEFFDCFF02001C00420077009800AF00DD00
0A01190129014C01630161016701780175015D0151014A0137012901310139013501410161017A018B01A301C101D401DD01F3010B0213021C02250220021002FE01F301E901DB01D601D801D201CE01DD01F001F301FA01150230023A024402530255024A0243023E022C0219020A02F501DA01C701BC01A70184016F016A0159014A0155015E015E016B0182018F0191019701A2019D0187017001590133010601E100B800830059003B001E000500F4FFEBFFE2FFD8FFD1FFCFFFCCFFCEFFDCFFE8FFE5FFE3FFE5FFDAFFC8FFBAFFAFFF99FF81FF76FF6EFF58FF43FF34FF28FF1BFF0EFF03FFF2FEE3FEDCFED5FECCFEC5FEBFFEB1FEA2FEA1FEA3FE98FE85FE7DFE
77FE67FE58FE52FE47FE32FE25FE24FE20FE10FE01FE02FE0CFE13FE1CFE1CFE04FEF3FD04FE1AFE26FE3AFE4FFE56FE58FE60FE70FE7CFE78FE76FE90FEBAFED1FECDFEB9FEB1FEC0FED7FEEDFEFCFE0CFF20FF32FF3BFF4BFF59FF4EFF47FF66FF8CFF94FF94FF9EFF99FF8BFF92FFA1FF9BFF94FFA5FFBCFFC6FFCFFFE4FFF4FFFCFF1500390050005C0075009400A500BA00E000FB000A011C013001420144013701360136012A0128012C0122011801190117010F0108010501F200D900D300D800D100C200B800B200A400920085007D006D005900500048003C00330029001A00130018001A0014000D00130022002B0033003D0037002B002F0036002F002000
17000F00F9FFE3FFD8FFC2FFA4FF9BFF9EFF95FF87FF7FFF77FF74FF7AFF83FF85FF79FF79FF82FF7BFF6FFF6AFF68FF60FF5AFF60FF66FF60FF57FF62FF79FF8CFFA2FFBAFFC5FFD9FF0100200027002C003F0056006000620070007A007500790085008E0092009800A900BE00D300E300EC00F60007012001340139013901370133013301320129011B0115011501170120012C013701420157017E01A301B401C401E5010702200235023E023A023A02430246023402160208020D02070200020402FF01ED01EC01FA01FF01FB0101020702FF01F001E201CE01AB017A01540139010B01CF009B006B003E0022000800E1FFC1FFB2FFA1FF93FF8FFF8CFF7EFF72FF
7BFF88FF7FFF6BFF5EFF4FFF32FF13FFFFFEE6FEBEFEA2FE8FFE6EFE4AFE2CFE08FEE1FDC7FDB7FDA6FD90FD7BFD6CFD5BFD4BFD47FD43FD33FD1FFD11FD08FDFDFCECFCD5FCBFFCB1FCA0FC84FC6FFC64FC5DFC5AFC50FC4EFC5BFC63FC6AFC86FCB5FCD9FCF0FC10FD3BFD65FD90FDBFFDE2FDF2FD05FE1DFE2EFE2EFE28FE28FE23FE1DFE26FE33FE3BFE48FE66FE8EFEAFFECDFEF7FE1EFF3FFF66FF93FFB2FFBEFFC7FFD4FFD6FFCBFFC5FFB8FFA0FF9EFFAFFFB3FFB2FFC9FFF1FF15003F007600A900D600100154018C01BC01F10125024C0266028102980290027D028102830278027902880290029302A602C202D702E302F9021D0337034503580365036C03
750378036C0354033C0326030703DF02BF029C02730263025F024D02410251026202620261026B02690255024B024E02400224021102FF01DD01B301890158011A01E400C700A5006D004300360023000A0005000400F4FFEFFF03000F00030000000E000E000000F5FFEDFFDAFFC3FFB3FF9AFF73FF4DFF2FFF12FFF2FEDCFECAFEB6FEAAFEA9FEACFEABFEAFFEC1FED4FEE0FEEFFEFCFEFEFEF9FEF6FEF5FEE6FED0FECBFECBFEB8FE98FE81FE76FE6AFE60FE66FE6AFE68FE75FE8BFE98FEA4FEBCFED3FEDBFEE6FEFBFE06FF08FF11FF17FF0BFFFEFE01FF06FFFEFEF5FEFAFE00FFFDFE06FF14FF17FF1EFF36FF55FF68FF72FF88FF9FFFAEFFBFFFD2FFD8FFD7FF
E0FFEFFFF3FFECFFE3FFDFFFD6FFC6FFBDFFBAFFB3FFB1FFB4FFB6FFBBFFC1FFCDFFE8FF0300150025003100430059006200630062005F0059004E003C002B00190009000200FAFFEDFFE8FFF3FF03000D001E003A004B005500660071006D006900740074005D00470031001300F9FFEAFFD5FFB9FFAEFFB2FFADFFAFFFC0FFD7FFF2FF0E002C00480061007D0092009A009D009D0099009400870072006800610051004D0053005400570065007D0090009C00AF00C500D000DC00EA00E500D100C900C700B60098007B0062004700290015000A00F5FFE0FFE3FFE5FFD4FFD2FFE3FFE5FFDEFFE4FFEEFFEEFFF0FFF2FFE1FFC9FFBCFFB1FF93FF6BFF51FF40FF29FF
11FF05FFFDFEECFEE9FEF6FEFDFE03FF0DFF1FFF35FF48FF54FF5FFF6FFF7DFF88FF91FF99FFA6FFB4FFC4FFD9FFEFFF030017002A004700650076008A00AF00CB00D800F000050108010C011E012801200123012E012F012D0133013401280127013B0146013D013D0149014A0149015301530149014A015301570151014B014E01500147013D013B0135012701240126011D011B012D013B013B01410158016E0175017B0189018D018D019F01B201AF01A901B001B401A60194018B017B0161014A0133011701F900DE00BE00960069003F001A00ECFFBBFF97FF77FF53FF38FF25FF0CFFF9FEF1FEE7FEDCFEDAFEE1FEE3FEE0FEE9FEF1FEECFEE4FEE4FEDBFEC5FE
UPDATE `ZSampl` SET `ZSampl`.`Sound`= ? WHERE `ZSampl`.`crc`= ?
java.lang.RuntimeException: android.database.sqlite.SQLiteException: near ".": syntax error (code 1 SQLITE_ERROR): , while compiling: UPDATE `ZSampl` SET `ZSampl`.`Sound`= ? WHERE `ZSampl`.`crc`= ?
    at anywheresoftware.b4a.keywords.Common$14.run(Common.java:1750)
    at android.os.Handler.handleCallback(Handler.java:883)
    at android.os.Handler.dispatchMessage(Handler.java:100)
    at android.os.Looper.loop(Looper.java:214)
    at android.app.ActivityThread.main(ActivityThread.java:7388)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:980)
Caused by: android.database.sqlite.SQLiteException: near ".": syntax error (code 1 SQLITE_ERROR): , while compiling: UPDATE `ZSampl` SET `ZSampl`.`Sound`= ? WHERE `ZSampl`.`crc`= ?
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:986)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:593)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:33)
 
Last edited:
Upvote 0
Jim2025

Jim2025

Member
My answer may not be useful to you because different language(vb6 near b4a), but since I accidentally saw your question and at that moment I was working on SQL Cipher on Windows and I was having the exact same problem of storing bytes for a UTF-8 string, I guessed that maybe this section would help. Instead of executing it linearly, I had to bind it so that I could record the bytes as parameters, and this is working properly for me now. Its work for me when writing/updating and when reading. You may need to follow the same algorithm.

for insert: 
    Dim nextid As Variant
    Dim chk As ssSQliteResult
    nextid = CVar(dbc.ResultString("SELECT seq FROM sqlite_sequence"))
    Dim cc As SQLiteCommand
    Set cc = dbc.Command("INSERT INTO tbl(namefamily,appcode,unlockcode) VALUES (?,?,?);")
    cc.Bind 1, ConvertToUTF8(txt(0))
    cc.Bind 2, txt(1)
    cc.Bind 3, txt(2)
   
    If cc.Step = SQLITE_ERROR Then
        dbc.ShowError
    Else
        ...
    End If


for update: 
    Dim cc As SQLiteCommand
    Set cc = dbc.Command("UPDATE tbl SET namefamily=?,appcode=?,unlockcode=? WHERE ID=?;")
    cc.Bind 1, ConvertToUTF8(txt(0))
    cc.Bind 2, txt(1)
    cc.Bind 3, txt(2)
    cc.Bind 4, ListView1.SelectedItem.Text
   
    If cc.Step = SQLITE_ERROR Then
        dbc.ShowError
    Else
        ...  
    End If


for display data: 
    Set dbc = SQLite.Connection(App.Path & "\demo.dat", "password")
    With dbc.ResultSet("SELECT * FROM tbl ORDER BY ID ASC")
        For i = 0 To .Count - 1
            text1=.Matrix(i, 0)
            text2=ConvertFromUTF8(.Matrix(i, 1))
            text3=.Matrix(i, 2)
            text4=.Matrix(i, 3)
        Next
    End With
 
Upvote 0
E

emexes

Expert
Licensed User
Longtime User
Lakhtin_V said:
But I can’t send part of this list to the BLOB field of the SQLight database
Click to expand...
B4X: 
Dim b() As Byte
For i=i0 To i1
b(y)=lstFinish.Get(i)
y=y+1
Next
strQuery = "UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?"
Log(strQuery)
Dim bc As ByteConverter
Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(bc.HexFromBytes(b), CRC))

If the SQLite database Sound column is a BLOB then the first parameter element in the array should just be the Byte Array "b", it should not be a String.

Same as with CakePictureBlob here in the working example that I pointed you to earlier:

emexes said:
B4X: 
Dim CakePictureBlob() As Byte = out.ToBytesArray

SQL1.ExecNonQuery2("INSERT INTO cakes VALUES(?, ?, ?)", Array As Object(NumCandles, CakePictureURL, CakePictureBlob))
Log("Added" & TAB & CakePictureBlob.Length & TAB & CakePictureURL)
Click to expand...

Perhaps if you call your Byte Array "SoundBlob" instead of just "b", that might make your code more self-explanatory.

Code which "explains itself" naturally and reduces the number of things we need to remember about it, is usually a good thing. 🍻

On a related note, your logged hex looks like audio data = at least that everything beforehand is probably working. We are on the home stretch!

(16-bit, mono, little endian, signed, sample rate 20-44 kHz)

1741744283769.png
 
Last edited:
Upvote 0
Lakhtin_V

Lakhtin_V

Active Member
Licensed User
Longtime User
emexes said:
B4X: 
Dim b() As Byte
For i=i0 To i1
b(y)=lstFinish.Get(i)
y=y+1
Next
strQuery = "UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?"
Log(strQuery)
Dim bc As ByteConverter
Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(bc.HexFromBytes(b), CRC))

If the SQLite database Sound column is a BLOB then the first parameter element in the array should just be the Byte Array "b", it should not be a String.

Same as with CakePictureBlob here in the working example that I pointed you to earlier:



Perhaps if you call your Byte Array "SoundBlob" instead of just "b", that might make your code more self-explanatory.

Code which "explains itself" naturally and reduces the number of things we need to remember about it, is usually a good thing. 🍻

On a related note, your logged hex looks like audio data = at least that everything beforehand is probably working. We are on the home stretch!

(16-bit, mono, little endian, signed, sample rate 20-44 kHz)

View attachment 162479
Click to expand...
I found a bug. Everything ingenious is simple. My list already contains identical byte sequences. So I tried to send it to BLOB without any processing, and it was successful.
B4X: 
strQuery = "UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?"
Log(strQuery)
Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(lstFinish, CRC))
Then I got a shortened version of the list and it also went to BLOB without errors. That is, I did not need to do any conversions. Now my task is to estimate the number of bytes contained in the BLOB field using the data provided by CURSOR in SQLight. SoundBLOB=Cursor1.GetBlob("Sound")
 
Upvote 0
E

emexes

Expert
Licensed User
Longtime User
BLOBs are Byte Arrays. I doubt that the above is working as well as its lack of errors suggest.

To do it by the book, add this function:
(which converts your list of Byte Array audio snippets to a single Byte Array audio snippet)

B4X: 
Sub ListOfByteArraysToByteArray(LBA As List) As Byte()
    Dim bb As B4XBytesBuilder    'in B4XCollections library
    bb.Initialize
   
    For I = 0 To LBA.Size - 1
        Dim BA() As Byte = LBA.Get(I)
        bb.Append(BA)
    Next
   
    Return bb.ToArray  
End Sub

and then save this Byte Array (BLOB) to the database:

B4X: 
strQuery = "UPDATE `ZSampl` SET `Sound`= ? WHERE `crc`= ?"
Log(strQuery)
Dim SoundBlob() As Byte = ListOfByteArraysToByteArray(lstFinish)
Main.sqlDB.ExecNonQuery2(strQuery, Array As Object(SoundBlob, CRC))
 
Upvote 0
You must log in or register to reply here.

Similar Threads

W
  • Question
Android Question JdbcSQL with BLOB field
Replies
8
Views
3K
Wendell Carneiro Mendes
W
Erel
  • Locked
  • Article
Android Code Snippet SaveAs - Let the user select a target folder + list of other related methods
Replies
16
Views
32K
Erel
Erel
Guenter Becker
  • Question
Android Question Imageview.bitmap 2 Blob and vice versa
Replies
5
Views
5K
Guenter Becker
Guenter Becker
W
  • Question
Android Question How send the BLOB field of remote MySQL Table to Android device?
Replies
19
Views
8K
Walter Scafati
W
Erel
  • Locked
  • Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
138
Views
159K
Erel
Erel
Top