Aug 10, 2023

Error Powershell "Attempt to load Oracle client libraries threw BadImageFormatException"


If you have this issue at connect to oracle database from powershell using [Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient"), error:

"Attempt to load Oracle client libraries threw BadImageFormatException.  This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed."

That is because you have oracle client library 32 bits version on windows SO 64 bits version.

The Solution for this is use the powershell 32 bits version. So you must to replace from your command line:

powershell -executionpolicy bypass -file script_powershell.ps1


to this:

C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe  -executionpolicy bypass -file script_powershell.ps1


That's all, i hope this will help.

NOTE: pls don't forget use try/catch commands in your powershell script for view issues or errors like this case, example:

    catch{
        echo "ERROR"
        Write-Host $_
        Write-Host $_.ScriptStackTrace
    }

Jun 29, 2023

Oracle Graph (part 3)

 

Basic Example for Graph Creation and Web Visualisation 


Graph Creation using graph Client


# This Example is based on doc:

https://docs.oracle.com/en/database/oracle/property-graph/21.2/spgdg/quick-start-create-query-and-analyze-graph-memory-graph-server-pgx.html#GUID-97DAD3CD-BA1F-4728-AC94-1249BA3DF05C



# Donwload HR example schemas (We need use HR Scheme):

https://github.com/oracle/db-sample-schemas/releases/tag/v19.2



# NOTE: Remember you can use sqdeveloper for oracle graphs. Check this: 

# https://medium.com/oracledevs/6db0e56aa822



# Review if the user has this role on the database

 GRANT PGX_SESSION_ADD_PUBLISHED_GRAPH to ORAGRAPH  ;


# Create chart using opg4j using oragraph user

export JAVA11_HOME=/usr/lib/jvm/jre-11

cd /home/oragraph/oracle-graph-client-23.2.1/bin

opg4j --base_url http://localhost:7007 -u ORAGRAPH


Every command from now it will be executed on opg4j client:

opg4j> 


// Create a graph with employees, departments, and "employee works at department",

// by executing a CREATE PROPERTY GRAPH statement.

// The following statement creates a graph in the in-memory graph server (PGX):


String statement =

"CREATE PROPERTY GRAPH hr_simplified "

+ " VERTEX TABLES ( "

+ " hr.employees LABEL employee "

+ " PROPERTIES ARE ALL COLUMNS EXCEPT ( job_id, manager_id, department_id ), "

+ " hr.departments LABEL department "

+ " PROPERTIES ( department_id, department_name ) "

+ " ) "

+ " EDGE TABLES ( "

+ " hr.employees AS works_at "

+ " SOURCE KEY ( employee_id ) REFERENCES employees "

+ " DESTINATION departments "

+ " PROPERTIES ( employee_id ) "

+ " )";

session.executePgql(statement);


// To get a handle to the graph, execute:

PgxGraph g = session.getGraph("HR_SIMPLIFIED");




// Run a few PGQL queries

// You can use this handle to run PGQL queries on this graph.

// For example, to find the department that "Nandita Sarchand" works for, execute:

String query =

"SELECT dep.department_name "

+ "FROM MATCH (emp:Employee) -[:works_at]-> (dep:Department) "

+ "WHERE emp.first_name = 'Nandita' AND emp.last_name = 'Sarchand' "

+ "ORDER BY 1";


PgqlResultSet resultSet = g.queryPgql(query);

resultSet.print();


+-----------------+

| department_name |

+-----------------+

| Shipping |

+-----------------+


// To get an overview of the types of vertices and their frequencies, execute:


String query =

"SELECT label(n), COUNT(*) "

+ "FROM MATCH (n) "

+ "GROUP BY label(n) "

+ "ORDER BY COUNT(*) DESC";


PgqlResultSet resultSet = g.queryPgql(query);

resultSet.print();


+-----------------------+

| label(n) | COUNT(*) |

+-----------------------+

| EMPLOYEE | 107 |

| DEPARTMENT | 27 |

+-----------------------+



// To get an overview of the types of edges and their frequencies, execute:


String query =

"SELECT label(n) AS srcLbl, label(e) AS edgeLbl, label(m) AS dstLbl, COUNT(*) "

+ "FROM MATCH (n) -[e]-> (m) "

+ "GROUP BY srcLbl, edgeLbl, dstLbl "

+ "ORDER BY COUNT(*) DESC";

PgqlResultSet resultSet = g.queryPgql(query);

resultSet.print();


+---------------------------------------------+

| srcLbl | edgeLbl | dstLbl | COUNT(*) |

+---------------------------------------------+

| EMPLOYEE | WORKS_AT | DEPARTMENT | 106 |

+---------------------------------------------+




// Execute algorithms and query the algorithm results

// Now that you have the graph in memory, you can run each built-in

// algorithms using a single API invocation. For example, for pagerank:

analyst.pagerank(g)



// As you can see from the preceding outputs, 

// each algorithm created a new vertex property

// on the graph holding the output of the algorithm. 

// To print the most important

// people in the graph (according to pagerank), 

// you can run the following query


session.queryPgql("select m.FIRST_NAME, m.LAST_NAME, m.pagerank from HR_SIMPLIFIED match (m:EMPLOYEE) where m.FIRST_NAME = 'Nandita' ").print().close()

+---------------------------------------------------+

| m.FIRST_NAME | m.LAST_NAME | m.pagerank |

+---------------------------------------------------+

| Nandita | Sarchand | 0.001119402985074627 |

+---------------------------------------------------+




// In the following example, we order departments by their pagerank value.

// Departments with higher pagerank values have more employees.


session.queryPgql("select m.DEPARTMENT_NAME, m.pagerank from HR_SIMPLIFIED match (m:DEPARTMENT) order by m.pagerank ").print().close();



+----------------------------------------------+

| m.DEPARTMENT_NAME | m.pagerank |

+----------------------------------------------+

| Manufacturing | 0.001119402985074627 |

| Construction | 0.001119402985074627 |

| Contracting | 0.001119402985074627 |

| Operations | 0.001119402985074627 |

| IT Support | 0.001119402985074627 |

| NOC | 0.001119402985074627 |

| IT Helpdesk | 0.001119402985074627 |

| Government Sales | 0.001119402985074627 |

| Retail Sales | 0.001119402985074627 |

| Recruiting | 0.001119402985074627 |

| Payroll | 0.001119402985074627 |

| Treasury | 0.001119402985074627 |

| Corporate Tax | 0.001119402985074627 |

| Control And Credit | 0.001119402985074627 |

| Shareholder Services | 0.001119402985074627 |

| Benefits | 0.001119402985074627 |

| Human Resources | 0.0020708955223880596 |

| Administration | 0.0020708955223880596 |

| Public Relations | 0.0020708955223880596 |

| Marketing | 0.003022388059701493 |

| Accounting | 0.003022388059701493 |

| Executive | 0.003973880597014925 |

| IT | 0.005876865671641792 |

| Purchasing | 0.006828358208955224 |

| Finance | 0.006828358208955224 |

| Sales | 0.03347014925373134 |

| Shipping | 0.043936567164179076 |

+----------------------------------------------+





// Share the Graph with Other Sessions

// After you load the graph into the server,

// you can use the publish() API to make the graph 

// visible to other sessions,

// such as the graph visualization #session. For example:


g.publish(VertexProperty.ALL, EdgeProperty.ALL)


// Next time you connect you can access the graph kept on memory without re-loading it,

// if the graph server has not been shutdown or restarted between logins.

// PgxGraph g = session.getGraph("HR_SIMPLIFIED");



// Get sessionid

session

session ==> PgxSession[ID=e5ec617f-28b6-450a-b6f5-eba38242c569,source=pgxShell]




# Visualizate graph using user,pass and pgx id session: 

go to http://localhost:7007/ui/ (or http://localhost:9090/graphviz if you are using your tomcat in the other install example)


# Enter values 

user:oragraph

pass:oracle

(advanced options)

graph server: http://localhost:7007

pgxid: (enter last value for my example: e5ec617f-28b6-450a-b6f5-eba38242c569 )





# in case of errors review:

tail -f /var/log/oracle/graph/pgx-server.log



# On the interface http://localhost:7007/ui/ execute this command:

# on the button "list of graphs" beside to "play" button

# you must to select graph "HR_SIMPLIFIED"


# The command for execute is:

SELECT e

FROM MATCH ()-[e]->() on  HR_SIMPLIFIED

LIMIT 100


# for execute chart press the "PLAY" button


Now you can see the graph created using graph client but now on the web interface:





That’s all. I hope this little tutorial could be helpful for you.

Cheers.

Felipe Donoso.