Text Field dislikes Apostrophes??

Targets macOS
22

Thank you!

So from what my limited knowledge can tell - my code SHOULD now work (hopefully).
Still trying to work out if the db.commit is still necessary when using prepared statements?

23

db.commit is only necessary if you start a transaction. In RealSQLDatabase the plugin automagically created a transaction for you, so it was necessary. The SQLite plugin that ships with Xojo doesn’t do this, so it’s now up to you to start the transaction.

As this is a self learning situation why don’t you post what you think should be your prepared statement for selecting a record?

24

Wayne - I have no idea at the moment as I have only just started to try to work out prepared statements :frowning:
I will look into it and post back when I have worked it out :slight_smile:

25

Arggggggggh !

My TextField called TitleField now successfully saves user entered apostrophes into the SQLite_TEXT database column.
It also seems to successfully write the value of my TextArea called CodeField into the SQLite_BLOB database column.

I know this because I have opened the database file and viewed the contents, and both columns have the correct data inside.
This means that both values ARE being saved to the database.

ALL GOOD SO FAR - BUT - when I try to display what is in the SQLite_BLOB column - I get a no record found error.

The really strange thing is if I do not insert an apostrophe into the SQLite_TEXT column - the SQLite_BLOB column displays correctly in the TextArea???

The 2 columns seem to be somehow swapped over?

[code] // INSERT INTO THE DATABASE IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
Dim ps As SQLitePreparedStatement = db.Prepare(“INSERT INTO snippets (Title, Code) VALUES (?, ?);”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.SQLExecute

// CHECK FOR DATABASE ERROR
If db.Error Then
  MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
  Return
  
Else
  
  // UPDATE THE LISTBOX
  MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
  
  // CLOSE THE WINDOW
  Self.Close
  
end if

End If[/code]

26

BLOB stands for Binary Large OBject. It’s handled in a special, more complex, way. You probably want TEXT fields.

27

So you know the above works - you’re back to your select statement.

Have a look at how the insert prepared statement works - we’ve replaced the entered data with bind points then bound to those points. Your select prepared statement will work the same way (clue there’s only one bind point).

ps.SQLExecute

will become

rs = ps.SQLSelect
28

Could it be something to do with the way I read the BLOB data into my TextArea?

[code] // POPULATE THE CODETEXTAREA
dim sql, selected as string
selected=Listbox1.cell(Listbox1.listindex,0)
sql=“select SRef, Title, Code from Snippets where Title=’”+selected+"’"
rs=db.SQLSelect(sql)

if rs=nil then
msgbox(“No record found.”)
self.close

else
dim s as string = DefineEncoding( rs.field(“Code”).StringValue , Encodings.UTF8 )
CodeTextArea.text=s

end if[/code]

29

Thanks Wayne and Rick.

Rick - I was advised to use BLOB because it retains carriage returns.

Wayne - Regarding the Select statement - I will look into it but I am so lost when it comes to databases that I know I will just spend days on it, and still not get any results :frowning:

I have now spent ALL day - (15 hours), trying to get this stupid apostrophe problem solved - to no avail!

I should have listened to my mum and become a Doctor :slight_smile:

30

If you are storing a not so big text, a pure UTF-8 text, I believe that your field should be better TEXT type. If you intend to use Blob, you should read here: http://documentation.xojo.com/index.php/DatabaseRecord.BlobColumn and here: http://documentation.xojo.com/index.php/SQLiteDatabase

TEXT uses UTF-8, so it SHOULD retain CRs and all weird chars of many languages. :slight_smile:

31

Rick - I changed the column type to TEXT instead of BLOB and the same problem exists.

Wayne - this is impossible - I have no idea how to formulate my select statement using a prepared statement :frowning:
Here is what I have so far (don’t laugh) !!!

[code]dim sql, selected as string
selected=Listbox1.cell(Listbox1.listindex,0)

// THE NEXT LINE OF CODE BELOW IS COMPLETELY MESSED UP :frowning:
Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM snippets WHERE (Code) = (?);”)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.Bind(1, CodeField.text)
rs=ps.SQLExecute

if rs=nil then
msgbox(“No record found.”)
self.close

else
dim s as string = DefineEncoding( rs.field(“Code”).StringValue , Encodings.UTF8 )
CodeTextArea.text=s

end if[/code]

32

Not text. :frowning:

33

rs=ps.SQLExecute(
if rs=nil then

rs should be nil. You did not executed a SQLSelect().

34

I also changed that to TEXT, but same problem.

35

You need some tea, a bit rest and pay attention for each line of code you’re doing. You are just trowing code, trying, without knowing. Know first, write after.

36

Close but no cigar.

Dim ps As SQLitePreparedStatement = db.Prepare("SELECT * FROM snippets WHERE Title = ?;") ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.Bind(0, TitleField.text) rs=ps.SQLExecute If db.Error Then MsgBox db.ErrorMessage End If

You didn’t need the () around the where field & data, and binds start at 0. I’m also sure you’re trying to find the title not the code.

Don’t forget to check for rs.EOF - to test if there is a record to use.

37

The more annoyed and frustrated I get - the less sense everything makes :frowning:
After 15 hours I am surprised I even know my own name.

I also have OCD, so I can’t even go to sleep until I crack this nut.

CODE ATTEMPT 6,547:

[code]Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM snippets WHERE Title = ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, TitleField.text)
rs=ps.SQLSelect

If db.Error Then
MsgBox db.ErrorMessage

else
dim s as string = DefineEncoding( rs.field(“Code”).StringValue , Encodings.UTF8 )
CodeTextArea.text=s

End If[/code]

38

You still need to check that you have a record - there may be no Title found in the database.

[code]else
If rs.EOF Then
MsgBox “No data found”
Else
dim s as string = DefineEncoding( rs.field(“Code”).StringValue , Encodings.UTF8 )
CodeTextArea.text=s
End If

End If[/code]

So does it work?

39

SUCCESS!!!

The code below worked - there was no TitleField in my MainWIndow, I needed to bind the text in the selected listbox row !

THANK YOU SO MUCH FOR ALL YOUR HELP AND PATIENCE - I REALLY APPRECIATE IT!
I have now also learnt about compiled statements :slight_smile:

[code] Dim selected as string
selected=Listbox1.cell(Listbox1.listindex,0)
Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM snippets WHERE Title = ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, selected)
rs=ps.SQLSelect

If db.Error Then
  MsgBox db.ErrorMessage
  
else
  If rs.EOF Then
    MsgBox "No data found"
    
  Else
    dim s as string = DefineEncoding( rs.field("Code").StringValue , Encodings.UTF8 )
    CodeTextArea.text=s
    
  End If
  
End If[/code]
40

Yes sure does. Does it work with the listbox cell in the bind?

41

Oh no - here we go again :slight_smile: :slight_smile: :slight_smile:
Do you mean like this:

[code]Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM snippets WHERE Title = ?;”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, Listbox1.cell(Listbox1.listindex,0))
rs=ps.SQLSelect

If db.Error Then
  MsgBox db.ErrorMessage
  
else
  If rs.EOF Then
    MsgBox "No data found"
    
  Else
    dim s as string = DefineEncoding( rs.field("Code").StringValue , Encodings.UTF8 )
    CodeTextArea.text=s
    
  End If
  
End If[/code]