Hello.
I have installed Oracle Express and successfully managed to comunicate it with a Xojo desktop app.
I can create tables and all with simple SQL commands written in the body of the app, like this:
[code]
Dim master As New OracleDatabase
master.DatabaseName = “localhost:1521/XE”
master.UserName = UserName
master.Password = Password
master.Debug = 1
If master.Connect Then
master.SQLExecute(“CREATE TABLE tbl_Ajuste (ID INTEGER NOT NULL, Data Date,”_
+" Fornecedor INTEGER, Estoque INTEGER, Razao VARCHAR2(1000), contabilizado CHAR CHECK(contabilizado in (0,1)), “_
+” usuario VARCHAR2(100), CONSTRAINT tbl_Ajuste_PK PRIMARY KEY (ID) ENABLE)")
master.SQLExecute(“CREATE SEQUENCE seq_Ajuste MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10”)
End If[/code]
When I try to read SQL commands from a TextInputStream and execute them, I get errors.
This is the code I’m using to read and process the SQL instructions stored in a file:
Dim sqlfile As FolderItem
Dim s_linha As String
#if DebugBuild then
sqlfile = GetFolderItem("").Parent.Child("Temp\\dados_geo_oracle.sql")
#else
sqlfile = GetFolderItem("").Child("Temp\\dados_geo_oracle.sql")
#endif
if sqlfile.Exists then
tis = TextInputStream.Open(sqlfile)
tis.Encoding = Encodings.UTF8
While Not tis.EOF
s_linha = tis.ReadLine(Encodings.UTF8)
master.SQLExecute(s_linha)
Wend
end ifWhen the very first SQL line is processed, Oracle returns:
ErrorCode = 2000
ErrorMessage = ORA-02185 - a token other than WORK follows COMMITAfter all the other lines the messages are:
ErrorCode = 2000
ErrorMessage = ORA-00911 - invalid characterI was careful enough to remove all portuguese accentuation marks from my test. The SQL file looks like this:
INSERT INTO tbl_Paises (ID, Nome, Sigla) SELECT Seq_Paises.NextVal, 'Brasil', 'BR' FROM dual WHERE NOT EXISTS (SELECT Nome FROM tbl_Paises WHERE Nome = 'Brasil');
INSERT INTO tbl_Estados (ID, Nome, Sigla) SELECT Seq_Estados.NextVal, 'Acre','AC' FROM dual WHERE NOT EXISTS (SELECT Nome FROM tbl_Estados WHERE Nome = 'Acre');
INSERT INTO tbl_Estados (ID, Nome, Sigla) SELECT Seq_Estados.NextVal, 'Alagoas','AL' FROM dual WHERE NOT EXISTS (SELECT Nome FROM tbl_Estados WHERE Nome = 'Alagoas');Any ideas what is going wrong?
I could get similar code to work with SQLite, Postgres, MySQL and SQL Server. Oracle is proving to be tougher than the others.
All help is appreciated.