JDBC Thin Client: Wallet
Hi, I have a question regarding Oracle Thin Dynamic Account. How could I connect to Oracle ATP with a wallet? Oracle says, the the wallet needs to extracted in a directory. Then I can use the Custom JDBC URL with jdbc:oracle:thin:@dbname_high?TNS_ADMIN=/Users/test/wallet_dbname How could I create a directory in the file section and how is the link to the directory? Has some connected an ATP sucessfully with SnapLogic? Many thanks, Birthe
Unable to get all records from mysql database in Generic JDBC execute or Select snap
Hello, I am seeing a different behavior in Generic JDBC execute or Select snap when it has upstream and downstream. I have a table with 10000 records and the query used in both snaps is "select * from . where pipl id =‘1’ " and this query is giving same count in mysql and when the snap(execute,select) is not having any input/output connected but if there is a input/output, i am not getting same count in output and the record count is not consistent. Sometimes i get 10,20 … but not 10000. Tried all troubleshooting steps like clearing cache, re deploying snap, restarting snap plex… but no luck. Snap version - com-snaplogic-snaps-jdbc-execute-1-main19844 Did anyone face this issue? Appreciate any solution here. Thank you in advance. Harish
SQL server snap - Unexpected Re-throwing connection error
Hi Community, I was trying to access SQL server via snaplogic but I wasn’t successful as I was facing an issue with the connection. Below are the two error messages when I tried with different plexes. Error while validating with groundplex Failed to validate account: Failed to retrieve a database connection. Cause: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “Unexpected rethrowing”. (Reason: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “Unexpected rethrowing”.; Resolution: Address the reported issue.) Error while validating with cloudplex: Failed to validate account: Failed to retrieve a database connection. Cause: The TCP/IP connection to the host ***********************, port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”. (Reason: The TCP/IP connection to the host ****************************, port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.; Resolution: Address the reported issue.) JDBC drives that I used: mssql-jdbc-9.4.0.jre8 mssql-jdbc-7.4.1.jre8 mssql-jdbc-10.2.0.jre8.jar mssql-jdbc-7.4.1.jre11.jar* Details: I would really appreciate if anyone could help me on this. Thank you.Heroku PostgreSQL JDBC Account Setup
I’m trying to configure the JDBC Account to work with my Heroku Postgresql account: But I’m getting the following error: Failed to validate account: java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgres://ybukpksfvupwgu:e5786b479f8736d389cb4818bc5961f9ce9c4454ce333b0b84777844e8a419c0@ec2-3-234-109-123.compute-1.amazonaws.com:5432/d409u29joqsfjd Cause: java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgres://ybukpksfvupwgu:e5786b479f8736d389cb4818bc5961f9ce9c4454ce333b0b84777844e8a419c0@ec2-3-234-109-123.compute-1.amazonaws.com:5432/d409u29joqsfjd (Reason: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgres://ybukpksfvupwgu:e5786b479f8736d389cb4818bc5961f9ce9c4454ce333b0b84777844e8a419c0@ec2-3-234-109-123.compute-1.amazonaws.com:5432/d409u29joqsfjd; Resolution: Driver loading possibly failed, please ensure the driver is correctly configured) What am I missing?
Connection to legacy Sybase DBMS sometimes hangs
On a Windows Groundplex, we are using the “Generic JDBC - Execute” snaps to issue several different kinds of queries to a legacy Sybase Adaptive Server Anywhere 9 database. (Also sometimes called Sybase “SQL Server Anywhere.”) We are using the connection options recommended by Snaplogic in the online documentation. [TBD: put a link to that here.] Every once in a while, a query that has previously worked just fine will “hang” the connection. Once this has happened, attempts to query via that connection go unresolved and time out. We believe that when the problem happens, what may be happening is that the query is either failing or else not finishing in time, and that the connection is left open in an “off hook” state, with the result set still pending. Subsequent queries on such a channel wouldn’t get acknowledged because it would just be sitting there waiting for the previous action to complete… which never does. Has anyone else experienced this with a Sybase connection? (Or with any other non-standard legacy database connection?) Any ideas how to troubleshoot and fix this?
Inserting timestamp string to DB2 database table
Hello, I am having trouble with writing to a db2 database table using the jdbc generic insert, or being more specific writing a timestamp format string to a timestamp column. I have to read from a csv fle and insert the rows into a db2 database table I can perfectly do so by skipping the timestamp column but that will cause the field in the table record to take the current timestamp value(which obviously is not the intended outcome) when I try it with the date value i get the next error “Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch”, I only get this error when including the date in the mapper output to the jdbc insert. the initial date&time string is in his format: “M/DD/YYYY HH.MM” and I format it in two steps(mappers) first: Date.parse($[‘Date & Time’]).toLocaleDateTimeString() OUTPUT=>“2018-08-21T16:48:00.000” second: ($DATE_PROCESSED.slice(0,10)+“-”+$DATE_PROCESSED.slice(11).replaceAll (“:”,“.”)+“000”).toString() OUTPUT=>“2018-08-21-16.48.00.000000” this is the format(TIMESTAMP) that is stored in the database but for some reason the insert still fails only when I include it in the mapper and I have tested the same string that the mapper gives me by inserting through query and it works fine… I don’t know what could be messing in the middle that I am not seeing… I’d appreciate any insights you can have on the way I am managing things. THANK YOU.
How to use H2 In-Memory Database with SnapLogic
Hello, I recently helped a customer solve a problem where they need to read a few CSV files and filter certain rows so they go to different people via email with an Excel multi-sheet spreadsheet. The issue was there were close 60 different filters and we didn’t want to statically define each filter within a Router snap. Instead we used H2 in-memory database to temporarily store and filter the data using SQL queries to generate each of the 60 Excel files then email them to different end-users. To use H2 with SnapLogic you need to use the Generic JDBC snap pack and you will need to download the H2 JAR from their website. Within the zip file under the bin folder there is a JAR file called something like h2-1.4.193.jar. When you setup the JDBC Account it will look something like this: You will need to upload that jar file to the Manager usually under the Global Shared and reference it within the JDBC Account you setup. JDBC Driver Class = org.h2.Driver JDBC URL = jdbc:h2:mem:snaplogic;DB_CLOSE_DELAY=-1;LOG=0;CACHE_SIZE=65536;UNDO_LOG=0;MAX_QUERY_TIMEOUT=0;AUTO_RECONNECT=TRUE;TRACE_LEVEL_FILE=0;LOCK_MODE=0 For more information on the JDBC URL settings refer to this website. I also created a short video recording stepping through how I used the H2 database to solve my customer problem which you can watch here: Video URL: https://vimeo.com/207395968 Video Password: snaplogic I’ve attached the pipelines I created in the video below: Unknown and Open Role Notifications (child)_2017_03_07.slp (23.8 KB) Unknown and Open Role Notifications (parent)_2017_03_07.slp (50.1 KB)Connecting to SQLMX
I am working on connecting to a SQLMX database using the JDBC connection. The connection is successful when I add the details in the JDBC account, but when I try to execute queries from the pipeline it throws error. I could run the same query using SQL workbench tool. See below the screenshots. Have anyone come across these errors? or if there is a known setting that is missed? Any help will be appreciated. *** ERROR[1183] Error 73 was returned by the file system on metadata table IFDB.DEFINITION_SCHEMA_VERSION_3500.OBJECTS (file name (partition \HPENS01.$S00X02.ZSD8KWJP.OBJECT00)). [2017-08-09 11:24:59] [SQL State=X0153, DB Errorcode=-1183] Next: *** ERROR[4082] Object IFDB.DEFAULT_SCHEMA.KRUPALI does not exist or is inaccessible. [2017-08-09 11:24:59] [SQL State=42000, DB Errorcode=-4082] Next: *** ERROR[8822] The statement was not prepared. [2017-08-09 11:24:59] [SQL State=X08MU, DB Errorcode=-8822] /KS
