Latest topics for the forum "SQL Power DQguru Technical Support"

Error: Couldn't save connection information

ErinKlanderman — GMT

Exception Type: java.io.FileNotFoundException
Detail String: C:\Documents and Settings\Administrator.ASAP\My Documents\desktop.ini (access is deined)

When trying to save my database connection I recieve this error.

Any help would be greatly appreciated!
Thanks-
Erin

How to Connect SQL Server 2008 with DQGURU

sabbir — GMT

Hello,
Can anyone help me to make a connection with SQL Server 2008 in my local pc. My SQL Server is in windows authentication mode. What will be the hostname. Please tell me step by step process

Metaphone or Soundex??

chrisDC — GMT

I am trying to use a Metaphone transform, and it looks like the preview and the merge is returning only the four-character SOUNDEX, and not the metaphone.

SQL Server 2008
Input is nvarchar(100)
Step one, convert to lowercase
Step two, retain characters only [a-z]
Step three, metaphone.

Metaphone is returning only the four-character soundex.

help??

Performance Tuning for Cleansing Projects

MarcdParadis — GMT

Any suggestions on performance tuning? I am trying to cleanse 4 columns in a 360,000 row table. The cleanse algorithm is to first convert to all uppercase and then to retain characters with the regular expression [0-9A-Z].

Should I only plug the four columns from pre-cleanse to post-cleanse? or should I plug all 15 columns in the table from pre- to post-cleanse?

I've already figured out that DQ Guru runs much faster when it is installed and run local to the DB (in my case SQL Server 2005). But are there any other tips?

teradata

sheridany — GMT

Will DQGuru work against Teradata? What is the repository qualifier? I have never heard that term and I tried several possibilities. I can actually connect in test connection but it fails when I go to login.

SAPDB/MAXDB Support

Saif — GMT

I hope all of you are doing well. Has anyone had any success connecting DQguru to MaxDB.

I have been able to create a JDBC that passes the test connection but I am unable to actually retrieve data from the DB because a repository does not exist.

Can I create a repository in MaxDB if the test connection is successful? Actually what is the repository for?

RuntimeException: Cannot convert Oracle Timestamp class

SJay — GMT

----------------------------------------
SQL Power DQguru version: 0.9.7
Operating System: Windows XP 5.1 (x86)
Runtime Environment: Java(TM) SE Runtime 1.6.0_03-b05
Database: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
---------------------------------------
I have test project to find duplicates. When I run the Match Engine without any transformations, it runs fine. However, when I add a transformation, it fails with the following exception:

java.lang.RuntimeException: java.sql.SQLException: Cannot convert Oracle Timestamp class because the an exception was encountered

If I remove the transformation and run Match Engine, it works fine.

I wanted to attach the log file but Attach Files button does not seem to work, so log file contents are pasted below.

Thanks,
Sameer
------------------------------------------------------------------
2010-03-11 16:01:57,854 INFO Checking Match Engine Preconditions
2010-03-11 16:02:01,731 INFO Starting Match Engine
2010-03-11 16:02:01,997 INFO Searching for matches
2010-03-11 16:02:01,997 DEBUG 0/7338: Running transformation New Transformation 1
2010-03-11 16:02:01,997 DEBUG Order of processing: [COMPANY: opened=false committed=false rolledBack=true [Inputs:] [Outputs: ] [Parameters: ], Substring: opened=false committed=false rolledBack=true [Inputs: ] [Outputs: ] [Parameters: ], Transformation Results: opened=false committed=false rolledBack=true [Inputs: null] [Outputs:] [Parameters: ]]
2010-03-11 16:02:01,997 DEBUG Opening MungeStep COMPANY
2010-03-11 16:02:01,997 DEBUG Attempting to execute input query: SELECT
COMPANY_ID,
COMPANY_NAME,
DATE_CREATED,
DATE_CHANGED,
WEB_SITE,
DATE_DELETED,
COMPANY_NOTE,
COMPANY_DESCRIPTION,
COMPANY_BANNER_URL,
PSFT_CUST_ID,
PSFT_CUST_STATUS,
PSFT_CUST_STATUS_DATE,
SFA_ACCOUNT_ID,
PARENT_COMPANY_ID,
PO_REQUIRED,
COMPANY_ONHOLD,
CURRENCY_CODE
FROM ECOWADMIN.COMPANY
WHERE COMPANY_NAME = COMPANY_NAME
2010-03-11 16:02:02,012 DEBUG ResultSet fetch size is: 100
2010-03-11 16:02:02,012 DEBUG Opening MungeStep Substring
2010-03-11 16:02:02,012 DEBUG Opening MungeStep Transformation Results
2010-03-11 16:02:02,012 DEBUG Searching for MungeStepOuput with name COMPANY_ID
2010-03-11 16:02:02,028 DEBUG Found MungeStepOuput
2010-03-11 16:02:02,028 DEBUG COMPANY Inputs:
2010-03-11 16:02:02,028 DEBUG Rolling back COMPANY
2010-03-11 16:02:02,028 DEBUG Closing MungeStep COMPANY
2010-03-11 16:02:02,028 DEBUG Closing MungeStep Substring
2010-03-11 16:02:02,028 DEBUG Closing MungeStep Transformation Results
2010-03-11 16:02:02,028 ERROR 0/7338: Match Engine failed
2010-03-11 16:02:02,263 ERROR Error during engine run
java.lang.RuntimeException: java.sql.SQLException: Cannot convert Oracle Timestamp class because the an exception was encountered
at ca.sqlpower.matchmaker.MatchEngineImpl.call(MatchEngineImpl.java:254)
at ca.sqlpower.matchmaker.swingui.engine.EngineWorker.doStuff(EngineWorker.java:117)
at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:104)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Cannot convert Oracle Timestamp class because the an exception was encountered
at ca.sqlpower.sql.jdbcwrapper.OracleResultSetDecorator.convertTimestamp(OracleResultSetDecorator.java:118)
at ca.sqlpower.sql.jdbcwrapper.OracleResultSetDecorator.getTimestamp(OracleResultSetDecorator.java:74)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:211)
at ca.sqlpower.matchmaker.munge.SQLInputStep.doCall(SQLInputStep.java:118)
at ca.sqlpower.matchmaker.munge.AbstractMungeStep.call(AbstractMungeStep.java:646)
at ca.sqlpower.matchmaker.munge.MungeProcessor.call(MungeProcessor.java:100)
at ca.sqlpower.matchmaker.munge.MungeProcessor.call(MungeProcessor.java:62)
at ca.sqlpower.matchmaker.MatchEngineImpl.mungeAndMatch(MatchEngineImpl.java:281)
at ca.sqlpower.matchmaker.MatchEngineImpl.call(MatchEngineImpl.java:229)
... 3 more
Caused by: java.lang.NullPointerException
at ca.sqlpower.sql.jdbcwrapper.OracleResultSetDecorator.convertTimestamp(OracleResultSetDecorator.java:98)
... 11 more

Frequently Asked Questions

Admin — GMT

Before posting to these forums, make sure that you consult the FAQ section of our website, available at :

[url]http://www.sqlpower.ca/page/FAQ[/url]

Google Address Lookup broken?

nls — GMT

Hi!

It seems Google Address Lookup may be broken :?

[code]2010-01-07 17:09:40,946 DEBUG Order of processing: [a_buildings1_dub: opened=false committed=false rolledBack=true [Inputs:] [Outputs: ] [Parameters: ], Upper Case: opened=false committed=false rolledBack=true [Inputs: ] [Outputs: ] [Parameters: ], Translate Words: opened=false committed=false rolledBack=true [Inputs: ] [Outputs: ] [Parameters: ], String constant: opened=false committed=false rolledBack=true [Inputs:] [Outputs: ] [Parameters: ], String constant: opened=false committed=false rolledBack=true [Inputs:] [Outputs: ] [Parameters: ], Concat: opened=false committed=false rolledBack=true [Inputs: null] [Outputs: ] [Parameters: ], Google Maps Address Lookup: opened=false committed=false rolledBack=true [Inputs: ] [Outputs:

] [Parameters:

], a_buildings1_dub: opened=false committed=false rolledBack=true [Inputs: ] [Outputs:] [Parameters: ]]
2010-01-07 17:09:40,947 DEBUG Opening MungeStep a_buildings1_dub
2010-01-07 17:09:40,947 DEBUG Attempting to execute input query: SELECT
objectid,
bld_type,
name_e,
source,
shape_leng,
dgis_id,
x_coord,
y_coord,
shape_length,
shape_area,
name_looked_up,
lat,
lng
FROM geostage.public.a_buildings1_dub
WHERE name_e is not null
2010-01-07 17:09:40,955 DEBUG ResultSet fetch size is: 100
2010-01-07 17:09:40,956 DEBUG Opening MungeStep Upper Case
2010-01-07 17:09:40,956 DEBUG Opening MungeStep Translate Words
2010-01-07 17:09:40,958 DEBUG Opening MungeStep String constant
2010-01-07 17:09:40,958 DEBUG Opening MungeStep String constant
2010-01-07 17:09:40,960 DEBUG Opening MungeStep Concat
2010-01-07 17:09:40,962 DEBUG Opening MungeStep Google Maps Address Lookup
2010-01-07 17:09:40,963 DEBUG Opening MungeStep a_buildings1_dub
2010-01-07 17:09:40,963 DEBUG a_buildings1_dub Inputs:
2010-01-07 17:09:40,963 DEBUG a_buildings1_dub Outputs: [ objectid: 13177 ] [ bld_type: 6 ] [ name_e: Courts ] [ source: Photogrammetry ] [ shape_leng: 546.42369764 ] [ dgis_id: 12774 ] [ x_coord: 330925.51424 ] [ y_coord: 2793497.10666 ] [ shape_length: 546.398691871296 ] [ shape_area: 10886.6295979211 ] [ name_looked_up: null ] [ lat: null ] [ lng: null ]
2010-01-07 17:09:40,963 DEBUG Upper Case Inputs: [ name_e: Courts ]
2010-01-07 17:09:40,964 DEBUG Upper Case Outputs: [ upperCaseOutput: COURTS ]
2010-01-07 17:09:40,964 DEBUG Translate Words Inputs: [ upperCaseOutput: COURTS ]
2010-01-07 17:09:40,965 DEBUG Translate Words Outputs: [ translateWordOutput: COURTS ]
2010-01-07 17:09:40,965 DEBUG String constant Inputs:
2010-01-07 17:09:40,966 DEBUG String constant Outputs: [ Value: Dubai ]
2010-01-07 17:09:40,967 DEBUG String constant Inputs:
2010-01-07 17:09:40,967 DEBUG String constant Outputs: [ Value: UAE ]
2010-01-07 17:09:40,967 DEBUG Concat Inputs: [ name_e: Courts ] [ Value: Dubai ] [ Value: UAE ] [ null ]
2010-01-07 17:09:40,967 DEBUG Concat Outputs: [ concatOutput: Courts, Dubai, UAE ]
2010-01-07 17:09:40,967 DEBUG Google Maps Address Lookup Inputs: [ concatOutput: Courts, Dubai, UAE ]
2010-01-07 17:09:41,348 DEBUG Address Lookup Response for "Courts, Dubai, UAE": {
"name": "Courts, Dubai, UAE",
"Status": {
"code": 200,
"request": "geocode"
},
"Placemark": [ {
"id": "p1",
"address": "Dubai Courts 1 - Dubai - United Arab Emirates",
"AddressDetails": {
"Accuracy" : 9,
"AddressLine" : [ "Dubai Courts 1" ]
},
"ExtendedData": {
"LatLonBox": {
"north": 25.2501966,
"south": 25.2439014,
"east": 55.3237266,
"west": 55.3174314
}
},
"Point": {
"coordinates": [ 55.3205790, 25.2470490, 0 ]
}
}, {
"id": "p2",
"address": "Dubai Courts 2 - Dubai - United Arab Emirates",
"AddressDetails": {
"Accuracy" : 9,
"AddressLine" : [ "Dubai Courts 2" ]
},
"ExtendedData": {
"LatLonBox": {
"north": 25.2511176,
"south": 25.2448224,
"east": 55.3225256,
"west": 55.3162304
}
},
"Point": {
"coordinates": [ 55.3193780, 25.2479700, 0 ]
}
} ]
}

2010-01-07 17:09:41,350 DEBUG Rolling back a_buildings1_dub
2010-01-07 17:09:41,351 DEBUG Closing MungeStep a_buildings1_dub
2010-01-07 17:09:41,351 DEBUG Closing MungeStep Upper Case
2010-01-07 17:09:41,351 DEBUG Closing MungeStep Translate Words
2010-01-07 17:09:41,351 DEBUG Closing MungeStep String constant
2010-01-07 17:09:41,351 DEBUG Closing MungeStep String constant
2010-01-07 17:09:41,352 DEBUG Closing MungeStep Concat
2010-01-07 17:09:41,352 DEBUG Closing MungeStep Google Maps Address Lookup
2010-01-07 17:09:41,352 DEBUG Closing MungeStep a_buildings1_dub
2010-01-07 17:09:41,352 ERROR 0/1: Cleanse Engine failed
2010-01-07 17:09:42,079 ERROR Error during engine run
java.lang.RuntimeException: org.json.JSONException: JSONObject["Country"] not found.
at ca.sqlpower.matchmaker.CleanseEngineImpl.call(CleanseEngineImpl.java:231)
at ca.sqlpower.matchmaker.swingui.engine.EngineWorker.doStuff(EngineWorker.java:117)
at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:60)
at java.lang.Thread.run(Thread.java:637)
Caused by: org.json.JSONException: JSONObject["Country"] not found.
at org.json.JSONObject.get(JSONObject.java:422)
at org.json.JSONObject.getJSONObject(JSONObject.java:516)
at ca.sqlpower.matchmaker.munge.GoogleAddressLookup.doCall(GoogleAddressLookup.java:146)
at ca.sqlpower.matchmaker.munge.AbstractMungeStep.call(AbstractMungeStep.java:633)
at ca.sqlpower.matchmaker.munge.MungeProcessor.call(MungeProcessor.java:100)
at ca.sqlpower.matchmaker.munge.MungeProcessor.call(MungeProcessor.java:58)
at ca.sqlpower.matchmaker.CleanseEngineImpl.call(CleanseEngineImpl.java:214)
... 3 more[/code]

Any work-around for this?
I'm using 0.9.5 for OS X and I'm 'build > make > install' phobic :(

Oracle 9i JDBC driver

rwaqar — GMT

I am using Oracle 9i db and looking for jdbc driver for that. where can I find it and what is the name of the file.

About command call function in DQguru

glede — GMT

I try to call a command from out side but I keeping get this error on the log:

1 [main] INFO ca.sqlpower.architect.ArchitectUtils - Log4J configured successfully
56 [AWT-EventQueue-0] INFO ca.sqlpower.sql.PlDotIni - Beginning to read/merge new pl.ini data
98 [AWT-EventQueue-0] INFO ca.sqlpower.sql.PlDotIni - Finished reading file.
99 [AWT-EventQueue-0] INFO ca.sqlpower.sql.PlDotIni - Beginning to read/merge new pl.ini data
139 [AWT-EventQueue-0] INFO ca.sqlpower.sql.PlDotIni - Finished reading file.
1560 [AWT-EventQueue-0] INFO ca.sqlpower.sql.SPDataSource - Driver Class org.hsqldb.jdbcDriver loaded without exception
1930 [AWT-EventQueue-0] INFO ca.sqlpower.matchmaker.dao.hibernate.MatchMakerHibernateSessionImpl - Connected to repository database.
1941 [AWT-EventQueue-0] INFO ca.sqlpower.matchmaker.dao.hibernate.MatchMakerHibernateSessionImpl - Database product name: HSQL Database Engine
1941 [AWT-EventQueue-0] INFO ca.sqlpower.matchmaker.dao.hibernate.MatchMakerHibernateSessionImpl - Database product version: 1.8.0
1941 [AWT-EventQueue-0] INFO ca.sqlpower.matchmaker.dao.hibernate.MatchMakerHibernateSessionImpl - Database driver name: HSQL Database Engine Driver
1941 [AWT-EventQueue-0] INFO ca.sqlpower.matchmaker.dao.hibernate.MatchMakerHibernateSessionImpl - Database driver version: 1.8.0
1968 [AWT-EventQueue-0] ERROR ca.sqlpower.security.PLSecurityManager - WARNING: YOU ARE USING THE PL SECURITY MANAGER IN INSECURE MODE!!!
1971 [AWT-EventQueue-0] ERROR ca.sqlpower.security.PLUser - WARNING: YOU ARE USING THE PL USER IN INSECURE MODE!!!
2538 [AWT-EventQueue-0] INFO ca.sqlpower.sql.SPDataSource - Driver Class org.hsqldb.jdbcDriver loaded without exception

I think it related to some security issue, but I don't know how to solve the problem.

Error when creating a new connection to sql server 2008

anildsouza — GMT

Hi

Im trying to connect to sql server 2008. However I keep getting this error.

com.microsoft.sqlserver.jdbc.SQLServerException: The server version is not supported. The target server must be SQL Server 2000 or later.

CSV Files Supported???

SQLPower_imus — GMT

Hi All,

what are the data sources supported by DQGuru?
whether it supports .csv files as input ??
Any other formats supported???

Regards,

imus

error with column names length creating a cleansing job in oracle

james_paniagua — GMT

in oracle the maximun column length is 30 characters and the adreess corraction project needs to create a table with more length, con you tell me what to do, please

Error starting at line 1 in command:
CREATE TABLE EMPLEADO_address_pool (
src_addr_key_col_0 VARCHAR(15),
input_address_line1 VARCHAR(70),
input_address_line2 VARCHAR(70),
input_municipality VARCHAR(30),
input_province VARCHAR(30),
input_country VARCHAR(30),
input_postal_code VARCHAR(10),
output_country VARCHAR(30),
output_delivery_installation_name VARCHAR(50),
output_delivery_installation_type VARCHAR(5),
output_direction_prefix BOOLEAN,
output_failed_parsing_string VARCHAR(150),
output_general_delivery_name VARCHAR(70),
output_lock_box_number VARCHAR(5),
output_lock_box_type VARCHAR(6),
output_municipality VARCHAR(30),
output_postal_code VARCHAR(10),
output_province VARCHAR(30),
output_rural_route_number VARCHAR(5),
output_rural_route_type VARCHAR(2),
output_street_direction VARCHAR(5),
output_street_name VARCHAR(30),
output_street_number INTEGER,
output_street_number_suffix VARCHAR(6),
output_street_number_suffix_separate BOOLEAN,
output_street_type VARCHAR(11),
output_street_type_prefix BOOLEAN,
output_suite VARCHAR(6),
output_suite_prefix BOOLEAN,
output_suite_type VARCHAR(15),
output_unparsed_address VARCHAR(150),
output_type VARCHAR(8),
output_urban_before_rural BOOLEAN,
output_valid BOOLEAN
)
ORA-00972: identificador demasiado largo
00972. 00000 - "identifier is too long"
*Cause: An identifier with more than 30 characters was specified.
*Action: Specify at most 30 characters.

Need some clarifications on DQGuru..

Ram Mohan T — GMT

Hi All,
I need some calrifications on DQGuru. Can anyone please help me in addressing this.

1) How to include "Business Rules" in DQGuru cleansing project. Eg of Business Rule is - I have a three columns in a table --> Status, Installation date, Disconnected date.

If my status is "Open" my Disconnected date should be null, else if my status is "Closed" my Disconnected date should be less than sysdate, else if status is "WIP" my Installation date should be less than sysdate.

2) Is it possible to create a "User Defined Transformer" in DQGuru? or we have only options to use the inbuilt transformers ?

3) Is it possible to include multiple source tables in the same transformer in a cleansing project?

4) Does the Address Database for countries other than canada are free of cost?

5) For a cleansing project, if a table doesnt have a unique index cant we use it?

Please help me in addressing these queries.

Thanks in Advance,
Ram Mohan T