B4J Question [Web App] MS SqlServer App

[Bachrock]

Hi with the SQL Tutorial and the MySQL example I was successful in making a connection to a remote SqlServer Database that resides on a remote Web Hosting Site!! So Exciting. Below is the Code in the Main Module. The table gets created properly. So I know I am connected.... Half the battle. I used the SQL Server JDBC driver downloaded from Microsoft. Additional questions follow code.

'Non-UI application (console / server application)
#Region Project Attributes
#CommandLineArgs:
#MergeLibraries: True
#AdditionalJar: sqljdbc4.jar
#End Region

Sub Process_Globals
Public srvr As Server
Private sql1 As SQL
End Sub

Sub AppStart (Args() As String)
srvr.Initialize("srvr")
srvr.AddWebSocket("/ws", "HelloWorld")
srvr.Port = 51042
srvr.Start
sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://mssql502.ixwebhosting.com;DataBaseName=****;user=*****;password=****")
sql1.ExecNonQuery("CREATE TABLE Test1 (col1 TEXT, col2 INTEGER, col3 INTEGER)")

StartMessageLoop

End Sub

So I based this on the simple HelloWorldWebApp. I have the complete project also attached.

My new questions now are these. What I want to do is add 3 simple text boxes to the Index.html file and also a button. When I click on the button I want to execute a query that inserts the data from the text boxes into the table that was created. I am not sure how to make the sql1 Variable Global so that it can be accessed in the HelloWorld Module. It is not recognized as I currently have it. I tried to move the sql1 variable declaration and create query command to the HelloWorld module but it did not work there.

Please give me some guidance on what to do next. I feel I am very close. Thank you....

 

[billzhan]

'in main module
Private sql1 As SQL
'change to
Public sql1 As SQL

'in handler use
main.sql1

If you have high concurrent queries, it's better to use ConnectionPool to manage. See Datebase Connections Pooling section.

You can find example of a mysql db(handler,not websocket) here.

A simple demo for text input attached.

 

[Bachrock]

billzhan, thank you very much for the reply. I tried to open your example you sent back to me and I get an error that says it was created with a newer version of software and I must update in order to open. I am running B4J 2.00. I am not aware of a newer version and did a quick search and I am not finding a newer version.

Thank you so much for the feedback. With what you have given me I should be able to finish it up. For this project there are only a couple of users that will use it a few times a day so high concurrent queries are unlikely. I will look into the connection pooling however.

Thanks again.

 

[Erel]

Here: B4J v2.20 BETA is available for download

 

[Bachrock]

Thanks Erel

 

[Bachrock]

Hi Erel or Billzan, any further information or examples regarding connection pools would be greatly appreciated. Thank you,

 

[Erel]

Using connections pool is very simple.
In your Main module create a public global variable:

Sub Process_Globals
 Public pool As ConnectionPool
End Sub

Initialize it in AppStart exactly like you initialize the SQL object:

pool.Initialize(driverClass, JdbcUrl, dbuser, dbpassword)

Now you can get a SQL connection from any module you like with:

Dim sql1 As SQL = Main.pool.GetConnection
'work with sql1...
...
sql1.Close '<-- very important. Releases the connection and returns it to the pool

 

[Bachrock]

Thanks Erel...