B4J Question MS SQL -> Syspro ERP

[Johan Schoeman]

I am an absolute DUMMY when it comes to "networking". We are using Syspro and I am trying to connect to it via B4J code but just don't get it right (no problem via ODBC connection or making use of "Launch Power Query Editor" that is available under Excel -> Get Data -> Launch Power Query Editor.

I am connected to the company network (VPN) and my B4J connection code is as follows:

#AdditionalJar: mssql-jdbc-9.2.1.jre11

.....and.....

Dim SQL1 As SQL

.....and.....

SQL1.Initialize2("com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://;servername=xxx-sql-001;databaseName=Syspro_Live_XXX_X", "johans", "xxxxxxxxxx")

But no joy - keep on getting the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'johans'. ClientConnectionId:4eece5da-4676-434a-b682-80289365fc6d

Please give some advise on how to solve this issue.....browsed the forum and the web for hours but just do not find an answer to the error....

 

[EnriqueGonzalez]

hi!
this is incorrect:

jdbc:sqlserver://;servername=xxx-sql-001

it should be

$"jdbc:sqlserver://${host}:${port};databaseName=${database}"$

check that sql server has the tcp ip protocol open and check that a fixed port is working too.

 

[EnriqueGonzalez]

check this thread:

config.properties - MS Sql driver for JDBC

I am trying to change the tutorial code for mysql to MS Sql, but I have it wrong. This is my code: DriverClass=com.Drivermicrosoft.sqlserver.jdbc.SQLServerDriver JdbcUrl=jdbc:sqlserver://localhost/test?characterEncoding=utf8 I could not find the correct connection string elsewhere on the...

www.b4x.com

 

[Johan Schoeman]

Have tried this:

SQL1.Initialize2("com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://xxx-sql-001:1433;databaseName=Syspro_Live_XXX_X;", "johans", "xxxxxxxxxx")

...but no joy.

I am not in charge of IT (hosted off site) so not sure how to do this:

check that sql server has the tcp ip protocol open and check that a fixed port is working too.

Same error:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'johans'. ClientConnectionId:68164b2b-9141-41f7-afca-bbdb4e526ed7

 

[EnriqueGonzalez]

xxx-sql-001

you cant use the name of the server, you have to use an IP.

 

[EnriqueGonzalez]

check that sql server has the tcp ip protocol open and check that a fixed port is working too.

you have to do it (or ask for someone to do it this way:

config.properties - MS Sql driver for JDBC

I am trying to change the tutorial code for mysql to MS Sql, but I have it wrong. This is my code: DriverClass=com.Drivermicrosoft.sqlserver.jdbc.SQLServerDriver JdbcUrl=jdbc:sqlserver://localhost/test?characterEncoding=utf8 I could not find the correct connection string elsewhere on the...

www.b4x.com

 

[Jmu5667]

Login failed for user 'johans'

You should really check the login credential are correct. Looks like you have connected to the SQL server. You need to make sure that the user, johans, has login permissions for the database you are connected too.

 

[Johan Schoeman]

At long last got it working.....

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
#End Region

#AdditionalJar: jtds-1.3.1

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private xui As XUI
    Private Button1 As B4XView
    Dim SQL1 As SQL
    Dim res As ResultSet
    
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Layout1")
    MainForm.Show
    
    Dim connected As Boolean = False
    SQL1.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://xx.x.xxx.xx:1433/Syspro_Live_XXX_X;integratedSecurity=true;useNTLMv2=true;")
    connected = SQL1.IsInitialized
    
    Log("connected = " & connected)

    res = SQL1.ExecQuery("SELECT Customer FROM ArTrnDetail  Where ArTrnDetail.TrnYear = '2022' and ArTrnDetail.TrnMonth = '06';")
    Do While res.NextRow

        Dim datarow(2) As Object
        datarow(0) = res.GetString("Customer")
        Log(datarow(0))
        
    Loop

End Sub

Also had to find and copy ntlmauth.dll into folder C:\Windows\System32 so that it can connect by making use of Window authentication.

connected = true
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO005
CRO005
CRO005
CRO005
CRO005
CRO005
CRO006
CRO006
CRO006
CRO006
PEN001
PEN001
ISE001
DEL002
DEL002
DEL002
DEL002
FOR001
FOR001
FOR001
FOR001
FOR001
DEL001
DEL001
DEL001
DEL001
DEL001
ESK001
ESK001
EXI004
EXI004
MON0001
MON0001
MON0001
MON0001
PEN001
FRE002
FRE002
FRE002
FRE002
FRE002
FRE002
OMN002
OMN002
CRO005
CRO005
CRO005
CRO005
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO004
CRO006
CRO006
CRO006
CRO006
CRO006
CRO006
CRO006
OMN002
1MIC00
1MIC00
1MIC00
1PET01
1PET01
1PET01
1PET01
1PET01
FOR001
FRE007
FRE007
FRE007
FRE007
FRE007
FRE007
FRE007
FRE007
FRE007
FRE007
FRE007
ASTRA 
ASTRA 
TRA001
DECO001
EXI004
FRE003
FRE003
FRE003
FRE003
FRE003
FRE003
FRE004
FRE004
FRE004
FRE005
FRE005
FRE005
FRE005
FRE006
FRE006
FRE006
FRE006
FRE009
FRE009
FRE009
FRE010
FRE010
FRE010
FRE011
FRE011
FRE011
FRE011
FRE015
FRE015
FRE016
FRE016
FRE016
FRE016
FRE016
MON0001
PEN001
PEN001
TRA001
TRA001
TRA001
CRO004
CRO004
CRO005
CRO005
CRO006
CRO006
CRO006
CRO006
CRO006
1MIC00
DEL002
FOR001
DEL001
EXI003
EXI004
FRE002
FRE003
FRE005
FRE005
FRE005
FRE005
FRE006
FRE006
FRE007
FRE007
FRE007
FRE007
FRE009
FRE009
FRE009
FRE009
FRE009
FRE016
FRE016
HAS001
HAS001
ISE001
TRA001
TRA001
ASTRA 
CRO004
CRO004
CRO005
CRO005
1MIC00
DEL001
DEL001
DEL001
DEL001
FRE004
FRE004
FRE004
FRE009
FRE009
FRE010
FRE011
FRE011
FRE016
FRE016
BTE001
DEL002
DEL002
DEL002
FOR001
FOR001
LAN001
PEN001
PEN001
PEN001
PEN001
PEN001
FOR001
PEN001
CAS032
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
CRO005
CRO005
CRO005
CRO005
CRO005
CRO005
CRO004
CRO004
CRO006
CRO006
CRO006
CRO006
CRO006
ASTRA 
CAS032
CLO001
DEL002
DEL002
DEL002
FOR001
1MIC00
FRE003
FRE003
FRE003
FRE007
FRE007
FRE007
FRE007
HAS001
LAN001
CRO005
CRO005
CRO005
CRO005
CRO005
CRO005
CRO004
CRO004
CRO004
CRO004
CRO004
CRO006
CRO006
CRO006
TRA001
TRA001
TRA001
CAS024
DUR003
BFC001
CAS032
DEL001
DEL001
EXI004
FRE002
FRE003
FRE003
FRE003
FRE003
FRE004
FRE004
FRE004
FRE004
FRE007
FRE007
FRE010
FRE010
FRE010
FAR0001
FOR001
FRE003
PEN001
CRO005
CRO005
CRO005
CRO004
CRO006
CRO006
FOR001
FOR001
DEL002
DEL002
CAS024
FIR001
FIR001
FRE003
FRE016
FRE016
FRE016
MON0001
MON0001
MON0001
MON0001
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
PEN001
CRO005
CRO005
CRO005
SAS003
CLO001
FOR001
FOR001
FOR001
FOR001
OMN002
EXI004
FRE003
FRE003
FRE003
FRE003
CAS115
ASTRA 
CAS11 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
ASTRA 
CRO005
CRO005
CRO005
CRO006
CRO006
CRO006
ASTRA 
ASTRA 
ASTRA 
ASTRA 
DEL002
DEL002
DEL002
DEL002
DEL002
DEL002
FOR001
FOR001
FRE007
FRE007
FRE007
OMN002
ESK001
EXI004
FRE003
SAS003
PEN001
CRO004
CRO004
CRO004
CRO005
DEL001
DEL001
1DAM00
1DAM00
ASTRA 
ASTRA 
1PRI03
1PRI03
DEL002
FOR001
FOR001
FOR001
FRE007
FRE007
FRE007
FUT002
OMN002
SAS003
DUR003
DUR003
ESK001
FRE003
FRE004
CAS024
MON0001
MON0001
CRO004
CRO004
CRO006
CRO006
CRO006
CRO006
CRO006
CRO006
CRO006
PEN001
PEN001
PEN001
PEN001
1DAM00
BBW001
BBW001
FOR001
FUT002
SAS003
1PRI03
ASTRA 
DEL001
DEL001
ESK001
ESK001
FRE003
FRE003
FRE003
FRE004
FRE004
FRE004
FRE007
FRE007
FRE007
FRE007
FRE010
FRE010
FRE010
FRE010
FRE011
FRE011
CLO001
HAS001
CRO004
CRO006
CRO006
CRO005
1DAM00
CAS124
ESK001
ESK001
ESK002
FOO001
FOO001
FRE003
FRE015
FRE015
ISE001
1PRI03
DEL002
FOR001
SAS003
KAL001
CRO005
CRO005
CRO004
CRO004
CRO004
CRO004
CRO004