[firebird-support] Table/field Alias - relname in SQLDA

2014-05-22 Thread acgomes2...@yahoo.com.br [firebird-support] 
I have the SQL:
  
 select 
CADPRO.CLIENTE_PRO,
   CADPRO.FORNECEDOR_PRO,
CADGRU.NOME_GRU,
SUBGRUPO.NOME_SUBGRU,
COLECAO.NOME_COL,
MARCA.DESCRICAO_MARCA,
NCM.NCM_NCM,
NCM.EX_NCM,
NCM.DESCRICAO_NCM,
LOCALIZACAO.DESCRICAO_LOC,
FORNECEDOR.NOME_CLI NOME_FORNECEDOR,
CLIENTE.NOME_CLI NOME_CLIENTE,
FAMILIA.DESCRICAO_FAM,
  
COR.DESCRICAO_PROCOR COR_PRODUTO,
COR2.DESCRICAO_PROCOR COR_CART_1,
COR3.DESCRICAO_PROCOR COR_CART_2,
  
PRINCIPAL_COMPONENTE.DESCRICAO_PRCOMP,
MODELO.DESCRICAO_MODELO,
MATERIA.DESCRICAO_PRO as DESCRICAO_MATERIA
  
 from CADPRO
   inner join CADGRU on (CADGRU.CODIGO_GRU = CADPRO.GRUPO_PRO)
   inner join SUBGRUPO on (SUBGRUPO.CODIGO_SUBGRU = CADPRO.SUBGRUPO_PRO)
   inner join COLECAO on (COLECAO.CODIGO_COL = CADPRO.COLECAO_PRO)
   inner join MARCA on (MARCA.CODIGO_MARCA = CADPRO.MARCA_PRO)
   inner join NCM on (NCM.CODIGO_NCM = CADPRO.NCM_PRO)
   inner join LOCALIZACAO on (LOCALIZACAO.CODIGO_LOC = CADPRO.LOCALIZACAO_PRO)
   inner join PRINCIPAL_COMPONENTE on (PRINCIPAL_COMPONENTE.CODIGO_PRCOMP = 
CADPRO.PRINCIPAL_COMPONENTE_PRO)
   inner join CADCLI as FORNECEDOR on (FORNECEDOR.CODCLI = 
CADPRO.FORNECEDOR_PRO)
   inner join CADCLI as CLIENTE on (CLIENTE.CODCLI = CADPRO.CLIENTE_PRO)
   inner join FAMILIA on (FAMILIA.CODIGO_FAM = CADPRO.FAMILIA_PRO)
   inner join CADPRO_COR as COR on (COR.CODIGO_PROCOR = CADPRO.COR_PRO)
   inner join CADPRO_COR as COR2 on (COR2.CODIGO_PROCOR = CADPRO.CART_COR1_PRO)
   inner join CADPRO_COR as COR3 on (COR3.CODIGO_PROCOR = CADPRO.CART_COR2_PRO)
  
   inner join MODELO on (MODELO.CODIGO_MODELO = CADPRO.CART_MODELO_PRO)
   inner join CADPRO as MATERIA on (MATERIA.CODIGO_PRO = CADPRO.CART_PAPEL_PRO) 
 
  
 Always I run sql, table and field alias for table FORNECEDOR and CLIENTE 
returns CADCLI as table alias... 
 And is not the same table, for CADPRO will be differents the values for fields 
CADPRO.CLIENTE_PRO and  CADPRO.FORNECEDOR_PRO
  
 I  run the sql, and field origin always returns CADCLI.NOME_CLI instead of 
FORNECEDOR.NOME_FORNECEDOR 
 and CADCLI.NOME_CLI  instead of CLIENTE.NOME_CLIENTE.
  
 Its a bug?
  
 FB 2.5.2.26540
 Win7 Pro
 I running SQL on IBExpert and IBX programs.

[firebird-support] order by 1, 2 - fb 32990

2018-04-12 Thread acgomes2...@yahoo.com.br [firebird-support] 
hi:
 select 
 A.Codigo
 'DINHEIRO' FORMA_PAGTO
 FROM NF A
 where A.dataEmissao between '01.01.2018' and '31.01.2018'
  and A.Modelo = '55'
 

 order by 1, 2
 

 using, "order by 2", return a error but, if i use: cast('DINHEIRO' as 
varchar(10)) FORMA_PAGTO does not ocorrur.

Re: [firebird-support] order by 1, 2 - fb 32990

2018-04-13 Thread acgomes2...@yahoo.com.br [firebird-support] 
hi helen... the comma is the less problem... 

 A correct sql:
 

 select N.DTEMISSAONOTA as DATA,
'DINHEIRO' as MEIO_PAGAMENTO1,
cast('DINHEIRO' as char(10)) as MEIO_PAGAMENTO2,
'00' as MEIO_PAGAMENTO,
N.VLRTOTALNOTA as VALOR,
0 as AUTENT_01,
0 as AUTENT_01_CALC,
N.AUTENTICACAO_NOTA as AUTENT_02,
3 as TIPO
 from NOTA N
 where (N.EMPRESANOTA = 1)
   and (N.DTEMISSAONOTA between '05/11/2018' and '04/11/2018')
   and (N.MODELONOTA = '55')
 order by 1, 2
 

 Error: 
 can't format message 13:587 -- message file D:\GDS_FBCLIENT\FB3\firebird.msg 
not found.
 conversion error from string "DINHEIRO".
 

 Of course, the index for the field "const" does not make much sense, the 
strange is the error.

 

 because the constant field will be the same value in all registers.

 

 Firebird: Server version: WI-V6.3.3.32900 Firebird 3.0

RES: [firebird-support] Table/field Alias - relname in SQLDA

2014-05-22 Thread Antônio Cristóvão Gomes acgomes2...@yahoo.com.br [firebird-support] 
then why not include Alias ​​Table in XSQLVAR?

 

 

this function should be used to retrieve the table alias?

 

function isc_dsql_sql_info(user_status: PISCStatus; stmt_handle: PIscStmtHandle;

item_length: Smallint; items: PISCSChar; buffer_length: Smallint; buffer: 
PISCSChar):

ISCStatus;

 

thanks



Antônio Cristóvão Gomes

AML-Software

Telefone: 32-3531-5071  Cel: 32-9104-2963

Ubá - MG

 

De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Enviada em: quinta-feira, 22 de maio de 2014 18:44
Para: firebird-support@yahoogroups.com
Assunto: Re: [firebird-support] Table/field Alias - relname in SQLDA

 

  

On 22-5-2014 19:15, acgomes2...@yahoo.com.br mailto:acgomes2...@yahoo.com.br  
[firebird-support] wrote:

 I have the SQL:

 select
 CADPRO.CLIENTE_PRO,
 CADPRO.FORNECEDOR_PRO,

...

 FORNECEDOR.NOME_CLI NOME_FORNECEDOR,
 CLIENTE.NOME_CLI NOME_CLIENTE,
..
 from CADPRO
 inner join CADCLI as FORNECEDOR on (FORNECEDOR.CODCLI =
 CADPRO.FORNECEDOR_PRO)
 inner join CADCLI as CLIENTE on (CLIENTE.CODCLI = CADPRO.CLIENTE_PRO)

I stripped all unnecessary code from your query to reduce the size. This 
makes it easier for us to look at it.

 Always I run sql, table and field alias for table FORNECEDOR and CLIENTE
 returns CADCLI as table alias...

Your title mentions relname, relname is the name of the original table, 
not a table alias.

 And is not the same table, for CADPRO will be differents the values for
 fields CADPRO.CLIENTE_PRO and CADPRO.FORNECEDOR_PRO

 I run the sql, and field origin always returns CADCLI.NOME_CLI instead
 of FORNECEDOR.NOME_FORNECEDOR

 and CADCLI.NOME_CLI instead of CLIENTE.NOME_CLIENTE.

 Its a bug?

No this isn't a bug. The item retrieved for XSQLVAR.relname is the 
information item isc_info_sql_relation which is always the original 
table of the field. So relname will contain the table name, not its 
alias within the query. The alias needs to be retrieved explicitly by a 
client (or tool) with a call to isc_dsql_sql_info with the 
isc_info_sql_relation_alias information item.

Something similar applies to XSQLVAR.sqlname which will contain the 
original column name (isc_info_sql_field), not its alias 
(isc_info_sql_alias, which is in aliasname). Although this field is 
included in the XSQLVAR.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] UDF or/and maybe Charset problem

2017-08-24 Thread Antônio Gomes acgomes2...@yahoo.com.br [firebird-support] 
there no a null string?

  De: "Nagy Szilveszter nagy_szilvesz...@yahoo.com [firebird-support]" 

 Para: Firebird-support  
 Enviadas: Quinta-feira, 24 de Agosto de 2017 6:41
 Assunto: [firebird-support] UDF or/and maybe Charset problem
   
    Hi,
i have an UDF function that runs perfectly on Linux 64 bit systems. I wanted to 
port it to Windows 32 bit, but i'm having problems there.
The UDF was written in C (not C++), compiled on Linux with gcc, everything 
works fine.
I compiled the same C file on Windows with MinGW Developer Studio (using MinGW 
compiler)
Here is the function with problems (the functions returns the same string but 
deletes every non alphanumerical character):

char *strpeel(char *str){
unsigned int i = 0, j = 0;
char *out;

str = strupr(str);
out = (char*)calloc(strlen(str), sizeof(char));

for (i=0; i= 'A' && str[i] <= 'Z') || (str[i] >= '0' && str[i] <= 
'9')) {
out[j++] = str[i];
} 
}

char * result;
result = (char*)calloc(j, sizeof(char));
strcpy(result, out);
return result;
}




This is the declaration of UDF in Firebird:
DECLARE EXTERNAL FUNCTION STRPEEL
CSTRING(255)
RETURNS CSTRING(255)
ENTRY_POINT 'strpeel' MODULE_NAME 'utils-udf';




When i test this function it runs correctly:
select strpeel('ABC - 123') from rdb$database    > returns: 'ABC123'



But when i run it on my PRODUCTS table, there are some rows where this function 
crashes:




select product_code, strpeel(product_code) from products
The field PRODUCT_CODE is Varchar(30) charset UTF8 collate UTF8



At row 51 the string must have some invisible characters that makes the UDF to 
crash.
The firebird.log says:


    The user defined function: STRPEEL
   referencing entrypoint: strpeel
in module:     utils-udf
caused the fatal exception: Access violation.
The code attempted to access a virtual
address without privilege to do so.
This exception will cause the Firebird server
to terminate abnormally.


    Shutting down the server with 2 active connection(s) to 2 database(s), 0 
active service(s)


    Firebird shutdown is still in progress after the specified timeout


    Operating system call ReleaseSemaphore failed. Error code 6


    Operating system call ReleaseSemaphore failed. Error code 6




That code in that line is TRX12270
If i run select strpeel('TRX12270') from rdb$database - it works fine
If i copy that string from table and replace the above string then it crashes 
again. So there must be something wrong in that field.
Also if i edit the table and rewrite the code then it works fine.


I also checked the characters with ASCII_VAL but i can see nothing strange.


As the UDF, there are 2 memory allocations for strings and no freeing it, 
neither FREE_IT was used. I dont know if this is a problem since other strings 
work well with this UDF.

Please help me detect and correct this issue!



Thank you!
























  #yiv3803517428 #yiv3803517428 -- #yiv3803517428ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3803517428 
#yiv3803517428ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3803517428 
#yiv3803517428ygrp-mkp #yiv3803517428hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv3803517428 #yiv3803517428ygrp-mkp #yiv3803517428ads 
{margin-bottom:10px;}#yiv3803517428 #yiv3803517428ygrp-mkp .yiv3803517428ad 
{padding:0 0;}#yiv3803517428 #yiv3803517428ygrp-mkp .yiv3803517428ad p 
{margin:0;}#yiv3803517428 #yiv3803517428ygrp-mkp .yiv3803517428ad a 
{color:#ff;text-decoration:none;}#yiv3803517428 #yiv3803517428ygrp-sponsor 
#yiv3803517428ygrp-lc {font-family:Arial;}#yiv3803517428 
#yiv3803517428ygrp-sponsor #yiv3803517428ygrp-lc #yiv3803517428hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3803517428 
#yiv3803517428ygrp-sponsor #yiv3803517428ygrp-lc .yiv3803517428ad 
{margin-bottom:10px;padding:0 0;}#yiv3803517428 #yiv3803517428actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3803517428 
#yiv3803517428activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3803517428
 #yiv3803517428activity span {font-weight:700;}#yiv3803517428 
#yiv3803517428activity span:first-child 
{text-transform:uppercase;}#yiv3803517428 #yiv3803517428activity span a 
{color:#5085b6;text-decoration:none;}#yiv3803517428 #yiv3803517428activity span 
span {color:#ff7900;}#yiv3803517428 #yiv3803517428activity span 
.yiv3803517428underline {text-decoration:underline;}#yiv3803517428 
.yiv3803517428attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv3803517428 .yiv3803517428attach div a 
{text-decoration:none;}#yiv3803517428 .yiv3803517428attach img