MySql vs Oracle

[Armando]

Amigos foreros:

Tengo en ciernes el desarrollo de una aplicación para un nuevo cliente, hasta ahora
mi experiencia ha sido con MySql, posiblemente el cliente elija Oracle y lo más probable
es que se instale en la nube, de ahí mis dudas y preguntas, que diferencia hay entre el
motor de DB MySql vs Oracle?, parece que los comandos (SELECT, INSERT, UPDATE etc.)
son los mismos?, necesito adquirir una licencia de Oracle para desarrollador ?,
mi experiencia en MySql puede ser suficiente para atacar con éxito este
nuevo proyecto?.

Saludos y gracias por compartir su experiencia

[Rick Lipkin]

Armando

I like to use MS Access for local and small network applications using ADO .. and you can use ADO for Oracle as well .. but my choice of an ( ado ) Enterprise database would be Microsoft Sql Server which is free up to a certain number of users ..

I am NOT a Cloud person .. and when a Customer comes into our Computer shop we have a sign on the wall that says:

The definition of "The Cloud" is basically someone elses computer ... I ask people what their definition of "the cloud" is ?? and I get the usual responce like "It is just OUT THERE" .. and here is my explaination .. The Cloud is just a data center somewhere ... and there are people that manage that data .. and those people have access to all that data .. so I tell people .. "Would you put you usename and password to your bank account ON SOMEONE ELSES Computer ? and the usual responce is "Hell no"

Forgive me ... I tell Organizations that if you have any local development .. "KEEP IT BEHIND YOUR FIREWALL" ... all you have to do is go to BleepingComputer.com which just today posted this and many more Security internet hacks :

SECURITY
Water services giant Veolia North America hit by ransomware attack
Veolia North America, a subsidiary of transnational conglomerate Veolia, disclosed a ransomware attack that impacted systems part of its Municipal Water division and disrupted its bill payment systems.

MySql, Oracle and MS Sql Servers are ALL good choices for local applications .. just beware of the cloud and even if users need internet access ... in a Corporate environment .. you can always use a VPN to connect remote users .. however if you are creating a commercial app to be presented on the Internet .. JUST BEWARE!!!! There is lots of EVIL out there and I sure do not want to be the next internet "Hack"

Rick Lipkin

[Armando]

Rick:

Thank you very much for your advice, I will keep it in mind,
and I totally agree with you.

Best regards

[Armando]

Amigos:

Nadie usa Oracle ?

Saludos

[cmsoft]

Armando:
Yo uso Ado para conectarme a una base de datos Oracle, que es de otro sistema (que no desarrollé yo) pero no me considero en condiciones de responderte las preguntas.
Lo uso basicamente para consultas, obtener informes, etc o para obtener datos que luego interactuan con sistemas que si desarrollé yo en MySql.
Yo intuyo que la diferencia se nota mas si usas las funciones mas avanzadas del motor (Procedures, Packages, Functions, Views, etc)
Luego el uso de estas tambien es diferente con respecto a Mysql en Fivewin.
También al haber menos usuarios de Fivewin que lo usen en comparación con Mysql o MariaDB existe menos post al respecto.
Creo que dependerá mucho de la complejidad del sistema que tengas que desarrollar

[Carles]

Hi

Armando

I like to use MS Access for local and small network applications using ADO .. and you can use ADO for Oracle as well .. but my choice of an ( ado ) Enterprise database would be Microsoft Sql Server which is free up to a certain number of users ..

I am NOT a Cloud person .. and when a Customer comes into our Computer shop we have a sign on the wall that says:

The definition of "The Cloud" is basically someone elses computer ... I ask people what their definition of "the cloud" is ?? and I get the usual responce like "It is just OUT THERE" .. and here is my explaination .. The Cloud is just a data center somewhere ... and there are people that manage that data .. and those people have access to all that data .. so I tell people .. "Would you put you usename and password to your bank account ON SOMEONE ELSES Computer ? and the usual responce is "Hell no"

Forgive me ... I tell Organizations that if you have any local development .. "KEEP IT BEHIND YOUR FIREWALL" ... all you have to do is go to BleepingComputer.com which just today posted this and many more Security internet hacks :

SECURITY
Water services giant Veolia North America hit by ransomware attack
Veolia North America, a subsidiary of transnational conglomerate Veolia, disclosed a ransomware attack that impacted systems part of its Municipal Water division and disrupted its bill payment systems.

MySql, Oracle and MS Sql Servers are ALL good choices for local applications .. just beware of the cloud and even if users need internet access ... in a Corporate environment .. you can always use a VPN to connect remote users .. however if you are creating a commercial app to be presented on the Internet .. JUST BEWARE!!!! There is lots of EVIL out there and I sure do not want to be the next internet "Hack"

Rick Lipkin

Rick as always, your reflections are interesting, but you must also keep in mind that this vision makes you lock yourself in your local environment. I completely agree that the cloud, the web, ... are very dangerous scenarios, but it is for this reason that we have to learn much more than before.

You have your banking details outside your local area, when you buy on Amazon, when you interact with your government pages,… and yes, The definition of "The Cloud" is basically someone else's computer...

It is for this reason that it is important to make sure how protected the system is and to whom you report your data, but... more and more clients want to interact with your system with their smartphone, tablet, web and we cannot hide behind your firewall

Nice weekend.
C.

[Armando]

Estimado César:

En principio me ayudarías mucho si me compartes la cadena de conexión al servidor
de la base de datos. y saber si necesito algún conector?

Saludos

[Rick Lipkin]

Carles

If you recall the website for 23 and Me was recently hacked and lots of data personal genome data was hacked .. and this is only a drop in the bucket ..

[url]
https://www.bleepingcomputer.com/news/s ... h-reports/
[/url]

Not that I am against data web access like you mentin Web on-line banking ... If you don't have to put your app on the internet .. and I do not care how diligent or you pay a hi-powered security person .. you will be a target sooner or later ...

Rick Lipkin

[cmsoft]

Estimado César:
En principio me ayudarías mucho si me compartes la cadena de conexión al servidor
de la base de datos. y saber si necesito algún conector
Saludos

Tienes que instalar el cliente Oracle.
Luego en tu programa creas la conexión así:

Code: Select all  Expand view  RUN

    cConnStr := "Provider=MSDASQL.1;Password =mypassword;Persist Security Info=False;User ID=myuser;Data Source=Oracle"
    oCn := FW_OpenAdoConnection( cConnStr )
    IF oCn = NIL
       MsgStop( "Error al conectarse a ORACLE!!","Error al conectar")
       RETURN nil
    ENDIF
 

Luego, por ejemplo para consultar un dato y llenar campos get con ellos hago:

Code: Select all  Expand view  RUN

STATIC FUNCTION CargarDato(oGet,nBuscar)
LOCAL lRta := .f., oErr, oRs, cSql
cSql := "select cod_prov, razon_social "+;        
        "from proveedores "+;                
        "where cod_prov = "+Str(nBuscar)
TRY
   oRs := TOleAuto():New("ADODB.Recordset")
   oRs:Open(cSql,oCn)
CATCH oErr
   MsgStop( "Error en la consulta, verifique!!"+CHR(10)+;
            oErr:Operation+CHR(10)+oErr:Description,"Error SQL")
   RETURN .f.
END TRY
IF  oRs:RecordCount() > 0
     oGet[02]:cText := oRs:Fields("razon_social"):value
    lRta := .t.
    ELSE
    oRs := BrowseOracle("proveedores",{"cod_prov","razon_social","fantasia"}) //Tabla, array con campos que quiero mostrar, where
    oGet[01]:cText := oRs:Fields("cod_prov"):value
    oGet[02]:cText := oRs:Fields("razon_social"):value
    lRta := .f.
ENDIF
RETURN lRta

******************************************************
*** Buscar en tabla generica Oracle
FUNCTION BrowseOracle(cTabla,aCampos,cWhere)
LOCAL i, oWnd1, oBrw, oDlg, oBot, nRes, oRs,oErr, cSql
DEFAULT cWhere := ""
cSql := "SELECT "
FOR i := 1 to LEN(aCampos)
    cSql += aCampos[i] + ","
NEXT i
cSql := LEFT(cSql,LEN(cSql)-1)
cSql += " FROM "+cTabla
IF !EMPTY(cWhere)
    cSql += " WHERE "+cWhere
ENDIF
TRY
   oRs := TOleAuto():New("ADODB.Recordset")
   oRs:Open(cSql,oCn)
CATCH oErr
   MsgStop( "Error en la consulta, verifique!!"+CHR(10)+;
            oErr:Operation+CHR(10)+oErr:Description+chr(10)+;
            cSql ,"Error SQL")
   RETURN nil        
END TRY

DEFINE DIALOG oDlg RESOURCE "BUSCAR" TITLE cTabla
oDlg:lHelpIcon := .f.
REDEFINE XBROWSE oBrw ID 111 OF oDlg RECORDSET oRs AUTOCOLS AUTOSORT CELL
oBrw:SetAdo(oRs,.f.,.T.)          
REDEFINE SAY oBrw:oSeek PROMPT "" ID 102 OF oDlg  
PintaBrw(oBrw,0)
oBrw:bKeyDown := {|nKey| IF(nKey==13,oDlg:End(),.t.) }
REDEFINE BUTTON oBot ID 103 OF oDlg ACTION oDlg:end() CANCEL
ACTIVATE DIALOG oDlg ON INIT oBrw:SetFocus() CENTER
RETURN oRs
 

El RC de BUSCAR es

Code: Select all  Expand view  RUN

BUSCAR DIALOG DISCARDABLE 15, 19, 593, 244
STYLE WS_POPUP|DS_MODALFRAME|WS_CAPTION|WS_SYSMENU|WS_VISIBLE
CAPTION "Buscar"
FONT 8, "MS Sans Serif"
{
  CONTROL "&Ok", 103, "Button", WS_TABSTOP, 384, 212, 50, 14
  CONTROL "", 111, "TXBROWSE", WS_TABSTOP|0x00a000a1, 4, 5, 584, 204
  CONTROL "Buscar:", -1, "Static", WS_GROUP, 6, 215, 47, 8
  CONTROL "", 102, "Edit", WS_BORDER|WS_TABSTOP, 34, 212, 339, 14
}

Espero te sirva de inicio

[Armando]

Cesar:

Muchas gracias por el aporte, creo que voy viendo la luz al final del túnel.

He instalado Oracle Database, SqlDeveloper y PLSQL developer en mi mortal PC

Continuaré con mis pruebas

Saludos

[Armando]

César:

Después de descargar e instalar Oracle Database XE, SqlDeveloper e Instant Client.

Intento conectarme a Oracle con la cadena de conexión que amablemente compartiste
y no tengo éxito, me falta algo?

Saludos y muchas gracias por el apoyo

[cmsoft]

Creo que tienes que configurar el ODBC (Digo creo porque hace mucho que no lo hago, y donde uso ORACLE lo hacen los técnicos)
Configurar Microsoft ODBC for Oracle
Panel de control->herramientas administrativas->orígenes de datos ODBC -> Agregar->Microsoft ODBC for Oracle
(Si no aparece la opción, ejecutar C:\WINDOWS\SysWOW64 o System32\Odbcad32.exe->Agregar->Microsoft ODBC for Oracle)
Datos
Origen de datos: oracle
Descripción: oracle
Nombre de usuario: tu_user_name (Aca el usuario que tenes asignado en la base de datos)
Servidor: tu_server (Aca el nombre de tu servidor)

Fijate si con eso te permite acceder con la cadena de conexion que te pase

[Armando]

César y amigos del foro, buen día:

Después de configurar ODBC tanto de usuario como de sistema, no he logrado conectarme,
he intentado con todas esta cadena y no hay éxito.

Code: Select all  Expand view  RUN

   oCn   := oCn := FW_OpenAdoConnection("Data Source=MyOracleDB;Integrated Security=yes")
   oCn   := oCn := FW_OpenAdoConnection("Data Source=MyOracleDB;User Id=SysAdmin;Password=91502127;Integrated Security=no;")
   oCn   := oCn := FW_OpenAdoConnection("Data Source=myOracleDB;User Id=/;")
   oCn   := oCn := FW_OpenAdoConnection("SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LocalHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=system;pwd=91502127;")
   oCn   := FW_OpenAdoConnection("Provider=MSDASQL.1;Password = 91502127;Persist Security Info=False;User ID=SYSTEM;Data Source=Oracle")

   oCn   := FW_OpenAdoConnection("Provider=MSDASQL.1;Password =91502127;Persist Security Info=False;User ID=system;Data Source=Oracle")

   oCn   := oCn := FW_OpenAdoConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CONEXION_CLIENT)));User Id=system;Password=91502127;")

   oCn   := FW_OpenAdoConnection("Provider=MSDASQL.1;Password = 91502127;Persist Security Info=False;User ID=SYSTEM;Data Source=Oracle")

   IF oCn = NIL
      MsgStop( "Error al conectarse a ORACLE !","Error al conectar")
      RETURN nil
   ELSE
      MsgInfo("Conectado !")
   ENDIF

   TRY
      oCnx200:=toleauto():new("adodb.connection")
   CATCH oError
      MsgStop(oError:Operation,"Ado Connection")
   END

   TRY
      oCnx200:ConnectionString   := FW_OpenAdoConnection("Provider=MSDASQL.1;Password =91502127;Persist Security Info=False;User ID=system;Data Source=Localhost")
//      oCnx200:ConnectionString   := "Provider=OraOLEDB.Oracle;Data Source=localhost:1521/cbs;User ID=system;Password=91502127;"
      oCnx200:Open()
   CATCH oError
      MsgStop(oError:Operation + "  !","Conexion con Oracle")
   END
 

Al configurar el ODBC clic en el botón TEST CONNECTION y funciona al 100% pero mi código no

Alguna hebra?

Saludos

[cmsoft]

Armando:
cual es el contenido de tu archivo C:\oracle\product\10.2.0\client_1\network\ADMIN\TNSNAMES.ORA (La carpeta dependera de tu version y donde instalaste el cliente)
O si puedes capturar la pantalla del ODBC para ver que ambos coincidan.
Sino, envíame un mail a cmsoft70 arroba gmail punto com y veo si puedo darte una mano...

[Armando]

Cesar:

De antemano te agradezco tu apoyo, esta es la configuración de ODBC

[url]

[/url]

Saludos