Moderador: Moderadores
Eu não disse para trazer 100 mil registros no Browse, eu disse para abrir uma tabela que tenha 100 mil registros ou mais. Itamar, você não entende o que você mesmo diz e nem lê, você não lê direito.
21676 01/05/15 16:12:53 SELECT A.* FROM [PRXA_WKLADY_WOBR] A WHERE 1 = 0 /* Open Workarea */
Here is sql commands log from SQLLOG.DBF:
........
21645 01/05/15 16:12:53 SELECT * FROM [PRXA_WKLADY_WOBR] WHERE 0 = 2
21646 01/05/15 16:12:53 COMMIT
21647 01/05/15 16:12:53 COMMIT
21648 01/05/15 16:12:53 DROP TABLE [PRXA_WKLADY_WOBR] /* create table */
21649 01/05/15 16:12:53 COMMIT
21650 01/05/15 16:12:53 DELETE FROM SR_MGMNTINDEXES WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe index info */
21651 01/05/15 16:12:53 COMMIT
21652 01/05/15 16:12:53 DELETE FROM SR_MGMNTTABLES WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21653 01/05/15 16:12:53 COMMIT
21654 01/05/15 16:12:53 DELETE FROM SR_MGMNTLANG WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21655 01/05/15 16:12:53 COMMIT
21656 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRAINTS WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21657 01/05/15 16:12:53 COMMIT
21658 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRAINTS WHERE SOURCETABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21659 01/05/15 16:12:53 COMMIT
21660 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRTGTCOLS WHERE SOURCETABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21661 01/05/15 16:12:53 COMMIT
21662 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRSRCCOLS WHERE SOURCETABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21663 01/05/15 16:12:53 COMMIT
21664 01/05/15 16:12:53 SELECT * FROM [PRXA_WKLADY_WOBR] /* check dropped table */
21665 01/05/15 16:12:53 COMMIT
21666 01/05/15 16:12:53 COMMIT
21667 01/05/15 16:12:53 CREATE TABLE [PRXA_WKLADY_WOBR] ( [CZLO_KEY] VARCHAR (36) ,
[MSK_KEY] VARCHAR (46) ,
[NP] CHAR (8) ,
[TO] CHAR (10) ,
[MR] CHAR (2) ,
[RR] CHAR (4) ,
[KO] VARCHAR (30) ,
[KP] VARCHAR (30) ,
[ST] NUMERIC (1,0) ,
[SR] CHAR (3) ,
[SD] CHAR (2) ,
[ND] NUMERIC (6,0) ,
[NK] NUMERIC (4,0) ,
[KW] NUMERIC (14,2) ,
[DD] DATE NULL ,
[TP] DATE NULL ,
[DR] DATE NULL ,
[DK] DATE NULL ,
[UW] VARCHAR (30) ,
[PF] VARCHAR (25) ,
[DKR] BIT,
[USR] CHAR (1) ,
[SR_RECNO] NUMERIC (15,0) IDENTITY,
[SR_DELETED] CHAR (1) NOT NULL
)
21668 01/05/15 16:12:53 COMMIT
21669 01/05/15 16:12:53 CREATE CLUSTERED INDEX PRXA_WKLADY_WOBR_SR ON [PRXA_WKLADY_WOBR]([SR_RECNO]) /* Unique Index */
21670 01/05/15 16:12:53 COMMIT
21671 01/05/15 16:12:53 DELETE FROM SR_MGMNTTABLES WHERE TABLE_ = 'PRXA_WKLADY_WOBR'
21672 01/05/15 16:12:53 COMMIT
21673 01/05/15 16:12:53 INSERT INTO SR_MGMNTTABLES ( TABLE_ , SIGNATURE_, CREATED_, TYPE_, REGINFO_ ) VALUES ( 'PRXA_WKLADY_WOBR','MGMNT 1.72', '2015010516:12:53','TABLE',' ' )
21674 01/05/15 16:12:53 COMMIT
21675 01/05/15 16:12:53 SELECT MAX( [SR_RECNO] ) FROM [PRXA_WKLADY_WOBR] /* Counting Records */
21676 01/05/15 16:12:53 SELECT A.* FROM [PRXA_WKLADY_WOBR] A WHERE 1 = 0 /* Open Workarea */
21677 01/05/15 16:12:53 COMMIT
21678 01/05/15 16:12:53 SELECT TABLE_,SIGNATURE_,IDXNAME_,IDXKEY_,IDXFOR_,IDXCOL_,TAG_,TAGNUM_ FROM SR_MGMNTINDEXES WHERE TABLE_ = 'PRXA_WKLADY_WOBR' ORDER BY IDXNAME_, TAGNUM_
21679 01/05/15 16:12:53 SELECT TOP 12 A.* FROM [PRXA_WKLADY_WOBR] A ORDER BY A.[SR_RECNO] /* GoTop */
21680 01/05/15 16:12:53 COMMIT
..............
OLD LIB
USE PRXA_WKLADY_WOBR
? LEN(STR(KW))
14
NEW LIB (2014.08)
USE PRXA_WKLADY_WOBR
? LEN(STR(KW))
17
BUT...
OLD LIB
USE PRXA_WKLADY_WOBR
? LEN(STR(ND))
6
NEW LIB (2014.08)
USE PRXA_WKLADY_WOBR
? LEN(STR(ND))
6
SO I SUPPOUSE PROBLEM HAS SOMETHING COMMON WITH DECIMALS :)
Regards
Andrzej Morgiewicz
Simple SQL Interface for Harbour
1. Introduction
Simple SQL interface implements accessing SQL query result via RDD
interface. It is not intended to be replacement for "transparent" move of
DBFCDX application to SQL world.
I want to discuss this in more detail. Many current RDDs for SQL servers
(ex. SQLRDD from xHarbour.com) tries to make a feeling you are working with
DBF file, but not with SQL database. SQL server does not support many
features, ex. RecNo(), deleted flag, file locks, record locks. These RDDs
are emulating these features to make feeling of DBF. Deleted() function is
emulated by creating additional table columns to store delete flag. Some
"hidden system" tables are used to register locking operations and emulate
record and file locks in DBF style. The idea of SQL query is also lost. If
you do a simple loop
dbUseArea( , "select * from my_table" )
DO WHILE ! Eof()
somefunc( FIELD->some_sql_field )
dbSkip()
ENDDO
RDD usualy will read SQL rows in portions, let's say 100 records per query.
So, hidden queries are generated. If you are using indexes these queries
are really complicated. Let's have index on FIELD1 + Str( FIELD2 ). A seek
to value cValue1 + Str( nValue2 ) will generate a query like:
SELECT * FROM my_table
WHERE (FIELD1 == cValue1 and FIELD2 >= nValue2) or FIELD1 > cValue1
ORDER BY FIELD1, FIELD2, _RECNO
LIMIT 100
After evaluation of first 100 cached records, next query will be generated:
SELECT * FROM my_table
WHERE (FIELD1 == cLastField1 and FIELD2 == nLastValue2 and _RECNO > nLastRecno) or
(FIELD1 == cLastField1 and FIELD2 > nLastValue2) or
FIELD1 > cLastValue1
ORDER BY FIELD1, FIELD2, _RECNO
LIMIT 100
To optimize these queries the SQL index expresion should be
"FIELD1,FIELD2,_RECNO", but not "FIELD1,FIELD2" as written in INDEX ON
command.
"Simple SQL interface" is too long to repeat every time I want to
address this library. I'll also use acronym "SSI" to address it.
The idea of SSI is different. It does not make hidden queries. All
queries should be made explicitly by programmer. SSI gives access to query
result via RDD interface, it does not tries to emulate DBF and be
"plug-and-play" solution for DBF to SQL migration. If you do
dbUseArea( , "select * from my_table")
all query (it could contain millions of records!) will be cached.
The features of SSI approach are:
- It's possible to access SQL database of other applications. Other
applications usualy does not follow agreement of "plug-and-play" SQL drivers
about additional DELETED column, _RECNO in the end of index expression, etc.
Access of SQL database of other applications is sometimes not possible.
- It's query oriented. That means a simple DO WHILE ! Eof() loop will iterate
each records once and only once. This is not true for "plug-and-play" SQL
drivers, if indexing is used. Just like in the case of loop over DBF file.
It is not guaranteed that all records are included! Yes! If key value of the
first record in index is changed to be the last record in index during the
phase of record processing, DO WHILE ! Eof() loop will iterate only this
single records even if the database contains millions of records. Your sould
do FLock() on DBF to guarantee the records are not changed. Do you use FLock()
before readonly DO WHILE ! Eof() loops? :)
2. Architecture
+-------------+
| |
| SQLMIX RDD |
| |
+-------------+
| ^
V |
+-------------+ +---------+
| |--->| |
| SQLBASE RDD | | SDD |
| |<---| |
+-------------+ +---------+
SQLBASE RDD implements basic functionality for accessing SQL query result
via RDD interface. This RDD could be used, if indexing of query result is not
necessary or all indexing is done by SQL server (by using ORDER BY clause).
SQLMIX RDD implements indexing of query result. This indexing is not
related to SQL server ORDER BY clause. SQLMIX do indexing of the query on the
client side.
SDD is acronym for Sql Database Driver. RDD is used to implement access
of different database formats like DBF, SDF, etc. SDD is used to implement
access of different SQL databases. Every SQL server (MySQL, PostgreSQL, etc.)
has a corresponding SDD. SDD driver implements a specific part of data
exchange interface between SQLBASE and SQL server.
3. Modifying database
SSI presents a query result via RDD interface and generates no hidden
SQL queries. So, how database can be changed? Does dbAppend() and FieldPut()
works, or is it readonly SQL interface?
dbAppend(), FieldPut() and other similiar functions work on cached query
result, i.e. query can be appended by new rows and field values can be
changed, but SQL database is not changed. dbCreate() function can also be
used to create an "empty query result" but no table is created on SQL server.
So, SSI can also be used as implementation of "array RDD".
The programmer must call SQL command explicitly to modify SQL tables.
SSI provides a method to detect which cached rows was changed or appended.
Itamar M. Lins Jr. escreveu:Só essa explicação que faltou. Vamos lá!
1)Para quem trabalha com SQL não importa se a tabela tem 1 ou 1.000.000.000 de registros
1.a) Não existe abrir tabela. Apenas solicitamos algo e o resultado vem. Só isso!
É 100% ARRAY o tempo todo!!!
2)Não usamos DbGotop(),DBSEEK(), nem DbAppend()... Isso é para DBF!
Entendi, a TOTVS que é a maior empresa de Software da América Latina, faz também tudo errado, sei.
Porquê ? se o povo do xHarbour.com não morreu ? ou será que morreu até hoje escrevem if´s ?? Não sabe o que é padrão ANSI SQL...vai morrer escrevendo IF para cada banco de dados.
Sim, nem pode usar, não funciona !!! kkkkk nada é por acaso.
Pra quem tá acostumado com dbf's... muito bom aprender esses conceitos sobre SQL!
vc evita os if's para cada banco de dados que for usar.
casos de lentidão em meu sistema,
Pois é, DBF é DBF e SQL é SQL, são conceitos/usos totalmente diferentes.
O correto é a pessoa fazer um curso para poder saber como é a filosofia de como trabalhar com SQL.
Case "ADSLOCAL"
cString = "Provider=Advantage.OLEDB.1;" & _
"Mode=Share Deny None;" & _
"Show Deleted Records in DBF Tables with Advantage=False;" & _
"Data Source=" & Sistema.PathDefault & ";Advantage Server Type=ADS_Local_Server;" & _
"TableType=ADS_CDX;Security Mode=ADS_IGNORERIGHTS;" & _
"Lock Mode=Compatible;" & _
"Use NULL values in DBF Tables with Advantage=True;" & _
"Exclusive=No;Deleted=No;"
Com o SQLMIX temos SKIP, GOTO, mas na tabela virtual no resultado da QUERY, podemos apaga-la que não afeta nada na tabela MATRIZ, só temos acesso de leitura e gravação via comandos SQL, SELECT, INSERT, DROP...
Então convém alertar ao usuário o seguinte:
Vai ter que abrir e fechar os arquivos toda hora, senão as atualizações ao banco de dados não serão vistas.
Ao abrir o arquivo, só vai enxergar o que existir ali naquele momento e nada novo, mesmo horas depois, e não ser que reabra o arquivo.
É isso mesmo?
Aquilo de parar todo mundo pra acrescentar um campo novo... já era, não existe mais isso.
Usuários vendo este fórum: Nenhum usuário registrado online e 9 visitantes