Time parameter for dbisql batch file

I currently have a VB6 ADO program extracting data from an ASA5.0 db and find that it only works partially against an ASA6.0 version of the same DB (nt4 sytem), I keep getting a E-Fail status msg on an existing table with data.  

Iam new to dbisql but find that I  can export the data via dbisql and all is well.  So I'm building a batch file to do so but need to pass in a date parameter.  I can use the system date or a specific date from one of the db tables (would prefer to use the latest date in the table).

proposed code for one table in batch file

select A.maj_grp_seq, A.obj_num, A.cat, A.name, B.business_date, B.sls_cnt, B.sls_ttl, B.rtn_cnt, B.rtn_ttl, B.dsc_ttl FROM micros.maj_grp_def A, micros.dly_sys_maj_grp_ttl B WHERE A.maj_grp_seq = B.maj_grp_seq AND business_date = {date};

command line
READ batch.sql '#08/13/2004#'

I am unclear on how to set the date as a variable in the batch file for dbisql either from the system or table, is this run as a dos batch file? could then  set an environment variable = time.
rk_elizAsked:
Who is Participating?
 
moduloConnect With a Mentor Commented:
PAQed, with points refunded (150)

modulo
Community Support Moderator
0
 
Jan FranekCommented:
I'm afraid, that you have to use some text manipulation programs to create sql batch with filled in date. You need something, that takes that date and creates sql batch using this date. Then you can run dbisql with that batch.

BTW, don't use # to indicate date - just '08/13/2004'.
0
 
leonstrykerCommented:
>>I currently have a VB6 ADO program extracting data from an ASA5.0 db and find that it only works partially against an ASA6.0 version of the same DB (nt4 sytem), I keep getting a E-Fail status msg on an existing table with data.  

What the connection string you are using?  Can you show some of the code?  I may be able to fix it.

Leon
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
rk_elizAuthor Commented:
the code is as follows:
Public Sub Table5()
    Dim p As String
    r = "Time_card_export"
    l = r
    sSQL = "select * FROM micros.v_R_employee_time_card;"
    Set m_oRecordset = New ADODB.Recordset
    m_oRecordset.Open sSQL, oConnection1, adOpenStatic, _
                        adLockBatchOptimistic, adCmdText
    m_oRecordset.Filter = "business_date >= " & j
    Set RecB = m_oRecordset
    Call RecordSetProcess(RecB, 5, r)
End Sub
the filter is using a previously set date, referenced in my original question.  Also note that this is dumping a view not a table.  within dbisql not a problem, also not in asa5.0.  This is the code used to connect with asa5.0
0
 
leonstrykerCommented:
I would need to see oConnection1 connection string.

BTW, why can't you use:

sSQL = "select * FROM micros.v_R_employee_time_card  WHERE business_date >= " & j

instead of using the Filter?

>>Also note that this is dumping a view not a table.  within dbisql not a problem, also not in asa5.0.
 
It should not matter.  Did you change you connection string then you switch from ASA5.0 to ASA6.0?

Leon
0
 
rk_elizAuthor Commented:
the connection string is:
    m_sConnStr = "Provider=MSDASQL.1;Password=custom;User ID=custom;Data Source=micros"
    Set oConnection1 = New ADODB.Connection
    oConnection1.CursorLocation = adUseClient
    oConnection1.Open m_sConnStr
---------------
tried making the date as part of the where clause but either the connection or provider doesn't respond to it but will respond to the filter.  would much rather do it as a where clause.
---------------------
no change in the connection string from asa5.0 to asa6.0

Richard
0
 
leonstrykerCommented:
Richard,

Have you tried using the ASA OLEDB provider?  You are usually better of with native drivers than the MS ones.

Try this:

m_sConnStr = "Provider=ASAProv; Data source=micros"

Leon
0
 
rk_elizAuthor Commented:
none of the above suggestions helped in the resolution, what I did do was a call to the sybase utility dbisql.exe to export the table and massaged it from there.  Thanks. Richard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.