Help with writing results from query

[dreamscapeuk]

Hi,



I'm fairly new to VBScript and I've bene trying really hard to pick it up over the last few weeks to get my project completed. Basically we load data from oracle to essbase and I wanted to create something to validate the data. I'm trying to pul data from Oracle and put it in an easy format that would be easy for VBScript to read when I later ask it to read values from 2 files and flag any differences.



I've managed to connect to Oracle and run the query but I am only getting it to write the account and balance for the first line. I've tried so many methods and am obviously doing something wrong. I'd prefer to get it into an array and then write it to file so I can create a couple of files. 1 with just account and balance and the other with the full report. I tried the array method bu tthen can't gget it to write to a file! Any help will be most appreciated. I'm trying to learn VB as I'm doing this so comments would be really useful

Here's my file so far:



dbType?=?"oracle"?????????????????'?Database?type
dbHost?=?"10.10.10.10"?????????????????'?Hostname?of?the?database?server
dbName?=?"ABCD"?????????????????'?Name?of?the?database/SID
dbUser?=?"xxxx"???????????????'?Name?of?the?user
dbPass?=?"XXXXX"???????????????'?Password?of?the?above-named?user


'*?Define?Constants?/?Variables
Const?SQLquery?=?"Select?sob.name?set_of_book,bal.period_name,
gcc.segment1?le,gcc.segment2?cc,gcc.segment3?acct,gcc.segment5?prod,gcc.segment6?mu,
gcc.segment7?ic,?
bal.currency_code,
to_char(SUM((bal.period_net_dr?-?bal.period_net_cr)))?period_balance?

FROM?GL.GL_BALANCES?bal,GL.GL_CODE_COMBINATIONS?gcc,?
GL.GL_SETS_OF_BOOKS?sob?

WHERE??bal.code_combination_id?=?1149165?
AND?bal.period_name?in?(to_char(add_months(sysdate,-1),'MON-YY'))
AND?gcc.summary_flag?=?'N'?AND????bal.set_of_books_id?=?sob.set_of_books_id?AND????bal.currency_code?=?sob.currency_code?AND????sob.name?=?'AZ_CON_USD'?AND????bal.actual_flag?=?'A'?GROUP??BY?sob.name,?
bal.period_name,?
gcc.segment1,?
gcc.segment2,?
gcc.segment3,?
gcc.segment5,?
gcc.segment6,?
gcc.segment7,?
bal.currency_code?HAVING?SUM((bal.PERIOD_NET_DR?-?bal.PERIOD_NET_CR))?!=?0"????????


set?Conn?=?CreateObject("ADODB.connection")
Dim?rows:?Set?rows?=?CreateObject("ADODB.Recordset")
Conn.ConnectionTimeout?=?30
Conn.CommandTimeout?=?30
Dim?connectionString:?ConnectionString?=?"Provider=OraOLEDB.Oracle;User?ID="?&?dbUser?&?";Password="?&?dbPass?&?";Data?Source="?&?dbName?&?";Persist?Security?Info=False"

'*?Create?objects
Dim?WshShell
Set?WshShell?=?CreateObject("WScript.Shell")

Dim?fso
Set?fso?=?CreateObject("Scripting.FileSystemObject")

'*?Create?log?file
Dim?LogFile
Set?ResultsFile?=?fso.CreateTextFile("Results.log",?true)


'*?Open?connection
Conn.Open?connectionString
WScript.Echo?"Retrieving?SQL?Data"

'*?Execute?SQL
rows.Open?SQLquery,?Conn,?0,?1??

????rows.MoveFirst
????????Do?Until?rows.EOF
????????????ResultsFile.WriteLine?rows.Fields("ACCT")?&?vbTab?&?rows.Fields("PERIOD_BALANCE")
????rows.MoveNext
??
Loop
????
????WScript.Echo?"Completed,?closing?connections"
????
'*?Close?log?file
LogFile.Close

'*?Close?connection?and?clean?up?objects
Conn.Close
Set?rows?=?Nothing
Set?Conn?=?Nothing
Set?WshShell?=?Nothing
Set?fso?=?Nothing
Set?LogFile?=?Nothing
Set?ResultsFile?=?Nothing



**************Many thanks***************

 

[tsuji]

You LogFile may all mean ResultsFile. Correct that part, in particular, the close line.
>LogFile.Close
[tt]ResultsFile.Close[/tt]

 

[tsuji]

Also I suppose the string SQLquery is properly formatted. It looks definitely spanned over multiple lines with line-breaks. If that is not an artifact in pasting, it is not properly formatted for vbs engine to read. (But, this is incompatible with what you said you have retrieved down, or at least the first row?!) If it is of mulitple line, it must use concatination and continuation sign and explicit line breaks, like this, figuratively.
[tt]
Const SQLquery ="Select sob.name set_of_book,bal.period_name, " & vbcrlf & _
"gcc.segment1 le,gcc.segment2 cc,gcc.segment3 acct, " & vbcrlf & _
"gcc.segment5 prod,gcc.segment6 mu, gcc.segment7 ic, " & vbcrlf & _
" etc etc..."
[/tt]
ps: you've 0xe28082 whitespace, again, I hope your platform-dependent render no problem for that. Otherwise, make sure you set space to 0x20. vbs engine has difficulty in handling utf-8 encoded script file for characters beyond 0x7f.

 

[dreamscapeuk]

Thanks tsuji. I actually formatted the sql code so it was easier for people to read it. Also took out the Logfile to shorten the code and focus on what I'm asking. Sorry for the confusion. The SQL query seems to work fine and appreciate your tip as I was putting it all on one line to make it work.

Not sure what you mean by this:
you've 0xe28082 whitespace, again, I hope your platform-dependent render no problem for that. Otherwise, make sure you set space to 0x20. vbs engine has difficulty in handling utf-8 encoded script file for characters beyond 0x7f.

Any help with getting th eresult so fth equery to the ResultFile?

Many thanks

 

[tsuji]

>I've managed to connect to Oracle and run the query but I am only getting it to write the account and balance for the first line.
What does it mean? I don't I made divertisement from tackling that as far as I understand. But then, what does it mean?

 

[dreamscapeuk]

Hey Tsuji,

By that I mean that I think the SQL query is working but the script is only writing te first line to the ResultsFile.

When I run the SQL query in SQL Developer I get 610,000 records returned. In the results file from this script I only get the first line but as I have selected to only display Account and Balance for each record:

ResultsFile.WriteLine?rows.Fields("ACCT")?&?vbTab?&?rows.Fields("PERIOD_BALANCE")


the result I get in my ResultsFile is:

500000 831.05

Yes only the first record then nothing else.

What I'm asking advice for here is to get all the records in this format (Account Balance) and then maybe also a full report with all the Fields in the table.

Hope that helps.

Thanks in advance.

 

[tsuji]

That was what I understand as well. I never come across cases where movenext does not move! Hence, the causes could most probably be that you did not close the text file handle after the loop. And that's what I drew your attention to and that's what the "edited" version shows in the post. Another possibility is that there embed control characters in the 2nd row that the fso fails to handle. But with accounting data, that is not very plausible.

Could you then check again that you've close ResultsFile? other than just LogFile?!

Since the binding and data retrieve seem on the march, I fear I cannot add much more other than the above.

 

[tsuji]

I forgot also to mention that you've to check, as well, the "exact" data (sub-)type of those two fields. vbs cannot handle some subtype without additional work.

 

[dreamscapeuk]

It's Ok I figured it out this morning.

ResultsFile.WriteLine rows("ACCT") & vbTab & rows("PERIOD_BALANCE")

For some reason when I use rows.Fields( it doesn't work properly, but taking fields away works like a charm.

Now I gotta figure out how to write the whole data to another file.

Cheers for taking the time Tsuji.

 

[tsuji]

Appreciate the feedback. (I am not convinced that makes a difference as far as the writeline goes. But let it be my issue and mine alone.)