🗄️ Classe TLPP para Copiar Query para SQLite V2 — Completa e Disponível no GitHub

Segue abaixo a classe completa para cópia de dados, desenvolvida em TLPP. Ela também está disponível e atualizada no GitHub — fique à vontade para fazer fork, sugerir melhorias ou enviar commits! 😄 O objetivo é simples e útil: pegar uma query e copiar seu resultado para um banco SQLite, de forma rápida e eficiente.

Nome do fonte: CUSTOM.UDERQRYTOSQLITE.tlpp

 #INCLUDE "TOTVS.CH"

namespace custom

/*/{Protheus.doc} UDERQRYTOSQLITE
Classe de Progresso
@type       class
@author     Eurai Rapelli
@since      2021.08.14
/*/
Class UDERQRYTOSQLITE

	Public data cAliasName      as character
	Public data cNameTbl    as character
	Private data cError         as character
	Private data cDriver        as character
	Private data cRootSrv       as character

	Public Method New( cRootSrv, cDriver ) Constructor
	Public Method Execute( cQuery, cTmp )
	Public Method Count(nReg,cSQL)
	Public Method Close()
	Public Method GetError()
	Public Method GetRealName()

EndClass

/*/{Protheus.doc} New
Instancia da classe
/*/
Method New( cRootSrv as character, cDriver as character ) Class UDERQRYTOSQLITE
	Default cDriver 	:= 'SQLITE'			//SQLITE_SYS	//SQLITECLI	//DBFCDX //SQLITE //SQLITE_TMP	//CTREECDX
	Default cRootSrv   	:= '\ud\backup\' + Dtos(Date()) + '_' + StrTran(Time(),':','') + cValtoChar(Randomize(1,9999))

	cRootSrv	:= AllTrim( cRootSrv )
	If Right( cRootSrv, 1 ) != '\'
		cRootSrv += '\'
	Endif
	FWMakeDir( cRootSrv )

	Self:cError 		:= ''
	Self:cDriver 		:= Upper(cDriver)
	Self:cRootSrv 		:= cRootSrv
Return( Self )

/*/{Protheus.doc} Execute
Executa a copia para o SQLLite
/*/
Method Execute( cQuery as character, cAliasName as character, cNameTbl as character  ) Class UDERQRYTOSQLITE
	Local lRet            := .T. as logical
	Local cAliasQuery     := GetNextAlias() as character
	Local lErro           := .F. as logical
	Local bError          := {||} as codeblock
	// Local nTamArray	      := 0 as numeric
	// Local nI	   		  := 0 as numeric

	Default cQuery      := ''
	Default cAliasName  := GetNextAlias()
	Default cNameTbl   := 'TMP_' + Dtos(Date()) + '_' + StrTran(Time(),':','') + cValtoChar(Randomize(1,9999)) + FunName()


	Begin Sequence

		If Empty(cQuery)
			Self:cError := "Query enviada está vazia."
			lRet     := .F.; Break
		Endif
		Self:cNameTbl 		:= cNameTbl + '.SDB'
		Self:cAliasName     := cAliasName
		If Select(cAliasName) > 0
			(cAliasName)->( dbCloseArea() )
		Endif
		If Select(cAliasQuery) > 0
			(cAliasQuery)->( dbCloseArea() )
		Endif
		ErrorBlock( { |UD| cError := UD:ErrorStack, lErro := .T., Break(UD) } )
		Begin Sequence
			MPSysOpenQuery(cQuery,cAliasQuery)
		End Sequence
		ErrorBlock(bError)
		If lErro
			Self:cError := "Query com erro: " + TCSQLERROR()
			lRet     := .F.; Break
		Endif
		DBSqlExec( Self:cRootSrv + Self:cNameTbl , "DROP TABLE " + Self:cNameTbl, Self:cDriver) //SQLiteCLI
		DBCreate( Self:cRootSrv + Self:cNameTbl, (cAliasQuery)->(dbStruct()), Self:cDriver )
		DBUseArea( .T., Self:cDriver, Self:cRootSrv + Self:cNameTbl, Self:cAliasName, .F., .F. )

		// nTamArray := Len( (cAliasQuery)->(dbStruct()) )
		// Do While (cAliasQuery)->( !EOF() )

		// 	RecLock(Self:cAliasName,.T.)
		// 	For nI := 1 To nTamArray
		// 		(Self:cAliasName)->( FieldPut( nI, (cAliasQuery)->( FieldGet( nI ) ) ) )
		// 	Next nI
		// 	(Self:cAliasName)->( MsUnlock() )

		// 	(cAliasQuery)->( dbSkip() )
		// EndDo
		If !DBTblCopy(cAliasQuery, Self:cAliasName)
			Self:cError := "Tabela não foi copiada."
			lRet     := .F.; Break
		Endif
		(cAliasQuery)->( dbCloseArea() )
	End Sequence

Return( lRet )

/*/{Protheus.doc} Close
Fecha o arquivo e deleta
/*/
Method Close() Class UDERQRYTOSQLITE
	Local lRet := .T. as logical
	(Self:cAliasName)->( dbCloseArea() )
	If !DBSqlExec( Self:GetRealName() , "DROP TABLE " + Self:GetRealName() , Self:cDriver)
		Self:cError := "Tabela não foi deletada"
		lRet     := .F.
	Endif
Return( lRet )

/*/{Protheus.doc} Count
Contador de registros
/*/
Method Count(nReg as numeric,cSQL as character) Class UDERQRYTOSQLITE
	Default nReg := 0
	Default cSQL := "SELECT COUNT(0)QTD FROM " + Self:GetRealName()

	If DBSqlExec( 'TMP_COUNT_UD' , cSQL, Self:cDriver)
		nReg := TMP_COUNT_UD->QTD
		TMP_COUNT_UD->( dbCloseArea() )
		lRet := .T.
	Else
		Self:cError := "Erro no count da tabela"
		lRet     := .F.
	Endif

Return( lRet )

/*/{Protheus.doc} GetError
Retorna mensagem de erro
/*/
Method GetError() Class UDERQRYTOSQLITE
Return( Self:cError )

/*/{Protheus.doc} GetRealName
Retorna o nome real da tabela SQLite
/*/
Method GetRealName() Class UDERQRYTOSQLITE
Return( Self:cNameTbl )

Gostou? Compartilhe com seus amigos e deixe um comentário! 😎

Um abraço, e até a próxima! 👋

#protheus #advpl #tlpp #sqlite #classe tlpp #totvsprotheus #query sqlite #programacaoadvpl #fwintegration #dbsqlite #copiar query #topconn