Solved

Time parameter for dbisql batch file

Posted on 2004-08-13
9
895 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:rk_eliz
9 Comments
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 11795337
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11795621
>>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
 

Author Comment

by:rk_eliz
ID: 11795980
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 29

Expert Comment

by:leonstryker
ID: 11796167
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
 

Author Comment

by:rk_eliz
ID: 11811419
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11812842
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
 

Author Comment

by:rk_eliz
ID: 12058184
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12096720
PAQed, with points refunded (150)

modulo
Community Support Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Starting your own business is always a daunting process, and for most people it is brand new experience. Avoid the common pitfalls by following these tips to start on the road to success.
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question