MySQL

MySQL: Assigned Calls to User based on Parent Account or Lead

Posted on

Select List of Calls to Update: SELECT * FROM calls LEFT JOIN accounts ON calls.parent_id = accounts.id LEFT JOIN leads ON calls.parent_id = leads.id WHERE ( accounts.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’ OR leads.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’ ) ;   Update Query: UPDATE calls x LEFT JOIN accounts y ON x.parent_id = y.id LEFT JOIN leads z ON x.parent_id […]

MySQL

MySQL: UPDATE or DELETE with JOIN Operators (Contacts)

Posted on

Same as previous post but this time with Accounts instead of Leads. List the Contacts related to the Accounts this employee has assigned: SELECT contacts.* FROM accounts INNER JOIN accounts_contacts ON accounts.id = accounts_contacts.account_id INNER JOIN contacts ON accounts_contacts.contact_id = contacts.id WHERE accounts.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’ AND contacts.deleted = 1 ; *** Now that I have done […]

MySQL

MySQL: UPDATE or DELETE with JOIN Operators

Posted on

I’m trying to update some records on Table C (contacts.assigned_used), but I have a Join from Table A (leads) and passing through in intermediary Table C. The query to show the filtered Table C (which is what I want to update) is: SELECT contacts.* FROM leads INNER JOIN leads_contacts_c ON leads.id = leads_contactsleads_ida INNER JOIN […]

OpenBravo

PgSQL: List and Remove Triggers

Posted on

I want to delete directly on the PgSQL database on my OpenBravo application but it has a los of triggers that prevent certain actions, first I want to list them and after that will want to disable them. List: SELECT event_object_table,trigger_name,event_manipulation,action_statement,action_timing FROM information_schema.triggers WHERE event_object_table=’m_inventoryline‘ ORDER BY event_object_table,event_manipulation Disable: DROP TRIGGER m_inventoryline_trg ON m_inventoryline;

OpenBravo

PgSQL: Cannot Delete Line on Table.

Posted on

This happens when I try to manually delete a record on the Database   ERROR: update or delete on table “m_inoutline” violates foreign key constraint “m_inoutline_canceled_inoutline” on table “m_inoutline” DETAIL: Key (m_inoutline_id)=(8310BD9D63A94DB5A2294EC8E48C6C9F) is still referenced from table “m_inoutline”. ********** Error ********** ERROR: update or delete on table “m_inoutline” violates foreign key constraint “m_inoutline_canceled_inoutline” on table […]

Jasper Reports

PgSql: Cannot use Extract function inside a Where Clause

Posted on

For Exmple:   AND (date_part(‘YEAR’,fin_finacc_transaction.statementdate) = $P{Año}) or AND ((extract(Year from  fin_finacc_transaction.statementdate) LIKE $P{Año}) OR $P{Año} = ‘ALL’) Neither of these work, I guess it has something to do with the Parameter type, as it works when I use a number instead of the Parameter. See error Log. net.sf.jasperreports.engine.JRException: Error executing SQL statement for : […]

MySQL

MySQL: External Connection to Database Blocked

Posted on

Cannot establish an external MySQL connection to the server. Error 1.  Error 10061: connect to MySQL server failed Fixed it using the following solution (https://help.ubuntu.com/community/ApacheMySQLPHP) Solution 1. Set mysql bind address Before you can access the database from other computers in your network, you have to change its bind address. Note that this can be a security problem, […]

PostgreSQL

PostgreSQL: Relevant Commands

Posted on

Connecting to Postgress Shell psql -U postgres -d postgres List Users select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig Change User Password   ALTER USER colin WITH PASSWORD ‘letmein’; Reference: http://www.davidpashley.com/articles/postgresql-user-administration/   MANAGE POSTGRES   PhpPGadmin : Install https://help.ubuntu.com/community/phpPgAdmin No se puede logear: http://www.unixmen.com/install-postgresql-and-phppgadmin-on-debian-7-wheezy/ Usuario […]

PostgreSQL

PostgreSQL: Not Listening

Posted on

http://stackoverflow.com/questions/12286886/postgresql-server-doesnt-listen ERROR 1 could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host “xxx.xxx.xxx.xxx” and accepting TCP/IP connections on port 5432? listen_addresses = ‘*’ in /etc/postgresql/9.1/main/postgresql.conf. ERROR 2 FATAL: no pg_hba.conf entry for host “xxx.xx.xxx.xxx”, user “postgres”, database “postgres”, SSL on FATAL Edit pg_hba.conf Add the following line: host     […]