MAS 90 ProvideX System Time

Hello can any one help me use this function online help is very limited
I need to get the right syntax for this condition
Timecreated > {fn Curtime()}-1

Check if updated in the last hour that's why I try to subtract1 from the current time but it complains about the "-" if I remove then it complains about the ">"

I just need to see what has been updated in the last hour
FYI time in the table is in the format below and they are listed as Varchar(8) type in the database.. Just for reference
13.25289
13.25347
11.26208
11.26393
11.26612
11.26834
12.60732
12.60919
12.61063
12.61158
16.44512
7.91391
7.91456
7.91496

Used the Providex Manuel
http://filedb.experts-exchange.com/incoming/2009/05_w19/136758/ODBC4x.pdf


Select * from SO_SalesOrderHeader
where Timecreated < {fn Current_Time()}-1 /*Error Here*/
or 
Select * from SO_SalesOrderHeader
where Timecreated < {fn Curtime()}-1 /*Error Here*/

Open in new window

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BadotzCommented:
Not sure, but you can try this:


Select * from SO_SalesOrderHeader
where Timecreated < {fn Current_Time()-1} /*Error Here*/
or 
Select * from SO_SalesOrderHeader
where Timecreated < {fn Curtime()-1} /*Error Here*/

Open in new window

0
Leo TorresSQL DeveloperAuthor Commented:
Both syntax give me the same error

Server Msg: 1015, State: 37000, [ProvideX][ODBC Driver]
Expected lexical element not found: <identifier>

I included a picture of the error and the type as well. I think we are also going to need a type cast on TimeUpdated and if I do need it I dont know the syntax


Select * from SO_SalesOrderHeader
where TimeUpdated > {fn Current_Time()-1}
 
Select * from SO_SalesOrderHeader
where TimeUpdated > {fn Curtime()-1}

Open in new window

TimeQuery.JPG
0
rr_milesCommented:
First, CURTIME() returns a string. You need to get the value to do math. TIMEUPDATED is also a string field. The current time will never be less then the updated time, so reverse the variables, leaving the < sign the same direction.

You may find this PDF document for Providex OBDC helpful. It can be downloaded from www.pvx.com. I have attached it here for your convenience. This info is for MAS90 version 4.x, mostly it is for Net Admins. For DBAs, ProvideX supported SQL syntax begins on p.41.  Scalar functions begin on p. 44. They are grouped by type: String, Numeric and Date (p. 46). Some examples of using SQL scalar functions are on p. 48. Remember that each vendor can choose their level of SQL conformance, so SQL syntax may very between vendors (Microsoft,ProvideX,Oracle). This is a good reference to keep close at hand when dealing with MAS90/200.

ODBC4x.pdf
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Leo TorresSQL DeveloperAuthor Commented:
Excellent I did not know Curtime returned a string I thought it was a number. The manual you gave me did help but it did not say that so thanks for the heads up. Yes your right I do need to do math but i dont know how. Yes I do understand that CurTime() returns current time. My idea is to subtract 1 hour from CurTime() then compare it to TimeUpdated to return everthing upadted in the las hour. That is the reason I have TimeUpdated > {Fn CurTime()-1}. Thanks for your help...
0
rr_milesCommented:
The problem is there is no way to convert data types using the ProviedX processor. CAST and CONERT are Microsoft SQL extensions. If the MS processor executes before the ProvideX processor, then these extended functions may work for you.  If the SQL passes the line unaltered tp ProvideX, it will choke abd cause an error. If that does not work, there is no way, using those tools to make it work. It is a limitation of the tools. There ways using other tools like VBS.
0
rr_milesCommented:
PS. I do this kind of thing alot and dont do it from SQL.
0
Leo TorresSQL DeveloperAuthor Commented:
Wow I can see that your very knowledge. More so than some of the support I receive from MAS90.

I kinda figured the CAST & CONVERT function belonged to MS but I thought Providex would have some substitute.  OK but I did not understand when you said

"If the MS processor executes before the ProvideX processor, then these extended functions may work for you.  If the SQL passes the line unaltered tp ProvideX, it will choke abd cause an error. If that does not work, there is no way, using those tools to make it work. It is a limitation of the tools. There ways using other tools like VBS."

This may have been a little over my head..Can you explain in lamen terms what I have to do to test what your saying.. What i am trying to to is run this query every hour from the SISS SQL Server 2008 tool so it can grab the sales data for the past hour and store it in my SQL 2008 database.  May be this is not the way to do it. I am open you here any suggestions you have!!! Thank again for your help!!
0
rr_milesCommented:
When I do this, I use ADO from a VBS script. The problems you have run into is the reason why. Also, in SQL 2000 it was called DTS, In SQL 2005 it was changed to SISS. I dislike changing things due to vendor changes when upgrading the SQL product. OK, enough ranting.

"If the MS processor executes before the ProvideX processor..." means if MS SQL substitutes the variables before passing the line to ProvideX, it may work. Otherwise, the line will cause Providex to error as the functions do not exist in the ProvideX syntax.

Alternatives: I am not as good with SQL as I am with ADO and VBS. It may be possible to change the SQL. If you would like to see how I perform similar tasks using ADO and VBS email me at rr_miles@hotmail.com and I will send you some code.
0
Leo TorresSQL DeveloperAuthor Commented:
I was under the impression that the providex driver was its own entity and the functions had to exist in its dictionary in order to work Did not know there was a need for the MS processor to be involved in this process since it is going through the providex driver.

Greatly appreciate your help on a holiday!!
0
rr_milesCommented:
As I mentioned earlier, each vendor may choose their level of SQL conformance and may include extensions. The CAST and CONVERT functions do not exist in the ProvideX driver. MS SQL can preprocess commends. An example is @variable. I am not an expert with this syntax, but have seen it use in SQL code leading up a SELECT statement. This implies that there is preprocessing taking place.
0
Leo TorresSQL DeveloperAuthor Commented:
OK cool. But can you give me an idea of what this file is doing I do see that your connecting to the Mas90 data files for the company code my case 003 it also connects to my SQL server. Here are some snippets that i had questions on


set connectiontodatabase = createobject("ADODB.Connection")
***ADODB.Connection this should be my connection String Correct....

Then you have
wscript.echo "Deleting existing SQL data from " & table & " in database MAS_" & company
    sql_insert = "Delete from " & table
    Connectiontodatabase.execute(sql_insert)
*** I could erase this Correct?? I want to append data to this table


xcommand  = "Select * from " & table
    Set gl8 = m90.Execute(xcommand)  /* Here you Execute the statement

    ' add data to table   ********Are you adding data or adding fields to the table??***********
    wscript.echo "Inserting rows into SQL table "&table&" in database MAS_" & company

    ' SQL columns
    fields =          "Account,"
    fields = fields & "AccountDesc,"
    fields = fields & "FiscalYear,"
    fields = fields & "FiscalPeriod,"
    fields = fields & "BeginningBalance,"
    fields = fields & "DebitAmount,"
    fields = fields & "CreditAmount"


Then the loop at the end why loop  to enter data into table??
then at the end you delete with this line??
connectiontodatabase.execute(sql_insert)


0
rr_milesCommented:
set connectiontodatabase = createobject("ADODB.Connection")
***ADODB.Connection this should be my connection String Correct

Yes, use your connection string.
---------------------------------------------------------------------------------------
Then you have
wscript.echo "Deleting existing SQL data from " & table & " in database MAS_" & company
    sql_insert = "Delete from " & table
    Connectiontodatabase.execute(sql_insert)
*** I could erase this Correct?? I want to append data to this table

Yes, this code can be removed.
---------------------------------------------------------------------------------------
xcommand  = "Select * from " & table
    Set gl8 = m90.Execute(xcommand)  /* Here you Execute the statement

    ' add data to table   ********Are you adding data or adding fields to the table??***********
    wscript.echo "Inserting rows into SQL table "&table&" in database MAS_" & company

    ' SQL columns
    fields =          "Account,"
    fields = fields & "AccountDesc,"
    fields = fields & "FiscalYear,"
    fields = fields & "FiscalPeriod,"
    fields = fields & "BeginningBalance,"
    fields = fields & "DebitAmount,"
    fields = fields & "CreditAmount"

These are the fields that already exist in SQL The INSERT statement is built to insert the values
---------------------------------------------------------------------------------------
Why the loop?

This loop steps trough each record in the ADO record set. Notice above the DO WHILE statement there is a SELECT statement for GL8.

xcommand  = "Select * from " & table
Set gl8 = m90.Execute(xcommand)

At the top of the loop, you can test for the TIMEUPDATED field.
---------------------------------------------------------------------------------------

Then the loop at the end why loop  to enter data into table??
then at the end you delete with this line??
connectiontodatabase.execute(sql_insert)

This will actually put the data into SQL
0
Leo TorresSQL DeveloperAuthor Commented:
OK I'm starting to follow a little better..

*******
' get MAS90 data
wscript.echo "Using data from MAS90/200 company " & company
Set m90=CreateObject("ADODB.Connection")
m90.ConnectionTimeout=60
m90.Open mDSN

xcommand  = "Select * from " & table ****Why cant I just put my TimeUpdated query here to that way i will have less records to look at table AR_InvoiceHistortHeader has 90000 rows***
Set gl8 = m90.Execute(xcommand)
**********would that be OK


*******Why this I dont think i need this I am not linking tables right now.. (After this one I will though) is that what this is??
' get MAS90 data into local list
acctkey = gl8("AccountKey")  ' link to GL1

***********dont understand the syntax some have "&" and some dont. you have a replace, Why the syntax different from field to field
if not gl1.eof then
    ' data for SQL
    values =          "'"  & gl1("Account") & "'"
    values = values & ",'" & replace(gl1("AccountDesc"),"'","") & "'"

    values = values & ",'" & gl8("FiscalYear") & "'"
    values = values & ",'" & gl8("FiscalPeriod") & "'"
    values = values & ","  & gl8("BeginningBalance")
    values = values & ","  & gl8("DebitAmount")
    values = values & ","  & gl8("CreditAmount")



0
Leo TorresSQL DeveloperAuthor Commented:
Could it be a typo


' SQL columns
fields =          "Account," ****Did you mean AccountKey Here************
fields = fields & "AccountDesc,"
fields = fields & "FiscalYear,"
fields = fields & "FiscalPeriod,"
fields = fields & "BeginningBalance,"
fields = fields & "DebitAmount,"
fields = fields & "CreditAmount"



if so why do you need the link??
Once done I run this from the command line correct??
0
rr_milesCommented:
Message 1

' get MAS90 data
wscript.echo "Using data from MAS90/200 company " & company
Set m90=CreateObject("ADODB.Connection")
m90.ConnectionTimeout=60
m90.Open mDSN

RRM : Yes
----------------------------------------------------------------

xcommand  = "Select * from " & table ****Why cant I just put my TimeUpdated query here to that way i will have less records to look at table AR_InvoiceHistortHeader has 90000 rows***
Set gl8 = m90.Execute(xcommand)
**********would that be OK

RRM : Hold that thought, we will come back to it. A little setup is required first
----------------------------------------------------------------

*******Why this I dont think i need this I am not linking tables right now.. (After this one I will though) is that what this is??
' get MAS90 data into local list
acctkey = gl8("AccountKey")  ' link to GL1

RRM : Yes, this can be deleted in this case as you dont need relational linking..
----------------------------------------------------------------

***********dont understand the syntax some have "&" and some dont. you have a replace, Why the syntax different from field to field
if not gl1.eof then
    ' data for SQL
    values =          "'"  & gl1("Account") & "'"
    values = values & ",'" & replace(gl1("AccountDesc"),"'","") & "'"

    values = values & ",'" & gl8("FiscalYear") & "'"
    values = values & ",'" & gl8("FiscalPeriod") & "'"
    values = values & ","  & gl8("BeginningBalance")
    values = values & ","  & gl8("DebitAmount")
    values = values & ","  & gl8("CreditAmount")

RRM : Remove the IF/ENDIF statement in this case. Keep the lines beginning with VALUES.
----------------------------------------------------------------

Message 2

Could it be a typo

' SQL columns
fields =          "Account," ****Did you mean AccountKey Here************
fields = fields & "AccountDesc,"
fields = fields & "FiscalYear,"
fields = fields & "FiscalPeriod,"
fields = fields & "BeginningBalance,"
fields = fields & "DebitAmount,"
fields = fields & "CreditAmount"

if so why do you need the link??
Once done I run this from the command line correct??

RRM: No typo. In MAS90 level 3.71 and prior, GL files were keyed on the actual account number. When the GL module went to level 4, an account key field was added (along with others). The account number and account key do not always match. I dont want the account key cluttering up my SQL tables. I Just want the chart of accounts number.

Replace the FIELDS text strings with your SQL column names. Make sure the order of the FIELDS is the same order as the VALUES data.

Command line syntax: CSCRIPT  xxxxx.VBS
0
rr_milesCommented:
OK, returning to the SELECT statement that started the discussion. Here is some code showing the setup of date and time functions. This setup goes before the SELECT statement.

    today = now()

    wscript.echo today

    ' time
    hr = datepart("h", today)

    ' MAS90 time decimal
    min = ((datepart("n", today) * 60) + datepart("s", today)) / 3600

    my_time = left(hr + min, 9)

    wscript.echo my_time, hr, min

    ' date
    yr = datepart("yyyy", today)  ' year

    mon = right("0" & datepart("m", today),2)

    da = right("0" & datepart("d", today), 2)

    my_date = "{d '" & yr & "-" & mon & "-" & da & "'}"

    wscript.echo my_date

SO, we have  & WHERE timeupdated < my_time &.

You may perform math functions on the variables like;
    hr = datepart("h", today)  1    (1 hour ago)
or
    da = right("0" & datepart("d", today)+5, 2)    (5 days from today)
0
Leo TorresSQL DeveloperAuthor Commented:
I dont understand the thee Connection strings??

OK I included your time code just before the first select statment in order not to bring in so much data.
I then eliminated the Select statement inside the while loop. because the data is in gl8  

You had some value line with different syntax so i changed it a bit see if it makes sense.

Then theres the TimeUpdated its also a string need to be a number
can we do a cint(gl8(TimeUpdated)) before we start the loop??

I believe this is what you ad in mind for the query
   xcommand  = "Select * from " & table & " where cint(TimeUpdated) > " & hr
         Set gl8 = m90.Execute(xcommand)

sent you an email

  ' SQL connection String /* This is for SQL OK with this*/
    set connectiontodatabase = createobject("ODBC;Description=SQLMAS90;DRIVER=SQL Server;SERVER=SQLSRV01\MSSQLSERVER2;UID=ltorres;APP=Microsoft Data Access Components;WSID=GSWS038;DATABASE=SQLMAS90;Network=DBMSSOCN;QueryLog_On=Yes;Trusted_Connection=Yes")
    connectiontodatabase.connectiontimeout=60
 
 
    ' put some data into the table /* Dont Know??? why twice? looks like SQL again */ 
    set connectiontodatabase = createobject("ODBC;Description=SQLMAS90;DRIVER=SQL Server;SERVER=SQLSRV01\MSSQLSERVER2;UID=ltorres;APP=Microsoft Data Access Components;WSID=GSWS038;DATABASE=SQLMAS90;Network=DBMSSOCN;QueryLog_On=Yes;Trusted_Connection=Yes")
    connectiontodatabase.connectiontimeout=60
    sConnectstring="Provider=SQLOLEDB; " & svr & "; Database=MAS_" & company & "; " & uid & ";" & pwd
    Connectiontodatabase.open(sConnectstring)
 
/*This is for MAS90 OK with this*/    
    ' get MAS90 data  MAS90 Connection String
    wscript.echo "Using data from MAS90/200 company " & company
    Set m90=CreateObject("ODBC;DRIVER={MAS 90 4.0 ODBC Driver};UID=leo;Description=MAS 90 4.0 ODBC Driver;Directory=M:\MAS90;Prefix=M:\MAS90\SY\, M:\MAS90\==\;ViewDLL=M:\MAS90\HOME;Company=003;LogFile=\PVXODBC.LOG;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer")
    m90.ConnectionTimeout=60
    m90.Open mDSN

Open in new window

0
rr_milesCommented:
   ' MAS90 parameters
    company = "ABC"                                     not used here, can be deleted
    table = "GL_PeriodPostingHistory"          not used here, can be deleted
    mDSN = "DSN=SOTAMAS90"               may be changed

    ' SQL login, all are used
    svr = "server=(local)"
    uid = "UID=sa"
    pwd = "password="

    ' Setup SQL
    set connectiontodatabase = createobject("ADODB.Connection")
    connectiontodatabase.connectiontimeout=60
    sConnectstring="Provider=SQLOLEDB; " & svr & "; " & _
        "Database=MAS_" & company & "; " & uid & ";" & pwd
    Connectiontodatabase.open(sConnectstring)

    ' Setup and get MAS90 data
    Set m90=CreateObject("ADODB.Connection")
    m90.ConnectionTimeout=60
    m90.Open mDSN

     Setup date/time code
    ....

     SELECT statement
    ....
0
rr_milesCommented:
Final comments. I have included line numbers from your email file.

'  table = "AR_InvoiceHistoryHeader"
RRM: line 12, remove the apostrophe, you need the variable

where cint(TimeUpdated) < hr
RRM: line 58, left(TimeUpdated,2) < hr may work better

values =          ",'" & gl8("InvoiceNo") & "'"
RRM: line 223, remove the comma after the first double quote

sql_insert = "Insert into "
RRM: line 346, INSERT assumes the database is empty. Since you removed the DELETE statement you will need to use UPDATE. This presents other challenges. Now you must determine if a row exists in SQL before using UPDATE.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:
Great job!!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.