Good morning, folks!
At new at Clarion and i’m trying to do an integration with the PostgreSQL, but i’m having a lot of problems.
Is there any place where I can find a tutorial to follow to learn how to use SQL sentences in Clarion? It doesn’t has to be something very complex, I just want to know how to execute the basics like “insert”, “select”, “update” and this kind of stuff and how to deal with them using Clarion.
would be great to me, because I have a lot of douts about it.
Att,
Ramon!
Hi Ramon! Have you seen in the Clarion help PROP:SQL and Sql() ?
The best approach sometimes depends on what you are trying to do though. Perhaps if you have a specific question to use as a starting point?
I had a quick look in the examples that come with Clarion but I see nothing specific to PostgreSQL and I have no familiarity with that myself so I can’t help you there.
Are you aware of the roles of Database Drivers in Clarion and using Clarion Views? Below are some quotes from the help
Database Drivers “Clarion achieves database independence with its built-in driver technology, which lets you access data from virtually any file system using the same set of Clarion language commands. Many file drivers are available and more are being added”
VIEW Declares a “virtual” file as a composite of related files.
Hopefully someone else with PostgreSQL experience will chip in here too at some point!
I’ll read this documentation e try to use it.
Thanks, @Brahn!
Ramon, Arnor has a lot of articles, including 77 on SQL. Look here: http://www.icetips.com/articleindex.php
Thanks, @rockyp. I’ll take a look at it.
Can someone here say to me why this code doesn’t works? I don’t realize why it doesn’t truncates the tables that are in the SQLs
if Clock() = L:HoraTruncate_1 or Clock() = L:HoraTruncate_2
L:Data = date(month(today()), today(), year(today()))
L:Retorno = getini('Tableau', 'truncate', '.\Hightech.INI')
if L:Retorno < L:Data
PG_cl_clifor{PROP:SQL} = 'truncate cl_clifor;'
PG_Co_Carteira{PROP:SQL} = 'truncate co_carteira;'
PG_co_nota{PROP:SQL} = 'truncate co_nota;'
PG_Co_Pedido{PROP:SQL} = 'truncate co_pedido;'
PG_Grupo{PROP:SQL} = 'truncate pg_grupo;'
PG_IT_PedNota{PROP:SQL} = 'truncate It_PedNota;'
PG_Produto{PROP:SQL} = 'truncate produto;'
PG_Representante{PROP:SQL} = 'truncate cl_clire;'
PG_Representante{PROP:SQL} = 'truncate pg_sgrupo;'
PG_TB_Cfop{PROP:SQL} = 'truncate tb_cfop;'
PG_TB_Municipios{PROP:SQL} = 'truncate tb_municipios;'
PG_tb_Paises{PROP:SQL} = 'truncate tb_paises;'
PG_TB_Regiao{PROP:SQL} = 'truncate tb_regiao;'
PG_TB_UF{PROP:SQL} = 'truncate tb_uf;'
message('Confere o banco')
Tray.ShowBalloon('Truncate realizado com sucesso!', 'PostgreSQL')
putini('Tableau', 'Truncate', L:Data, '.\Hoghtech.INI')
P_CliFor
P_CFOP
P_CO_Pedido
P_Co_Nota
P_Grupo
P_IT_PedNota
P_Municipio
P_OC_Itens
P_Paises
P_Produto
P_Regiao
P_Representante
P_SubGrupo
P_UF
P_Carteira
Tray.ShowBalloon('Processos executados, Tableau atualizado!', 'PostgreSQL')
end
endYou should probably try checking for error after calling PROP:SQL, that might tell you what is going on.
PG_cl_clifor{PROP:SQL} = 'truncate cl_clifor;'
IF ErrorCode()
Message('Oh no, an Error!||Error=' & Error() & '|FileError=' & FileError())
END
Of course put all of that into an error function so you don’t have to write the same code after each PROP:SQL!
Also, what happens if you try it without the semi-colon on the end?
I have already tried it, but is doesn’t appears any error message during the process =/
Another thing to try is trace logging. See the section Logging Driver I/O for debugging in the help file.
In particular:
On Demand Logging
For on-demand logging you can use property syntax within your program to conditionally turn various levels of logging on and off. The logging is effective for the target table and any view for which the target table is the primary table.
file{PROP:Profile}=Pathname !Turns Clarion I/O logging on
file{PROP:Profile}='' !Turns Clarion I/O logging off
PathName = file{PROP:Profile} !Queries the name of the log file
file{PROP:Log}=string !Writes the string to the log file
file{PROP:Details}=1 !Turns Record Buffer logging on
fFile{PROP:Details}=0 !Turns Record Buffer logging off
At least, now I have a new way to try it. Thanks, @brahn
New doubt folks.
I created a QUEUE with only one column and i’m trying to execute a “select” that should return a value which is equals to the type of the field in the QUEUE. The SQL runs with no problem, but I have no return.
Thats the code: QSQL:CodCliente{PROP:SQL} = ‘select cltcodigo from tblclientes where cltcodigo like ‘’’ & CAD:Codigo & ‘’’’
If I message the “QSQL:CodCliente”, it returns an empty value. It’s not a SQL error, because if I try to execute the same SQL with the same value in the end of it at the BD I have normal return.
Does anyone knows what is happening over hever?
Thx.
Is this the label of your QUEUE?
PROP:SQL will only work with a FILE. So to get your data into a QUEUE you would need to do something like this:
MyFile{PROP:SQL} = 'select cltcodigo from tblclientes where cltcodigo like ''' & CAD:Codigo & '''
LOOP
Next(MyFile)
IF ErrorCode()
BREAK
END
QSQL:CodCliente.SomeField = MyFile.SomeField
Add(QSQL:CodCliente)
END
QSQL:CodCliente is the label! I’ll try your solution, @brahn
Look at this code:
tblclientes{PROP:SQL} = 'select cltcodigo from tblclientes where cltcodigo like ' & CAD:Codigo & '''
message(WLECLI:cltcodigo) <<<<<<<<<<<<<< THIS MESSAGE RETURNS EMPTY
loop
next(tblclientes)
if errorcode()
if clip(CAD:FisicaJuridica) = 'J'
do some magic over here
if error()
do some magic over here
end
else
do some magic here
if error()
do some magic here too
end
end
end
break
end
Can you see the problem? The message in the beginning of the code return an empty string.
The PROP:SQL executes the SQL statement against the server, it is not until you issue the Next(tblclientes) that values are moved to the table record buffer. Also, don’t forget to check for errors e.g.
tblclientes{PROP:SQL} = 'select cltcodigo from tblclientes where cltcodigo like ' & CAD:Codigo & '''
IF ErrorCode()
Message('An Error: ' & Choose(Error()=FileSystemErr, FileError(), Error()))
END
I wanna thank each an everyone of you, because it is workiiiiiiiiiiing! 
Hello folks!
New problem over here. Now that the SQL stuff are working, my browses aren’t anymore. Everytime that I do an insertion, update or delete the records on the browse duplicates. The most interesting thing is that it only happens on the view, because on the DB it is absolutely normal.
Does anyone ever had this kind of problem?
It normally means the key you are using in the browse is not unique. You can add additional sort items (in the template extension) which will help with uniqueness if you don’t want to use an different key.
I’m using an unique key… x.x
The problems with the browses are solved, it really was the key.
Now i’m trying something different. Does anyone here has ever worked with views executing SQL Statements?
I declared a dummy table and on the Local Data - Other Declarations I create the view and projected the fields. When I try to make the view execute a SQL Statement, it returns an error that says: “S1000 - No query has been executed with that handle”
@brahn, help me! hahahahaha