0

I have a macro in Excel to run .sql files connecting to Oracle to extract data. My problem is I have a very weird "bug" based on if the first line of the .sql file is a comment line.

If there are only the -- or the whole comment line is after the select, it will work, but if there is something after the -- on the first line, it won't work. (It is the same deal with /**/. Can't put something inbetween the /* and */ else it won't work).

Here are some example of .sql:

Example 1 (doesn't work):

-- test
select 
* 
from ABC.ABC_FIELD ABCField 
where what_ever = 15

Example 2 (works):

--
select 
* 
from ABC.ABC_FIELD ABCField 
where what_ever = 15

Example 3 (works):

select 
-- test
* 
from ABC.ABC_FIELD ABCField 
where what_ever = 15

Here are the code of the VBA that might be relevant. It seems to fail at the rs.Open line (the rs doesn't open at all), but I get no error. So the rs.Fields.Count is equal to 0, which make trying to read records after that a fail.

Public cnn As ADODB.Connection

...

Set cnn = New ADODB.Connection
        cnn.ConnectionString = "DSN=ABC_ORA;UID=" & UserId & ";PWD=" & Password & ";Persist Security Info=true"
cnn.Open

...

Dim myFile As String
Dim text As String
Dim textline As String

LaRequete = ""

myFile = Application.Worksheets(FeuilleParametres).Range("FICHIER").Value

Open myFile For Input As #1

Do Until EOF(1)
    Line Input #1, textline
    LaRequete = LaRequete & textline & " " & vbCrLf
Loop
Close #1

...

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open LaRequete, cnn.ConnectionString 

For iCols = 0 To rs.Fields.Count - 1
    resultatsSheet.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next

Here are the two errors I get in the ADODB.Connection. Sorry, the descriptions are in French.

Error 1
Native Error: 0 
Number : -2147217887
Source : "Microsoft OLE DB Provider for ODBC Drivers"
Description : "Une opération OLE-DB en plusieurs étapes a généré des erreurs. Vérifiez chaque valeur d'état OLE-DB disponible. Aucun travail n'a été effectué."

Error 2
Help Context : 1240640
Native Error : -2147217887
Number : -2147217887
Source : "ADODB.Connection"
Description : "Le fournisseur ne prend pas en charge cette propriété."

I hope I provided every needed information to solve this. Thanks.

Wildhorn
  • 926
  • 1
  • 11
  • 30
  • 1
    Do you get an error when opening the recordset? If Yes what is it? Have you checked the Connection errors collection for any clues? If you store the exact same SQL in a worksheet cell and read it from there do you see the same issue? – Tim Williams Jan 17 '20 at 17:20
  • If I store it in a worksheet cell and read it from there, I get the same issue. I do not get an error when I open the recordset. It just return no records and stay closed. I have checked the Connection errors. There are 2 of them I will edit initial post to add details about them – Wildhorn Jan 17 '20 at 18:11
  • Maybe try not using a DSN-based connection ? Eg: `Provider=OraOLEDB.Oracle;User Id=userHere;Password=passwordHere;Data Source=TNSEntryHere` – Tim Williams Jan 17 '20 at 18:32
  • What if simply replace `LaRequete = ""` with `LaRequete = vbNewLine`? – Akina Jan 17 '20 at 19:52
  • And I'd recommend do not iterate over lines, but use FilesystemObject for to read the SQL file, and use simply single line `LaRequete = vbNewLine & file.ReadAll`. – Akina Jan 17 '20 at 19:55

1 Answers1

0

Finally figured out that the problem is that ADODB.Recordset only accept query that start with a SELECT. If it start with anything else, it will consider it as a tablename or something like that.

So the solution is to nest it inside a "select * from (YOURQUERYHERE)".

Thanks to René Nyffenegger who answered it here Why can't I do a "with x as (...)" with ADODB and Oracle?

Wildhorn
  • 926
  • 1
  • 11
  • 30