SQLite error - comma use

ClassicExtensions
#1

I am using the Taifun SQLite extension and I created a table in SQLite where one of the columns is to insert phrases like this example:
The goal of each day should be better, not perfect, only better than yesterday.
John

Data type: Text (400 characters)

When inserting the phrase, the error appears: ERROR: 3 values for 1 columns (code 1): , while compiling: INSERT INTO favoritos (frasesfav) VALUES (‘The goal of each day should be better’,‘not perfect’,‘only better than yesterday.’);

Commas are causing this error, but they must be part of the sentence. Does anyone know how to solve this?

tks

Marcos

#2

what about

INSERT INTO favoritos (frasesfav) 
VALUES ('The goal of each day should be better, not perfect, only better than yesterday.');

Taifun

1 Like
#3

I created a DB with several phrases, all inserted manually. In Thunkable, I have Screen1 where I randomly call each sentence on a label.

1-When I call the phrase, this appears in the label with quotation marks (“phrase”). This I solved by creating an invisible label and then on the main label I put a replace all text block to remove the quotation marks. There is a button that saves the (favorite) phrase displayed in another DB table. -this table I typed the sentences manually through SQLiteStudio.

2-In Screen2 there is a label that received from the Favorites table the phrases selected by the user. - In this table I inserted the phrases through the blocks: call.Sqlite1.Execute + call.SQLiteAddons1.INSERT_INTO (Taifun Extensions and Dendrites)

Problem: When the phrase is retrieved from the DB on Screen2, if it has no commas, it is displayed correctly on the label. If the phrase has commas, it does not appear on the label, doing Do It in the Thunkable block, it shows the error shown previously.

  • The interesting thing is that the recovery of the sentences typed from the first table in Screen1 also has commas and this problem does not occur, only in the sentences recorded by Thunkable.

I hope it was clear

A hug

Marcos

#4

you might want to provide a screenshot of what exactly you are doing…

see also the documentation https://puravidaapps.com/sqlite.php

For SELECT statements, the result will be returned and you can assign it to a label or listpicker or … In case of an error, an error message will be returned. For other statements (for example CREATE TABLE, INSERT, UPDATE, DELETE, …) in case the query was successful, “done” will be returned, else an error message.

It is possible to have commas inside table values. In this case, for SELECT queries which return more than one column, the result will be quotified by double quotes.

Taifun

1 Like
#5

I understand, when the phrase is retrieved from the DB for an invisible label, it returns with quotation marks at the beginning and at the end. So I remove the quotation marks to display on another visible label. Until then, all right.

The problem appears when I store this phrase from the invisible label on another DB table and then retrieve it on another screen for a new bookmark label.

No matter if I insert directly from the invisible label or if I treat the phrase by replacing the quotation marks, the error occurs.

I am attaching the images of the blocks for your appreciation.

A hug

Imagem1.png1197×549 69.7 KB

Imagem2.png1195×549 49.1 KB

#6

sugiero remplaces la coma por otro carácter luego lo recuperas y cambias de nueva el caracter

encode

decode

#7

what happens, if you create an INSERT INTO statement directly using text blocks (without using @Andres_Cotes helper extension)?
Also remember: commas are delimiter between columns, so if your value has commas, then you will have to quotify the value using double quotes…

Taifun

1 Like
#8

es mejor que utilices una version mas reciente de la extensión ,y muestres el resultado de la consulta antes de ingresarlas al método de Execute query

#9

Taifun, I already tested with and without double quotes and did not work. When I put double quotes, the return of the DB ends up coming with 2 double quotes.

#10

Andres, I already had this model to use “replace all text” and it worked, even so I thank the tip. But regardless, I wanted to do it the way it should be, even to be able to learn better.

I will download the SQLite Tool Extension Update (Free) extension and test it.

As soon as I do the tests, I will post the result again.

Tks

#11

please provide another screenshot including DO it result of

Taifun