SQL Excel

Hiya Roland

That is very good you were able to get the integration going. Great you got that going.. Hopefully next week everything will be working perfectly

I am sorry but as I just started a new job, I won't be able to look at this before Saturday.

I look at this on Satuday though and expect to release v 1.4 on Sunday after testing.
The only change will be the addition of the API functions that you are using in DBAddin.
They should be done up as you described - using byref so you can easily get both back to Dbaddin.
Also, there should be no problems like you described with the "Get SQL" button.

Sorry for the delay very busy these days.
Speak to you later and have a nice week.
Al

Hi Roland

I hope you had a great weekend.

I am sorry but I was not able to look at the addin this weekend.. I was pretty sure I would get the time but I was exhausted from going back to work and have been resting all weekend..

Sorry, I did not get those fixes in. I will get them in pretty soon though.

Hope that is ok.. Have a nice week there !

Al

Hi Al!

Thanks, no problem. However, I'd appreciate a sooner than later fix, as I'd then publish version 1.2.0 of my DBAddin on sourceforge, which will have the final capability of this integration inside.

-regards,
Roland

Hi Roland
Will get it in pretty fast. Should be there by the end of the work week or at the latest the weekend.. Will do my best with the time..
Have a good day.
Al

Hi Roland

This is not done yet but I uploaded a new version where you should be able to get the connectionstring and sql statement back by ref.
The new file is here http://www.sqlexcel.net/dload/temp/

I added in a new function to get the last connetion string.

Test function from VBA was
Public Function GetLastConnectionStringFromSQLExcel()
Dim oSQLExcel As Object
Set oSQLExcel = CreateObject("SqlExcel.Connect" )
GetLastConnectionStringFromSQLExcel = oSQLExcel.SQLExcel_GetLastConnectionString()
Set oSQLExcel = Nothing
End Function


Test Function for the passing byreg of sql and connection string was
Sub TestModifiedFuncion()

sConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(LOCAL)"
sSQLStatement = "Select * From Categories"

Debug.Print "Original Connection String = " & sConnectionString
Debug.Print "Original SQL Statement = " & sSQLStatement

sSQLExcelStatement = GetSQLStatementFromSQLExcel(sConnectionString, sSQLStatement)

Debug.Print "New Connection String = " & sConnectionString
Debug.Print "New SQL Statement = " & sSQLStatement

End Sub



Sorry but this is not done yet.

I saw the same behaviour your described before - the "SQL Button" did not show when it should have - happened a few times but I do not know yet why and have no time to look tonight.

Also, the DLL will only work in Excel for now. I need several hours I think to get these two points sorted out. I am planning to do this on Friday/Saturday


Other point, is that the SQLExcel_LoadMainFormtoGetSQLStatement function is non standard now as it is returning parameters byref and also returning the SQL Statement as the return value for the function. I think this should be just retuning the boolean to indicate success or failure but cant change it now. Are you ok with this implementation ? True or False for function return and you get the refs for the connection string and SQL ?

Have to go now. If you have spare time it would be great if you could take a look.
Thanks and bye for now,
Al

Hi Al!

Great you could do it that quickly.
I've tested it and as it works, already built the new possibility into DBAddin (I also had to extend the DBFunction inserting feature, as multiline queries are now possible and I had a feature to enable multicell queries, i.e. DBFunctions that take their query from multiple excel cells, which makes parametrization and query building a lot easier...-> now that's not only possible qith cut/paste queries into Excel, but also from the new Query builder called SQL excel...)

There's one issue with Profiles vs. connection strings: As I'm sending in a connection string, SQL excel naturally doesn't know which profile to select. Now, if I'm in a different connection string than the default selected profile (the first in the list), SQL-Excel fails immediately with any pre-built query sent in that depends on the sent-in connection string, however is evaluated in a different connection string now.

This issue I think could be overcome in 3 ways:
1) (less end-user configurable): let the calling VBA/VB code pass a Profile name instead or along with the connection string, so SQL Excel can choose the right thing immediately. The drawback of this approach is that the mapping of connection string to profile name is done by the calling code and is not easily configurable as the connection strings tend to change in peculiar ways (more of that below). The only mapping that I could think of is a rather rigid/hardcoded one, based on parts of the connection string (e.g. database identifier..)

2) let sql-excel figure out the correct profile name from the connection string. That would be the best and transparent solution, however I've observed that SQL-Excel changes the connection strings from provider=SQLOLEDB;Server=OEBFASRV02;Trusted_Connection=Yes;Database=OEBFA;Packet Size=32767

to

Provider=SQLOLEDB.1;Extended Properties="server=OEBFASRV02;Trusted_Connection=Yes;database=OEBFA";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=KAPL;Use Encryption for Data=False;Tag with column collation when possible=False

I could resolve the problems associated with returning this different connection string (one of them a bizarre application error from excel where the application's statusbar couldn't be set ), BUT: still there is the problem of sending forth and back the connection string if it's not returned the way it's defined in the edit profile dialog.

-so there is one more thing to check into, all else is fine....
Roland

Thanks very much Roland for the clear explanation..

Have to be quick now as nearly bed time but number 2 looks the good way and SQL can get the profile name from the connection_settings.xml (there is still an issue here if the DB Addin user has a connection string which is not available in SQL Excel.) But we can fix the basic issue easily enough by reading the profile name from the XML file.

I will look at this point on the weekend and why the connection string changed which is strange..

For the other point about the application status bar.. Is that error coming from sql excel ? It is weird. If you are using the addin from a non Excel Office application, you will have a problem with the toolbar for sure (when you close the main form, the profile name dropdown list gets populated) but I dont think we use the statusbar object in the addin.. Pretty sure we don't use it..

Please let me know if this is coming from the SQL Excel side..

Thanks again for all the feedback.. Looking forward to trying out DB Addin !!
Al

hi Roland

I am going to try to put throught the changes over the weekend in bits and pieces.. I put through one fix there - where the "GET SQL" button was not showing consistently.. It was due to a missing ".visible" property in the form load event. It should be fine now.

Also, I changed the SQLExcel_LoadMainFormtoGetSQLStatement function to return a boolean value - indicating whether the function ran succesfully or not - and you can get the connetion string and sql statement from the input paratmers.

The new VBA test code is below and the new dll is loaded to the temp directoy.
Will be back later on the other issues - will do them as I get time today and tomorrow. thanks, al

Option Explicit

Public sConnectionString As String
Public sSQLStatement As String

Sub GetSQLStatementFromSQLExcelFromVBA()


sConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(LOCAL) "
sSQLStatement = "Select * From Categories"

Debug.Print "Original Connection String = " & sConnectionString
Debug.Print "Original SQL Statement = " & sSQLStatement

If GetSQLStatementFromSQLExcel(sConnectionString, sSQLStatement) = True Then
Debug.Print "New Connection String = " & sConnectionString
Debug.Print "New SQL Statement = " & sSQLStatement
Else
Debug.Print "GetSQLStatementFromSQLExcel returned False: Function Failed"
End If


End Sub


Public Function GetSQLStatementFromSQLExcel(ByRef sConnectionString As String, ByRef sSQLStatement As String)
'//The GetSQLStatementFromSQLExcel function will load the main SQL Excel form
'//the function takes 2 input parameters - Connection String, and SQL Statement which will be used to populate the form
'//the function will return a boolean value indicating whether or not the method has successfully returned back the
'original or modfied connection string and sql statement (both of these will be passed by back reference as your input parameters
'//The GetSQLStatementFromSQLExcel function will return the last SQL Statement that was used in SQL Excel
Dim oSQLExcel As Object
Set oSQLExcel = CreateObject("SqlExcel.Connect" )
GetSQLStatementFromSQLExcel = oSQLExcel.SQLExcel_LoadMainFormtoGetSQLStatement(sConnectionString, sSQLStatement)
Set oSQLExcel = Nothing
End Function

Public Function GetLastConnectionStringFromSQLExcel()
'//The GetLastConnectionStringFromSQLExcel function will return the last connection string used by SQL Excel
'//Takes no input parameters as returns a string
Dim oSQLExcel As Object
Set oSQLExcel = CreateObject("SqlExcel.Connect" )
GetLastConnectionStringFromSQLExcel = oSQLExcel.SQLExcel_GetLastConnectionString()
Set oSQLExcel = Nothing
End Function

Public Function GetLastSQLStatementFromSQLExcel()
'//The GetLastSQLStatementFromSQLExcel function will return the last SQL Statement used by SQL Excel
'//Takes no input parameters as returns a string
Dim oSQLExcel As Object
Set oSQLExcel = CreateObject("SqlExcel.Connect" )
GetLastSQLStatementFromSQLExcel = oSQLExcel.SQLExcel_GetLastSQLStatement()
Debug.Print GetLastSQLStatementFromSQLExcel
Set oSQLExcel = Nothing
End Function

Roland,

I took a look at the situation where the connection string was not returned not exactly the same as the one store in the sql excel settings.

It seems like the string will be the same if you specify all the connection properties within the string. But if you do not specify them all, then the defaults are taken for the connection and when you get the "LastConnectionString" it returns the actual connection that was used. This explains the difference.

I added an extra method though to work around this situation.
The GetConnectionStringByProfileName method will return the connection string that corresponds to the profile name from the connection_settings.xml file.
It takes the profile name as a string type input parameter and returns the conneciton string in string type.

Please take a look when you get a chance and see if this will work.
I am uploading the new DLL to the temp folder now.

Public Function GetConnectionStringByProfileName _
(ByVal sProfileName As String)
'//The GetLastSQLStatementFromSQLExcel function will
'//the connection string which matches the input profile name
'//Takes profile name as input
Dim oSQLExcel As Object
Set oSQLExcel = CreateObject("SqlExcel.Connect" )
GetConnectionStringByProfileName = _
oSQLExcel.SQLExcel_GetConnectionStringByProfileName(sProfileName)

Debug.Print GetConnectionStringByProfileName
Set oSQLExcel = Nothing
End Function

hi Roland

fyi, I made the change for the profile name being selected (based on the original connection string). I just uploaded the new DLL to the temp location. Please take a look when you have time and see if this works ok for you.

I think this leaves one open issue for you - the toolbal/addin not being accessible from other office applications - access or word etc.

I am going to post up a new thread on this subject as this thread is causing formatting issues for the forum for some reason. That is a seperate issue I need to fix one of these days..!)

For the addin being used from other office applicatins, I want to try to do the make the addin useful - in say word first (for example paste the query results to a word table instead of two an excel range..) - before adding in any public methods that can be used.
I will get the addin working in other office applications done pretty quickily but maybe not this weekend as I have alot of stuff to do later.

Please take a look through at the other items that were fixed today when you can and see if all looks ok now.
thanks,
al

Hi Al!

Sorry, but I think the version in http://www.sqlexcel.net/dload/temp/ is still the old version, there is no SQLExcel_GetConnectionStringByProfileName function and the SQLExcel_LoadMainFormtoGetSQLStatement function still returns a string (also the "GET SQL" button is still having problems...)

-regards,
Roland

Hi Al!

Sorry again, seems I can't download properly , the version was alright.

Hoever there's still the issue that neither SQLExcel_LoadMainFormtoGetSQLStatement nor SQLExcel_GetLastConnectionString return the "true" (stored) connection string, but rather the one used by ADO (or OLEDB, whichever you're using internally). SQLExcel_GetConnectionStringByProfileName DID return the right (stored) connection string however!

I've reconstructed the problem in VBA, by storing a connection string and retrieving the connection property and got the same behaviour as from SQL-EXcel:

QUOTE:

Sub testADOConnect()
Dim cnn As ADODB.Connection
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "provider=SQLOLEDB.1;server=OEBFASRV02;Trusted_Connection=Yes;Database=InfoDB"
Debug.Print cnn.ConnectionString
End Sub

Also, in the current version there is no way to use the "execute" button to test the passed statement as the form doesn't (can't ?) connect to a connection string.

I'd recommend that you
1) when a connection string is passed, compare it to the stored ones in the list of profiles (case insensitive, that's always a good idea !) and select/autoconnect to the found profile. In case no profile was found, issue a warning (might need an additional boolean parameter warnIfNoProfileFound in the SQLExcel_LoadMainFormtoGetSQLStatement function to optionally turn this on/off).
1) return the "true" store connection string when the user clicks the "GET SQL" button.

-best regards,
ROland

Hi Roland

Thanks for that. I will change it to just return the same ado connection string as used.
The initial implementation was incorrect for the "returnconnectionstring" and I didnt think of going straigt for the ado object.. No probs it is only a small change.

For the "Execute" issue, I am not sure what you mean. Will look at it later but the addin just implements the ado library..
Have to rush out. have a nice day. al

Hi Al!

The "execute" issue is the use case that a user should be able to just continue with his query that was passed over to SQL-excel, adding fields, tables, conditions, etc. and then immediately test the new query inside SQL-excel. Currently that's not possible as you have to select a profile, connect to that profile which invalidates (clears) the passed query.

Ideally the connection string passed should select a profile (if it doesn't exist, maybe issue a warning), connect to the connection string's info (admittedly that can be done without a profile) and then let the user continue working on his query!

I think I saw that behaviour in an earlier version of SQL-excel, however without the lookup of the profile name (it just connected and you could execute the query, however when you tried to add new tables/fields the addin gave an error message)!

-regards,
Roland

Thanks ! I got it.. Ok, we need to look into this again.

I think it is taking shape a bit even if there is a few more kinks to be worked out..

Bascially, this issue is the one where you don't automatically connect ?
That should be no problem.. Looking at the weekend again though !)
Sorry, these issues will all get resolved but going through a few extra versions in getting there..
Will be back on this and the point about the connection string..
Thanks. have a nice night. al