Esta ayuda es para todos aquellos que empizan a hacer sus aplicaciones con DBExpert.
Esta es una guia no oficial.
En la pagina de http://www.sundialsystems.com dispones de una lista de correos. De la cual se han sacado multiples de estos ejemplos.
El que quiera me puede remitir sus comentarios, o nuevos consejos para añadirlos a este fichero. Si alguien se anima a traducirlo al Ingles, mejor.
Roberto Gainza
roberto@zikoyl.com
Siempre deberas de hacer una copia de seguridad de tus datos, y todas tus pruebas realizarlas sobre esos datos de respaldo. En varias ocasiones yo he llegado a perder tablas enteras durante el desarroyo.
Pero lo mas importante, durante mas de dos años, en el trabajo no se a perdido un solo byte. No puedo decir lo mismo de otros sistemas operativos y otros programas de bases de datos.
Supongamos que tenemos una tabla llamada TABLE3, con dos columnas numero (Number) y nombre(text). Introduce unos 4 registros de prueba.
Y queremos renumerar el campo numero, empezando por el 10 y terminando en el ultimo record.
1º Crearemos una macro, como esta:
/*MACERAR*/
/*ACERAMIENTO DE VARIABLES DE ENTORNO*/
GUARDAR='OS2ENVIRONMENT'
ASUNO=VALUE('ASUNO',10,GUARDAR)
ASDOS=VALUE('ASUNO',10,GUARDAR)
2º Crearemos otra macro como esta:
/*MAAANUMERO*/
NUMERIC DIGITS(9)
ARG N
GUARDAR='OS2ENVIRONMENT'
ASUNO=VALUE(ASUNO,ASUNO,GUARDAR)
ASDOS=VALUE('ASUNO',ASUNO+1,GUARDAR)
RETURN ASUNO
3º Crearemos una query como esta: QRESULTADO
SELECT Table3.NUMERO, Table3.NOMBRE, DBERUNMACRO ('MAAANUMERO', NUMERO)
AS RENU
FROM Table3
4º Crearemos una macro que ejecute, la primera macro y luego la consulta(query).
/*MLASDOS*/
RC=DBEOPEN('MACRO','MACERAR')
RC=DBEOPEN('QUERY','QRESULTADO')
Ya esta renumerado, esto servira tambien para calcular los saldos de un extracto, o cualquier otra cosa que se nos ocurra.
DIF=ABS(IMPORIVA)-ABS(RECOGIDO)
IF DIF<=0.49 THEN
DO
ENTREGAR=RECOGIDO
END
ELSE
DO
IF IMPORIVA < 0 THEN
DO
ENTREGAR=RECOGIDO-1
END
ELSE
DO
ENTREGAR=RECOGIDO+1
END
END
END
ELSE /*SI NO SON PTS COGE DOS DECIMALES NADA MAS*/
DO
RECOGIDO=TRUNC(RECOGIDO*100,0)
IMPORIVA=IMPORIVA*100
DIF=ABS(IMPORIVA)-ABS(RECOGIDO)
IF DIF<=0.49 THEN
DO
ENTREGAR=RECOGIDO/100
END
ELSE
DO
IF IMPORIVA < 0 THEN
DO
ENTREGAR=(RECOGIDO-1)/100
END
ELSE
DO
ENTREGAR=(RECOGIDO+1)/100
END
END
/*ENTREGAR=RECOGIDO/100*/
END
IMPORIVA=ENTREGAR
RETURN
Ejemplo:
Cadena=nombre & '~' & NumTlf & '~' & Edad & '~' & Sexo&'~'&pais
En la query tendremos campo_ficticio:dberunmacro('la_macro',cadena)
/*la_macro*/ esta es la forma de hacerlo MAL
ARG GRU
P1=POS('~',GRU)
P2=POS('~',GRU,P1+1)
AL=P2+1
P3=POS('~',GRU,AL)
AL=P3+1
P4=POS('~',GRU,AL)
nombre=SUBSTR(GRU,1,P1-1)
numtlf=SUBSTR(GRU,P1+1,P2-P1-1)
edad=SUBSTR(GRU,P2+1,P3-P2-1)
sexo=SUBSTR(GRU,P3+1,P4-P3-1)
pais=SUBSTR(GRU,P4+1,DBELEN(GRU)-P4)
........
RETURN nueva_cadena_o_valor
y esta otra es la forma de hacerlo BIEN
/*METICOPIA*/
PARSE ARG CCLI '~' CONTE '~' ETI '~' BUL '~' NOM '~' MASNOM '~' CONTAC
'~' CALLE '~' CP '~' POBLA '~' PROV '~' PAIS
Despues de esto se pueden operar con los campos, y retornar a cadena el valor o valores deseados.
Es posible sustituir el caracter Alt126=~ por otros caracteres validos. Yo he seleccionado este caracter por no estar en los teclados Españoles, por lo que no tengo posibilidades de encontrarme con un texto introducido por un usuario que pueda contener ese caracter. Por lo que lo utilizo como separador de argumentos.
La forma de hacer esto es poniendo el boton con el texto rojo, que queremos aparedca cuando lo pulsamos. Y escribiendo en otro lugar texto simple, con lo que queremos aparedca en negro. Luego lo arrastramos encima del boton y ya tenemos un boton con dos coleres.
No olvidar que al terminar la macro, debes de añadirle una instruccion del tipo dbegotoformcontrol(), a otro boton, para que al terminar el proceso se retorne el color negro.
1º Tendremos creada una tabla de por lo menos una columna, y con un registro cualquiera introducido (esto es importante, si no, no funcionara) Pongamos para este ejemplo el nomnbre de tabla CATALIZA.dbf En esta tabla ni se escribira nada, ni se leera nada, solo sirve para poder ejecutar la sentencia SELECT ficticia.
2º Supongamos que tenemos estos tres valores:
a- Dias='30-10-00'
b- Tlf=93223311
c- Nombres='Pepe'
3º Supongamos que la tabla destino se llama DESTINO.dbf y tiene estos campos:
dia de tipo fecha
telefono de tipo numero
nombre de tipo texto
otro_mas de tipo texto
4º.....
CAD='INSERT INTO DESTINO (DIA, TELEFONO, NOMBRE ) SELECT ',
' '"'"DIAS"'"' AS DIA, 'TLF' AS TELEFONO, '"'"NOMBRES"'"' AS NOMBRE
FROM CATALIZA'
rc=DBESHOWWARNINGS(FALSE)
RC=DBERUNSQL(CAD)
....
number11.zip
Muchos ejemplos y mas informacion de Rexx de calidad:
rxtt32.zip
Informacion de Rexx incluida con el Warp:
rexx.inf
Una guia para iniciarse en Rexx:
rexxsrc.inf = rs960115.zip
Informacion sobre SQL.
http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM
Acceso al zocalo del TCP/IP con Rexx
rxsocket.inf
Basicamente para usar una DLL, lo que tenemos que hacer es decirle a la DLL, oye!,que te quiero usar aqui, puedo?
Y esto se hace con:
call RxFuncAdd 'SysLoadFuncs', 'RexxUtil', 'SysLoadFuncs'
call SysLoadFuncs
En este caso llamamos a RexxUtil.dll y a partir de aqui si todo es ok, podremos usar las funciones que tenga esa dll, en nuestro programa.
Obtener este resultado:
TABLE3 or Query
E
F
La solucion buena:
Copy o insertar la TABLE2 en otra TABLE2_2
Enlazar TABLE1 con TABLE2_2 y Borrar los campos enlazados en TABLE2_2
Esta solucion es sencilla y aceptablemente rapida.
La solucion mala:
Crear una nueva tabla, con todos los registros de las dos tablas,(se
generara una tabla enorme, y luego agrupandola obtenemos este resultado )
TABLE4
A
B
C
D
E
F
Luego se borrarian los registros de la TABLA4 que existen en la TABLA1.
Otherwise, it might work you get quote marks around the entire name.
Something like this:
quotemark = '"'
file="two parts.ctx"
rc=dberunapp("e:\cl\bin\look.exe",quotemark ||
"e:\documents\" || file || quotemark)
Now create a command file "papstart.cmd" on the correct
location.
This file a normal commandfile with the parameters: eCS-window, close
when ended
The content of the file is:
i:
cd \lag
START "Papierlager " /N /F rbase -r chpt
EXIT
This means:
change to the correct drive
change to the correct path
START the programm "RBASE" with the parameter "-r
chpt" (That means: no
programlogo, use the subcommandfile chpt"
The word "Papierlager" is only for naming the window.
Then EXIT the command file.
1) formfields are treated as "character", not as
"number":
Sometimes this problem arises when the field values are used in
calculations, expressions (dbeIIF...) or WHERE-clauses. One solution to
cover this problem was to use the command "dbeCharToNumber".
But it is
much easier, just to set the default value of the
"rebellious" variable
to 0 (zero). Then the variable will be treated as "number"!
2) There is a constant problem with the decimal "point" and
the decimal
"comma":
People, using the decimal comma are getting troubles when numbers are
entered into a formfield. Different errors occurs, depending on the
underlaying database. To prevent from those errors, just put the
following command into the after update (also the before update event
works correctly):
=dbeSetValue('fieldname', fieldname * 1)
This simple multiplication causes to transform the entered decimal
comma
to a decimal point before the data is stored into the database (you can
watch this changing in a spreadsheet form: walking through the rows
will
change the value in the ACTIVE row to the decimal point while the other
rows will be displayed with the decimal comma as specified in the
system
settings)
Maybe someone else needs this solutions too.
Hanno
Otra 3) solucion es modificar en el sistema el 'country palette', cambiando
las solapas de monetary y numeric a 1 000.22 , y marcar por defecto.
In most of my forms, I do not use the DBE built in "show VCR"
or the
built in "show Toolbar" options. I prefer to create buttons
for "Next",
"Previous", "First" and "Last". Beside
other advantages of this
solution, there is one I want to describe below:
Sometimes I have the request, that the user should not be able to add
new rows to a table - only editing should be possible.
When placing the buttons for "Next", "Previous",
"First" and "Last" on
the form, there is an undesired effect with the "Next"
button: Pressing
the button in the last row, creates empty form field for adding a new
row.
So I found a little solution, checking if the last row is reached and,
if the user presses "Next" again not walk to a new row, but
stay in the
last row or the table or query.
To do this, just add the following command in the "On push"
event of the
"Next" button:
=dbeRunMacro('vnext_not_new_m', 'check_column_name')
This calls the vnext_not_new_m macro and passes along 1 parameter: the
name of a column of the form. It is essential, that the chosen column
is
a field, that does not allow NULL values, because of this will be
checked in the called macro!
Now here is the code for the macro 'vnext_not_new_m':
/* vNext_not_New_M */
/* check, ob bei GotoNextRecord das dateiende erreicht ist und in eine
leere zeile gesprungen würde */
/* falls sprung in leere Zeile: gehe zur letzten zeile */
/* übergebener parameter: feldname, der bei neuer zeile NULL
wäre (muss
feld sein, das sonst niemals NULL sein kann!) */
PARSE ARG mfieldname
rc=dbeGotoNextRecord()
mfieldname='ActiveForm:' || mfieldname
IF dbeGet(mfieldname) = '' THEN
rc=dbeGotoLastRecord()
EXIT
As you see, the macro simply gets the fieldname to compare, does a next
record function, checks if there is a NULL value in a "null not
allowed"
field and, if the fieldvalue is NULL, goes to back to the LAST record
of
the table or query.
That's it. Hope some of you can use it. Please let me hear your
comments.
Hanno
PS: In addition to my explanations above, I will give a more complex
possibility to use this function:
Think of a program/form to edit invoices. There can be 1 or more
invoices be stored for a customer. To edit the data, there is a form
with an embedded subform and the user should only be able to add rows
to
the subform: the row(s) of the mainform should only be editable. The
mainform holds the row(s) for the specific invoicenumbers and the other
data, only needed 1 time for each invoice (customer number, date, terms
of delivery, ...). The subform (typically "spreadsheet"-view)
holds the
article-rows for this invoice: 1 or many, each editable or deleteable
and also new rows can be added.
Form and subform data is liked together with the invoice number.
Now think of the buttons to walk between the invoices. Pressing
"Next"
(on the mainform!) causes DBE to get the next invoice row and link
immediately the corresponding article rows for the subform.
But pressing "Next" in the last row brings up a lot of
troubles: there
is no invoice row available and storing such "empty" rows is
naturally
not desired. If however, the 'vNext_not_New_M' macro is used, there is
no possibility for a user to make a fault.
This function is of course also useful for customer tables and much more.
This will NOT work, because of the lenght of the sting is a the end
(after adding value1 and value2 and value3) equal as having just 1 long
valiable.
INTERPRET value1 || value2 || value3
does not solve the problem, because of REXX first adds the values and
the
interprets the content of the values (In general that's o.k. and the
desired action that REXX should perform.
Again: The problem is, that REXX does not allow more than about 250
characters to interpret in one command.
The solution:
mcommand1='dbeRunSQL("INSERT INTO martsuche_zwi (mabk, abfrageID)
SELECT
mabk, forms:v_f:vmat_berecht AS a FROM mart WHERE 1=1" '
/* generieren der dynamischen WHERE-klausel aufgrund der eingegebenen
daten in martsuche_f: */
mcommand2=' " '
IF dbeGet('forms:martsuche_f:vmabk') <> '' THEN
mcommand2=mcommand2 "AND mabk LIKE ActiveForm:vmabk"
IF dbeGet('forms:martsuche_f:vmgruppe') <> '' THEN
mcommand2=mcommand2 "AND mgruppe LIKE ActiveForm:vmgruppe"
IF dbeGet('forms:martsuche_f:vmbezeich') <> '' THEN
mcommand2=mcommand2 "AND mbezeich LIKE
ActiveForm:vmbezeich"
IF dbeGet('forms:martsuche_f:vmtxt') <> '' THEN
mcommand2=mcommand2 "AND mtxt LIKE ActiveForm:vmtxt"
IF dbeGet('forms:martsuche_f:vpapaufbau') <> '' THEN
mcommand2=mcommand2 " AND papaufbau LIKE
ActiveForm:vpapaufbau"
IF dbeGet('forms:martsuche_f:vpapoberfl') <> '' THEN
mcommand2=mcommand2 "AND papoberfl LIKE
ActiveForm:vpapoberfl"
mcommand2=mcommand2 '" '
mcommand3=' " '
IF dbeGet('forms:martsuche_f:vvpapgm2') <> '' THEN
mcommand3=mcommand3 "AND papgm2 >= ActiveForm:vvpapgm2"
IF dbeGet('forms:martsuche_f:vbpapgm2') <> '' THEN
mcommand3=mcommand3 "AND papgm2 <= ActiveForm:vbpapgm2"
IF dbeGet('forms:martsuche_f:vpapfarbe') <> '' THEN
mcommand3=mcommand3 "AND papfarbe LIKE
ActiveForm:vpapfarbe"
IF dbeGet('forms:martsuche_f:vpapformat1') <> '' THEN
mcommand3=mcommand3 "AND papformat1 >=
ActiveForm:vpapformat1"
IF dbeGet('forms:martsuche_f:vpapformat2') <> '' THEN
mcommand3=mcommand3 "AND papformat2 >=
ActiveForm:vpapformat2"
IF dbeGet('forms:martsuche_f:vmlagerort') <> '' THEN
mcommand3=mcommand3 "AND mlagerort LIKE
ActiveForm:vmlagerort"
/* Ergänzen des befehls mit abschluss-characters: */
mcommand3=mcommand3 || '")'
/* Einfügen der selektierten Zeilen mit INTERPRET, damit dynamische
WHERE-Klausel überhaupt möglich ist */
TRACE off
INTERPRET mcommand1,
mcommand2,
mcommand3
TRACE normal
rc=dbeRequeryControl('vmartsuche_sf')
/****--coment about rexx--*****/
<quote>
Implementation maximum: A literal string can contain up to 250
characters.
The length of the evaluated result of an expression, however, is
limited
only by the available virtual storage of your computer, with an
additional
limit of 512MB maximum per process. </quote>
In practice the maximum string lenght limit is well under 512MB.
We haven't changed the processing of validation rules for some time...
thus I would expect this to be consistent with most recent versions of
DBExpert.
The issue is indeed that the validation is being done on TEXT fields.
DBExpert trys to be intelligent about the type of comparision in the
rule
based on the type of the field being validated.
In this case, the comparison is done as a string comparison rather than
a numeric comparison... and it's producing the correct result in that
case.
There is currently no way to force the comparison be be specifically
one way (numberic) or the other (string). If you need that, you will
have to write a macro to do the validation in the BeforeUpdate event
(instead of using a validation rule).
The solution:
You need the last version of Dbexpert.
X = dbeTimestampToTime(dbeGet('TimeIn'))
Y = dbeTimestampToTime(dbeGet('TimeOut'))
HRSIN = dbeRound(dbeTimeToNumber(X) * 24,2)
HRSOUT = dbeRound(dbeTimeToNumber(Y) * 24,2)
ROUNDUP = ((HRSIN + .24) % .25) * .25
ROUNDDOWN = (HRSOUT % .25) * .25
HRS = ROUNDDOWN - ROUNDUP - .5
rc=dbeSetValue('Hrs Reg',HRS)