Android Question Syntax error on sql

[3394509365]

Hello everyone I have a strange problem I did my little program to manage a DB.
I do insertions and deletions of entire tables and edit table name.

Until here everything is ok but if the unanimous table I want to create starts with a number I did not create the same way if the table name eg xxx2 has a number I did not delete it.

Yet there AlterName table name is declared as a string

sub Process_Globals
SelectedTable As String Public
As Public DB2 SQL
end Sub

'This is the Query String: rename
DB2.ExecNonQuery ("ALTER TABLE" & AlterName & "RENAME To" & NewNameList)

'This is the query string: delete table
DB2.ExecNonQuery ("DROP TABLE" & AlterName) 'deletes table

'This is the log

java.lang.Exception: android.database.sqlite.SQLiteException: near "prva2": syntax error: DROP TABLE table prva2

 

[DonManfred]

I´m not really familar with SQL on Android. REALLY NOTHING. If have just tried the examples...

But i am familar with MySQL on Win (in my apps i´m using a mysql remote-db with a php-script as bridge.

My try to help is the following.

sub Process_Globals
'This is the Query String: rename
DB2.ExecNonQuery ("ALTER TABLE" & AlterName & "RENAME To" & NewNameList)

'This is the query string: delete table
DB2.ExecNonQuery ("DROP TABLE" & AlterName) 'deletes table

Assumed that you use something like

dim AlterName as string = "members"

the command to drop a table should be

DROP TABLE members

or

DROP TABLE if exists members

(i would prefer this variant to prevent an possible error when the table did not exists)

BUT with your code you would get a command like

DROP TABLEmembers

which is wrong. Your forget to put spaces at the right place.
In your Query String - assumed

dim AlterName as string = "members"
dim NewNameList as string = "customers"

and a B4A-command like

DB2.ExecNonQuery ("ALTER TABLE" & AlterName & "RENAME To" & NewNameList)

you will execute an SQL-command

ALTER TABLEmembersRENAME Tocustomers

The error you posted:

java.lang.Exception: android.database.sqlite.SQLiteException: near "prva2": syntax error: DROP TABLE table prva2

let me think that the content of the string AlterName is " table prva2" and not just "prva2" which it is supposed to be

Just my 2 cent of not knowing much on SqLite on Android

Maybe it helps ;-)

 

[3394509365]

as mentioned above instructions work for both eliminate both create and rename a table.

But if you use as a table name es. "12345" does not give me error. or with the space, "table test", does not give me error but does not delete it, rename and does not change

The name of the table I keep it in the string variable is considered even number?

how to convert numbers into string? I know better displayed and basic visual studio but the instructions are different

guarda quest' altro esempio con gli spazi.

java.lang.Exception:  android.database.sqlite.SQLiteException: near "gd": syntax error: CREATE TABLE df gd gfd (ID INTEGER Not Null)

 

[eps]

It must have something to do with Java and the way it handles numerics. It invariably gets itself into a twist when it encounters a number in a string and starts to do all sorts of odd things to it.

Can you attempt a .ToString on the string to ensure that it is of type string?

If this still doesn't resolve the issue maybe put some extra logging statements in there to check the value held in the variables and whilst they're being constructed, this may well elicit a clue as to the problem being encountered.

 

[sirjo66]

java.lang.Exception:  android.database.sqlite.SQLiteException: near "gd": syntax error: CREATE TABLE df gd gfd (ID INTEGER Not Null)

Innanzitutto guarda che il discorso degli spazi è esatto, devi usarli per far capire quale è il comando e quale il nome della tabella.
Sul comando

DROP TABLE table prva2

hai 2 volte la parola "table" e quindi (giustamente) non capisce cosa sia la parola prva2 (tra l'altro mi sa che è prova2 e non prva2)
per cui aggiungi gli spazi e diventa:

DB2.ExecNonQuery ("ALTER TABLE " & AlterName & " RENAME To " & NewNameList)

Invece su questo comando:

CREATE TABLE df gd gfd (ID INTEGER NotNull)

Il nome della tabella non può contenere spazi, per cui secondo lui la tabella si chiama "df" (subito dopo la parola TABLE), e poi si aspetta che si aprano le parentesi con la lista dei campi, ed invece lui si trova la parola "gd" che non capisce che cosa è e quindi ecco l'errore.

Sergio

 

[3394509365]

In fact, I wanted to force the string and converts it to say that the string but not how to do. You need to install some library?

 

[3394509365]

Grazie per le risposte. No la tabella ai chiama proprio tabella prva2 , ma sono tabelle di esperimenti il nome e casuale. Ma se invece vorrei chiamare una tabella xxx22 che contiene numeri non posso? Mi da Anche in questo caso errore.

 

[3394509365]

Tutto funziona se non metto spazi e numeri. Ma non è tanto logico. Secondo me non le vede come stringhe.

 

[eps]

Are you able to log the SQL you are attempting to execute? Then you can execute this in a SQL Editor. If this works then Java is doing something odd - which I suspect it is.

.ToString should force the string to be a string.

e.g.

AlterName = AlterName.ToString

 

[sirjo66]

Grazie per le risposte. No la tabella ai chiama proprio tabella prva2 , ma sono tabelle di esperimenti il nome e casuale. Ma se invece vorrei chiamare una tabella xxx22 che contiene numeri non posso? Mi da Anche in questo caso errore.

Come ti dicevo prima non è una buona norma avere i nomi delle tabelle e i nomi dei campi che contengono spazi, infatti sarebbe meglio che la tabella invece che chiamarla "tabella prva2" la chiamassi ad esempio tabella_prva2

Comunque puoi anche usare spazi dentro al nome della tabella, ma per far questo devi far capire al sistema il vero nome della tabella, e per far questo devi racchiuderla tra parentesi quadre.
Questo comando va in errore:

DROP TABLE table prva2

questo invece funziona:

DROP TABLE [table prva2]

... e quindi anche questo funziona:

CREATE TABLE [df gd gfd] (ID INTEGER NotNull)

Puoi usare anche i numeri come nomi di tabelle e/o nomi dei campi, ma il numero non può essere il primo carattere del nome

Sergio

 

[LucaMs]

First of all, why don't ask in the Italian forum?

Second, you should search on web for a basic SQL tutorial.

CREATE TABLE [df gd gfd] works because it creates a table named "df gd gfd".

Without square brackets they are interpreted as three separate "objects", as if you wanted to create 3 tables.

 

[Mahares]

Normally a SQLite table must start with a letter not a digit, but if you want to start a table name with a digit or give it a name as all digits, you must enclose the name inside brackets. Here is an example:

Dim AlterName As String = "123456"
SQL1.ExecNonQuery ("DROP TABLE [" & AlterName & "]") 'deletes table

Here is another:

Dim AlterName as String="MyTable"
Dim NewNameList as String="765543"
SQL1.ExecNonQuery ("ALTER TABLE " & AlterName & " RENAME To [" & NewNameList & "]")

 

[3394509365]

In fact, I imagined that somehow I had to use brackets [].
I'll post the solutions for deletion and renaming.

DB2.ExecNonQuery ("DROP TABLE [" & AlterName & "]") 'delete table' added to the square

DB2.ExecNonQuery ("ALTER TABLE [" & AlterName & "] To RENAME [" & NewNameList & "]")

still some problems with the creation that includes parentheses ().

I have been trying in many ways. anyone have any idea??

DB2.ExecNonQuery ("CREATE TABLE statement" ["& AlterName &"] & "(" & ["& GestioneTbl.ColNames (0) &"] & "INTEGER Not Null" & ")")

 

[Mahares]

You are not explaining well what you want, but I think this is what you want:

Dim AlterName As String = "123456"
Dim MyCol As String = "ID"
Dim txt As String
txt="CREATE TABLE IF NOT EXISTS [" &  AlterName & "] ( " & MyCol & " INTEGER Not Null)"
DB2.ExecNonQuery(txt)

It will create a table with a column called ID that is INTEGER.

 

[3394509365]

Si, esatto, infatti non so perchè ma io mettevo anche l' ID dentro le quadre.

Grazie adesso posto la mia riga per chi leggerà il forum grazie.

        DB2.ExecNonQuery("DROP TABLE IF EXISTS [" &  NomeNewList & "]")'    bobobobobobobobo  ??????
              'Query = "CREATE TABLE " & NomeNewList & "(" & GestioneTbl.ColNames(0) & " INTEGER Not Null" & ")" ' con le variabili senza quadre
              Query = "CREATE TABLE [" &  NomeNewList & "] ( " &GestioneTbl.ColNames(0) & " INTEGER Not Null)"' con le variabili senza quadre
           
          DB2.ExecNonQuery(Query)