Help with Database to hpdf

[Bret]

I am here again asking for your help.
I'm trying to generate a report to be saved to PDF from a database using SQLite3 and HPDF, but when I run the test I'm getting the following error message:
"Attempt to perform arithmetic on field 'Rows' (a nil value)
Stack Traceback:
1: [Report -> Button_GeraPDF -> On Click] Line: 86 in main chunck "

The code is as follows:

require "hpdf"
require "common"

-------------------- FUNÇÕES --------------------
function S_Logotipo (SPage)
image = hpdf.LoadPngImageFromFile(Relatorio_pdf, "AutoPlay\\Images\\Controle de Acesso LOGO.png");
local iw = hpdf.Image_GetWidth(image);
local ih = hpdf.Image_GetHeight(image);
hpdf.Page_DrawImage(SPage, image, 40, 700, 150, 150);

end



function S_Relatorio(SPage)
hpdf.Page_SetLineWidth(SPage, 0.2);
hpdf.Page_Rectangle(SPage, 310, 605, 230, 70);
hpdf.Page_Stroke(SPage);

end



function sCabeceira(sPAGINA,sLINEA)
hpdf.Page_BeginText(sPAGINA);
font5=hpdf.GetFont(Relatorio_pdf, "Times-Bold","WinAnsiEncoding");
hpdf.Page_SetFontAndSize(sPAGINA, font5, 9);
hpdf.Page_TextOut(sPAGINA, 45,sLINEA,"NOME VISITANTE");
hpdf.Page_TextOut(sPAGINA, 100,sLINEA,"EMPRESA VISITANTE");
hpdf.Page_TextOut(sPAGINA, 200,sLINEA,"EMPREGADO VISITADO");
hpdf.Page_TextOut(sPAGINA, 300,sLINEA,"ÁREA VISITADA");
hpdf.Page_TextOut(sPAGINA, 375,sLINEA,"HORA DE ENTRADA");
hpdf.Page_TextOut(sPAGINA, 450,sLINEA,"HORA DE SAÍDA");
hpdf.Page_EndText(sPAGINA)

end



function s_Controle (sPAGINA)
hpdf.Page_BeginText(S_NOMEPAGINA);
font4=hpdf.GetFont(Factura_pdf, "Times-Bold","WinAnsiEncoding");
hpdf.Page_SetFontAndSize(S_NOMEPAGINA, font4, 12);
hpdf.Page_TextOut(S_NOMEPAGINA, 400,800,"RELATÓRIO DE VISITAS NO DIA "..vDiaRelatorio);
hpdf.Page_EndText(S_NOMEPAGINA);

end



function sPie (fPAGINA,fNUMERODEPAGINA,fTOTALPAGINAS)
hpdf.Page_BeginText(fPAGINA);
font3=hpdf.GetFont(Relatorio_pdf, "Courier","WinAnsiEncoding");
hpdf.Page_SetFontAndSize(fPAGINA, font3, 7);
hpdf.Page_TextOut(fPAGINA, 435,35,"Página "..fNUMERODEPAGINA.." de "..fTOTALPAGINAS);
hpdf.Page_EndText(fPAGINA)

end



function sEspera ()
StatusDlg.Show();
StatusDlg.SetTitle("GERANDO PÁGINAS ");
StatusDlg.SetMeterRange(1, S_NUMEROS_DE_PAGINAS);
StatusDlg.SetMeterPos(S_CONTADOR);
StatusDlg.SetStatusText("PÁGINA. . . " .. S_CONTADOR.." DE "..S_NUMEROS_DE_PAGINAS );

end


-------------------- INÍCIO --------------------

Relatorio_pdf = hpdf.New()


if Relatorio_pdf then

Opendb()
for row in db:nrows("SELECT * FROM Controle WHERE Dia LIKE '%"..vDiaRelatorio.."%' ") do
tbResults = row.Dia
--tbResults = SQLite3.QueryToTable(sDatabase, "SELECT * FROM Controle WHERE Dia LIKE '%"..vDiaRelatorio.."%' ")

s_LINHAS_POR_PAGINA = 59 ;

S_NUMEROS_DE_PAGINAS = tbResults.Rows/s_LINHAS_POR_PAGINA;

S_NUMEROS_DE_PAGINAS =Math.Floor (S_NUMEROS_DE_PAGINAS );

S_NUMEROS_DE_PAGINAS = S_NUMEROS_DE_PAGINAS + 1;

S_LINHAS_ADICIONALES = S_NUMEROS_DE_PAGINAS * 8;

S_PAGINAS_ADICIONAIS = S_LINHAS_ADICIONALES / 59;

S_PAGINAS_ADICIONAIS = Math.Floor (S_PAGINAS_ADICIONAIS )+ 1;

S_NUMEROS_DE_PAGINAS = S_NUMEROS_DE_PAGINAS + S_PAGINAS_ADICIONAIS ;

--Dialog.Message("páginas",tostring(S_NUMEROS_DE_PAG INAS))

S_CONTADOR = 0;

S_CONTADOR2 = 1;

nRow =0 ;

sLinea = 690 ;
sLinea2 = 675 ;

while S_CONTADOR < S_NUMEROS_DE_PAGINAS do


font=hpdf.GetFont(Relatorio_pdf, "Helvetica","WinAnsiEncoding");

S_CONTADOR = S_CONTADOR + 1

sEspera ()



S_NOMEPAGINA = "página"..S_CONTADOR;

S_NOMEPAGINA = hpdf.AddPage(Relatorio_pdf);
hpdf.Page_SetWidth (S_NOMEPAGINA,595.276);
hpdf.Page_SetHeight (S_NOMEPAGINA,841.89);
hpdf.Page_SetFontAndSize(S_NOMEPAGINA, font, 10);


s_Relatorio (S_NOMEPAGINA) ;

S_Logotipo (S_NOMEPAGINA);

sCabeceira(S_NOMEPAGINA,sLinea);
sPie (S_NOMEPAGINA,S_CONTADOR,S_NUMEROS_DE_PAGINAS);


for S_CONTADOR2 = 1, tbResults.Rows do

nRow = nRow + 1;
font2=hpdf.GetFont(Relatorio_pdf, "Courier","WinAnsiEncoding");
hpdf.Page_SetFontAndSize(S_NOMEPAGINA, font2, 8);

hpdf.Page_BeginText(S_NOMEPAGINA);


hpdf.Page_TextOut(S_NOMEPAGINA, 45,sLinea2,(tbResults.Data[nRow]['Nome']));
hpdf.Page_TextOut(S_NOMEPAGINA, 100,sLinea2,(tbResults.Data[nRow]['Empresa']));
hpdf.Page_TextOut(S_NOMEPAGINA, 200,sLinea2,(tbResults.Data[nRow]['Empregado']));
hpdf.Page_TextOut(S_NOMEPAGINA, 300,sLinea2,(tbResults.Data[nRow]['Area']));
hpdf.Page_TextOut(S_NOMEPAGINA, 375,sLinea2,(tbResults.Data[nRow]['HoraEntrada']));
hpdf.Page_TextOut(S_NOMEPAGINA, 450,sLinea2,(tbResults.Data[nRow]['HoraSaida']));

sLinea2 = sLinea2 - 12 ;
hpdf.Page_EndText(S_NOMEPAGINA);


if nRow == tbResults.Rows or sLinea2 < 59 then
sLinea2 = 675
break
end


end


end

StatusDlg.Hide ( )

local res = hpdf.SaveToFile(Relatorio_pdf, _TempFolder.."\\Relatorio_"..vPDFRelatorio..".pdf" );
hpdf.Free(Relatorio_pdf)
if (res == 0) then
File.Open(_TempFolder.."\\Relatorio_"..vPDFRelator io..".pdf", _TempFolder, SW_SHOWNORMAL);
else
Dialog.Message("ERRO", "Há um documento aberto com o mesmo nome.".."\r\n".."Salve e/ou feche o arquivo para gerar um novo.", MB_OK, MB_ICONSTOP);
end
end
else

Dialog.Message("ERRO", "Erro ao criar o documento PDF.", MB_OK, MB_ICONSTOP);

end
--end
--db:close()

Can anyone give me a light on where I am getting wrong in code?
Thanks in advance for your patience and willingness to help this newbie.

[Ulrich]

Next time, please attach the exported project. It is very hard to read the code embedded in a post, with extra spaces, no indenting and no line numbers. There is no way to see where line 86 is in this post.

Your code cannot work, because you have not decided which engine you want to use to access the database. Or you use LuaSQL, or you use a plugin. You cannot perform the SELECT using LuaSQL and a few lines later attempt to walk over the resultset using features offered by the SQLite3 plugin. Or you use the SQLite3 plugin for everything, or you need to use a cursor to process the resultset obtained via LuaSQL.

Ulrich

[Bret]

Dear Ulrich,
Follow the apt file for you to review.
Can you help me correct the beginner mistakes I made?
Thank you for your attention and willingness to give up your business to help me.
Thank you.

[Bret]

Dear Ulrich,
I forgot to tell you that the application is to access, read and generate reports from a database generated for SQLite3. I've tried using SQlite, but it returns error saying that the database is not recognized or corrupted.
Thank you.

[Ulrich]

Okay, first please uninstall the current SQLite3 plugin you are using in AutoPlay Media Studio, as you were using code compatible with my version in your project, but using a plugin with the same name provided by a different developer. You can download my SQLite3 plugin from my web server: http://www.mindquake.com.br/en/actions/sqlite3 . You need the free version for this.

If have corrected the scripts so now they work. I have commented out incorrect code and left the remaining fixing for you - have a look at the page count, which is wrong. If you loop over the results longer as there are records to fetch, you will get a runtime error.

When you select a date which has at least one record in your database, you will see the result in the Grid object:



and upon clicking the button, the report is generated as PDF:



Ulrich

[Bret]

Thanks for the help Ulrich, you took a huge weight off my head. I was already thinking of starting all over again.
You're a beast!
Thank you so much.

[Bret]

The version of the SQLite3 plugin I use is the one that is available in the part of Indigo Rose plugins, and it's also free.
But I'm already picking up on Mindquake.

By the way, are you from Taboão da Serra?
If, for me, I'm using the silly translator! ... I'm from MG.

[Ulrich]

I am from Germany, but I live in Taboão da Serra, you are correct. While I understand Brazilian Portuguese, here we should keep the conversations in English.

Ulrich