ascendix / salesforce-jdbc Goto Github PK

Hi,

I was very happy when I found this implementation of the SOQL JDBC driver for salesforce.
I use IntelliJ and it appeared that it is not compatible with this IDE, as most of the methods were not implemented.
Spent some time (around month) on improving it and finally I got this driver working for my favorite IDE!

My idea was to get more attention to this driver so posted my PRs as Hacktoberfest event. However the company looks like was not interested in this at all, as out of four of my PR's I got review on one, some tag addition for other two and the last one did not get any attention at all for almost a month.

I even created several support tickets to get attention to that - but no luck. No response from support, no response from developers.

So the only thing I can say - thank you Ascendix to taking my attention to this area - I will implement all the logic myself and will not try to bother you with future updates and new functionality

Is there a way to get a License added to this library?

Hi,
I call
databaseMetaData.getPrimaryKeys( schema.getCatalogName(), schema.getSchemaName(), table.getName())
then getShort(5) which based on documentation should be a short
https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String,%20java.lang.String,%20java.lang.String)

Still I get the error below.

Similar issue with md.getImportedKeys();
and rs.getShort(10) rs.getShort(11)

Please fix this, as this is used in DbSchema designer ( www.dbschema.com ).

class java.lang.Integer cannot be cast to class java.lang.Short (java.lang.Integer and java.lang.Short are in module java.base of loader 'bootstrap')
at com.ascendix.jdbc.salesforce.CachedResultSet.getShort(CachedResultSet.java:348)

public class SalesforceTest {

	public static void main(String[] args)  {
		try {
		Connection con = null;
		
		Class.forName("com.ascendix.jdbc.salesforce.ForceDriver");
		con = DriverManager.getConnection(
				"jdbc:ascendix:salesforce://[loginDomain=https://login.salesforce.com/services/oauth2/authorize;DatabaseName=SFORCE;[email protected];password=somepassword]");
		Statement pstmt = con.createStatement();
		
		ResultSet rs = pstmt.executeQuery("SELECT COUNT(*) FROM SFORCE.ACCOUNT");
		while (rs.next()) {
			System.out.println(rs.getString(1));
		}
		}catch(ClassNotFoundException cfe) {
			cfe.printStackTrace();
		}catch(SQLException se) {
			se.printStackTrace();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

I am using above class and the saleforce jdbc jar which is downloaded from here but I am getting the following error

java.sql.SQLException: [LoginFault [ApiFault  exceptionCode='INVALID_LOGIN'
 exceptionMessage='Invalid username, password, security token; or user locked out.'
 extendedErrorDetails='{[0]}'
]
]

	at com.ascendix.jdbc.salesforce.ForceDriver.connect(ForceDriver.java:53)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at SalesforceTest.main(SalesforceTest.java:14)
Caused by: [LoginFault [ApiFault  exceptionCode='INVALID_LOGIN'
 exceptionMessage='Invalid username, password, security token; or user locked out.'
 extendedErrorDetails='{[0]}'
]
]

	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at java.lang.Class.newInstance(Class.java:442)
	at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
	at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
	at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
	at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
	at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
	at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
	at com.sforce.soap.partner.PartnerConnection.login(PartnerConnection.java:818)
	at com.sforce.soap.partner.PartnerConnection.<init>(PartnerConnection.java:427)
	at com.sforce.soap.partner.Connector.newConnection(Connector.java:28)
	at com.ascendix.jdbc.salesforce.ForceConnection.getConnectionByUserCredential(ForceConnection.java:93)
	at com.ascendix.jdbc.salesforce.ForceConnection.getPartnerConnection(ForceConnection.java:51)
	at com.ascendix.jdbc.salesforce.ForceDatabaseMetaData.<init>(ForceDatabaseMetaData.java:28)
	at com.ascendix.jdbc.salesforce.ForceConnection.<init>(ForceConnection.java:38)
	at com.ascendix.jdbc.salesforce.ForceDriver.connect(ForceDriver.java:51)
	... 3 more

Any help would be greatly appreciates, and the same username is working when I am going through the login.salesforce.com.

Steps to reproduce:

1. Form a query that retrieves a multiple columns from a related object
2. Perform a query using ForcePreparedStatement executeQuery method
   Observed Result: Only one of the columns from the related object is included in result set. There is a mismatch between metadata colCount and the actual number of columnNames and values in their respective arrayLists in the returned ResultSet
   Expected Result:
   The ResultSet should include all expected columnNames and Values, and the number of columnNames and values from ResultSet should match the metadata colCount.

I have this query that returns results as expected when executed from Salesforce Workbench:

SELECT Id,Credit_Card_Address_1__c,Credit_Card_Address_2__c,Credit_Card_Expiration_Month__c,Credit_Card_Expiration_Year__c,Credit_Card_Holder_Name__c,Credit_Card_Type__c,Payment_Type__c,CreditCardLastName__c,CreditCardFirstName__c,Billing_Address__r.Apttus_Config2__Street__c,Billing_Address__r.Apttus_Config2__City__c,Billing_Address__r.Apttus_Config2__Country__c,Billing_Address__r.Apttus_Config2__PostalCode__c,Billing_Address__r.Apttus_Config2__State__c FROM Payment_Method__c WHERE Id = 'a0m53000000HRRSAA4'

The above query is getting a couple of columns from one table (Payment_Method__c), and is including a 5 columns from a related table (Billing_Address__r). When running this query through salesforce-jdbc, the result from ForcePreparedStatement executeQuery method is only including the first column from Billing_Address__r and seems to be dropping the remaining 4.

Looking at the raw data from PartnerService query method, it looks like SFDC is returning all the data from the related record, there is an SObject that includes all 5 of the expected fields from Billing_Address__r:

It appears that the child data from Billing_Address__r is getting dropped in the PartnerService toForceResultField method:

Could you share the instructions to build the jar file with dependencies?

According to the MySQL driver:

According to JDBC spec:
The driver should return "null" if it realizes it is the wrong kind of driver to connect to the given URL. This will be common, as when the
JDBC driver manager is asked to connect to a given URL it passes the URL to each loaded driver in turn.

Source: https://github.com/mysql/mysql-connector-j/blob/34cbc6bc61f72836e26327537a432d6db7c77de6/src/main/user-impl/java/com/mysql/cj/jdbc/NonRegisteringDriver.java#L188-L193

I have multiple drivers in my classpath but the root cause is lost because this driver throws an exception:

Unknown URL format "jdbc:sqlserver://localhost\foo:1433;database_name=bar"
  com.ascendix.jdbc.salesforce.ForceDriver.connect(ForceDriver.java:41)
  java.sql/java.sql.DriverManager.getConnection(Unknown Source)
  java.sql/java.sql.DriverManager.getConnection(Unknown Source)
  org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
  org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
  org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:196)
  org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:159)

Here the root cause is an authentication failure on the SQLServer database.

Connection is successful, can see all tables, however if I try to check available columns I see:
Can't read attributes For input string: "id"

If I try to select any column from any table, I receive no results....
Misconfiguration on my side?

Hi,

As per the details provided in below page
https://github.com/ascendix/mvnrepo

I am using salesforce-jdbc driver using mavne pom. But the details provided for repository is not working.
As the url "https://github.com/ascendix/mvnrepo/raw/master" is 404 page not found. This jar is very very important for my project. Can anyone please help me here.

com.ascendix.maven
Ascendix Maven Repo
https://github.com/ascendix/mvnrepo/raw/master

Spark version = 2.2
spark-shell --jars salesforce-jdbc-1.1-20180403.104727-1-single.jar
val jdbcurl="jdbc:ascendix:salesforce://;user=;password=;loginDomain="
val df=spark.read.format("jdbc").option("driver","com.ascendix.jdbc.salesforce.ForceDriver").option("url",jdbcurl).option("dbtable","select Name from account limit 10").load()

Error: ( I dont have an asterix in my soql )
java.sql.SQLException: org.mule.tools.soql.exception.SOQLParsingException: There was a SOQL parsing error close to '' (line:1,position:7).
at com.ascendix.jdbc.salesforce.ForcePreparedStatement.query(ForcePreparedStatement.java:123)
at com.ascendix.jdbc.salesforce.ForcePreparedStatement.executeQuery(ForcePreparedStatement.java:99)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:62)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.(JDBCRelation.scala:114)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:309)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
at org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:193)
... 50 elided
Caused by: org.mule.tools.soql.exception.SOQLParsingException: There was a SOQL parsing error close to '' (line:1,position:7).
at org.mule.tools.soql.SOQLParserHelper.createParsingRecognitionException(SOQLParserHelper.java:115)
at org.mule.tools.soql.SOQLParserHelper.createParsingException(SOQLParserHelper.java:90)
at org.mule.tools.soql.SOQLParserHelper.createSOQLParserTree(SOQLParserHelper.java:71)
at org.mule.tools.soql.SOQLParserHelper.createSOQLData(SOQLParserHelper.java:34)
at com.ascendix.jdbc.salesforce.SoqlQueryAnalyzer.getQueryData(SoqlQueryAnalyzer.java:191)
at com.ascendix.jdbc.salesforce.SoqlQueryAnalyzer.getFieldDefinitions(SoqlQueryAnalyzer.java:162)
at com.ascendix.jdbc.salesforce.ForcePreparedStatement.getFieldDefinitions(ForcePreparedStatement.java:264)
at com.ascendix.jdbc.salesforce.ForcePreparedStatement.query(ForcePreparedStatement.java:113)
... 58 more
Caused by: org.antlr.runtime.NoViableAltException
at org.mule.tools.soql.parser.SOQLParser.select_spec(SOQLParser.java:4853)
at org.mule.tools.soql.parser.SOQLParser.select_clause(SOQLParser.java:2961)
at org.mule.tools.soql.parser.SOQLParser.soql_query_(SOQLParser.java:2680)
at org.mule.tools.soql.parser.SOQLParser.soql_query(SOQLParser.java:2587)
at org.mule.tools.soql.SOQLParserHelper.createSOQLParserTree(SOQLParserHelper.java:56)
... 63 more

I see that calling connection.createStatement() returns null. Is this not implemented ? Is it possible to implement this as well ?

The com.sforce.ws library which this driver uses has built in methods for specifying and using a proxy server. This is key for large enterprise deployments. Could this be implemented in the driver?

Note the 'setProxy' method in the sforce library
https://github.com/forcedotcom/wsc/blob/b02fc05344064ba0e33c6d64768097815124c0d1/src/main/java/com/sforce/ws/ConnectorConfig.java

which could be used by the corresponding methods in the driver
https://github.com/ascendix/salesforce-jdbc/blob/86d2c19c096dfa9df4420af61c99ffd6cb12dc54/sf-jdbc-driver/src/main/java/com/ascendix/jdbc/salesforce/connection/ForceService.java