terça-feira, 26 de novembro de 2013

Conexão Oracle com ( Datasul )


/* a sample procedure to test an ado connection */
def var objrecordset    as com-handle no-undo.
def var objconnection   as com-handle no-undo.
def var objcommand      as com-handle no-undo.
def var odbc-dsn        as character  no-undo.
def var odbc-server     as character  no-undo.
def var odbc-userid     as character  no-undo.
def var odbc-passwd     as character  no-undo.
def var odbc-query      as character  no-undo.
def var odbc-status     as character  no-undo.
def var odbc-reccount   as integer    no-undo.
def var odbc-null       as character  no-undo.
def var odbc-cursor     as integer    no-undo.
def var c-char          as character  no-undo.

/* if not executing against a sports2000 like database this temp table will need to be redefined */
define temp-table tt-item
    field nr_os       as char
    field cod_taref   as char
    field cod_item    as char
    field valor       as dec.

def var c-nr_os     like tt-item.nr_os     no-undo.
def var c-cod_taref like tt-item.cod_taref no-undo.
def var c-cod_item  like tt-item.cod_item  no-undo.

/* create the connection object for the link to sql */
create "adodb.connection" objconnection.
/* create a recordset object ready to return the data */
create "adodb.recordset" objrecordset.
/* create a command object for sending the sql statement */
create "adodb.command" objcommand.

for each manut_os no-lock:
    /* change the below values as necessary */
    assign c-char      = string(manut_os.nr_ord_ser)
           c-char      = substring(c-char,1,length(c-char) - 3) + "." + substring(c-char,length(c-char ) - 2)
           odbc-dsn    = "oracle" /* the odbc dsn */
           odbc-server = "oracle" /* the name of the server hosting the sql db and dsn */
           odbc-userid = "siman7" /* the user id for access to the sql database */
           odbc-passwd = "siman7" /* password required by above user-id */
           odbc-query  = "select * from os_prev_mt where os_prev_mt.os = " + c-char + " and " +
                         "os_prev_mt.tarefa = '" + trim(manut_os.cod_tarefa) + "'".
 
    /* open up the connecti.on to the odbc layer */
    objconnection:open ( "data source=" + odbc-dsn + ";server=" + odbc-server, odbc-userid, odbc-passwd, 0 ) no-error.
    /* check for connection errors */
    if ( error-status:num-messages > 0 ) then do:
        assign odbc-status = "Erro: conexÆo nÆo foi estabelecida!".
    end.
    else do:
        assign objcommand:activeconnection  = objconnection
               objcommand:commandtext       = odbc-query
               objcommand:commandtype       = 1 /* adcmdtext */
               objconnection:cursorlocation = 3 /* aduseclient */
               objrecordset:cursortype      = 3 /* adopenstatic */
               objrecordset                 = objcommand:execute ( output odbc-null, "", 32 )
               odbc-reccount                = objrecordset:recordcount.
 
        /* have we returned any rows ? */
        if ( odbc-reccount > 0 ) and not ( odbc-reccount = ? ) then do:
            objrecordset:movefirst no-error.

            do while odbc-cursor < odbc-reccount:
                assign c-nr_os     = objrecordset:fields ("os"):value  
                       c-cod_taref = objrecordset:fields ("tarefa"):value
                       c-cod_item  = objrecordset:fields ("codigo"):value.

                find first tt-item where
                    tt-item.nr_os     = c-nr_os     and
                    tt-item.cod_taref = c-cod_taref and
                    tt-item.cod_item  = c-cod_item  exclusive-lock no-error.

                if not avail tt-item then do:
                    create tt-item.
                    assign tt-item.nr_os     = c-nr_os  
                           tt-item.cod_taref = c-cod_taref
                           tt-item.cod_item  = c-cod_item.
                end.

                assign tt-item.valor = tt-item.valor + dec(objrecordset:fields ("qte"):value).

                assign odbc-cursor = odbc-cursor + 1.
                objrecordset:movenext no-error.
            end. /* retrieved a single data row */
        end. /* retrieved all data rows */
        else
            assign odbc-status = "Erro: registros nÆo encontrados!".
     
        /* close the ado connection */
        objconnection:close no-error.
    end. /* the connection opened correctly */
end.

/* don't forget to release the memory!! */
release object objconnection no-error.
release object objcommand no-error.
release object objrecordset no-error.

assign objconnection = ?
       objcommand    = ?
       objrecordset  = ?.

if can-find(first tt-item no-lock) then do:
    run cdp/cdv1409.w(input table tt-item).
end.
else do:
    message odbc-status view-as alert-box.
end.

Nenhum comentário:

Postar um comentário