Ӧ
Computer Applications
1999 19 7  Vol.19 No.7 1999



PowerBuilderOracleӦþ
šϣ

ժҪPowerBuilder 5.0Oracle 8.0ƳʱϵĲɵĲȫֵ֧Ӧñݵ롢ݴڼ¼ɾģԼTable֮ӵ֣̽˾
ؼʡPowerBuilderӦãݣݴڣ

Ϊͻ/PowerBuilderĹԣ̡ӻ¼̷ʽʽϵͳͼûȣǿϵͳPowerBuilder㡢ûӭĹ֮һPowerBuilderһŵЭʽԷκһõĺ̨ݿϵͳOracleSybaseInformixDB2ͬʱҲ԰࿪߼ɵPowerBuilderСڽҵMISϢϵͳĿУϵͳCLIENT/SERVERṹPowerBuilder 5.0ΪCLIENT˵ĿߡݿΪORACLE8.0 ENTERPRISEPowerBuilderOracleʱPowerBuilder 5.0Oracle 8.0֮ƳʱϵĲ죬ߵƳʱǰߣǰ߶Ժߵ֧ṩĲͳûȫֵ֧PowerBuilderOracle 8.0İڸʽݵȡڱӦоʹֵעĵط
1ݵ롢
PowerBuilderݴDataWindowṩ˴õĹܣӦдݿĽͨDataWindowɡΪԱ뿪ݿӦãPowerBuilderṩһǶʽSQLԱPowerBuilderűɵǶSQL һ˵ǶSQLΪ׼SQL伴ɡPowerBuilder 5.0Oracle 8.0ʱǶSQLֵעĵطPowerBuilderűַһ㲻ǵŻ˫ţOracleʱõSQLõַֻʹõš
PowerBuilder 5.0Oracle 8.0䵱ǶSQLҪʹõʱҪʹضת䣺
1 ݿʱ(date)ݣʹTOCHAR()TOCHAR()﷨ΪTOCHAR(date,Format)书Ǹ趨ڸʽFormatõOracleݿӦַ
2 дݿʱ(date)ݣʹTODATE()TODATE()﷨ΪTODATE(string,Format)书ǽһַתΪʽڣдOracleݿС
3 formatõڵڣһضڷſκıַһ𡣵ıַ˫formatʽΪ "The" dd "of" Month 
"in the year" YYYY 䣬Ƶȵȡ
ͬʱݿеʱݵʾĻʱӦעݿʱΪֵNULLʱ磬жһ༭EditMaskʽĵб༭em1ݿworkerbirthdayֶΣݣem1ʽΪYYYY/MM/DD(2)ᵽģáTOCHAR()birthdayֶδݿȡתlsbirthС
Select TOCHAR(birthday, yyyy/mm/dd)
Into :lsbirth
From worker
Where workerbh = :lsbh
Using sqlca;
ʱҪbirthdayݷŵem1УֻǼ򵥵дϣem1.text = lsbirthǲ׼ȷġݿbirthdayΪֵ(NULL)ʱȡִϢMismatch between retrieve columns and fetch columns.Ͳƥ䣩ȷıҪ϶ʱݵжϣ
If Isnull(lsbirth) Then
em1.text = "0000/00/00"
Else
Em1.text = lsbirth
End If
Ҫem1едصݿУͬҲҪȶǷΪֵжϣȻʹáTODATE()
If em1.text <> "0000/00/00" Then
em1.GetData(ldbirth) //ȡʱ
Else
SetNull(ldbirth)
End If
lsbirth = string( ldbirth, YYYY/MM/DD )
 
Update worker Set
Birthday = TODATE(:lsbirth, YYYY/MM/DD)
Where workerbh = :lsbh
Using sqlca;
⻹һЩֵעĵطOracleеʱ֡ıʾPowerBuilderͬPowerBuilderʱ֡루24Сʱƣhh:mm:ssʾOracleʱ֡루24Сʱƣhh24:mi:ssʾһڱдʱҪע⡣
2ݴм¼ɾ
PowerBuilderУϵͳʺͲݿDatabaseԷǳ㡢׵شʺάкݿṹһݿDatabaseṩȫͬļõĿӻʹǡPowerBuilder 5.0Oracle 8.0ʱOracleݿ͵ֶΣݿļ¼ɾĴʱݿϵͳϢʧܡԭPowerBuilderԶDatabaseеĲתΪ׼SQL͵ݿִУ3λĸʾ·ݣOracleⲻܡ磬רΪ˽һtexttextiddateֶͣΡеļ¼޸ʱϵͳ´Ϣ
ORA-01843: Ƿ·

No changes made to database.

Update "TEXT" Set "DATE" = TODATE
(4-Nov-1997 00:00:00,DD-MON-YYYY HH24:MI:SS) 
Where "TEXTID" = 1  And 
"DATE" = TODATE(4-Nov-1997 00:00:00,
DD-MON-YYYY HH24:MI:SS)
Կӡɾ¼ĲʱҲƴϢ
ڱӦʱݴڽвֶҲ⣬ݴύϢھӦУ߾̽⡣ݴڵdbError¼ȡԭִеSQL䣬תõµOracleɽܵSQL䣬Ȼơ̡Ĳ˵ťĽűУִµSQL伴ɡdbError¼ܽ²
* sqldbcode DBMSרеĴţ
* sqlerrtext DBMSרеĴϢ
* sqlsyntax ʱִеSQL䣻
* buffer ´ڵĻֵΪDelete!, Filter! Primary!
* row ´кš
ݴڵdbError¼űдɣ
If sqldbcode <> 0 Then// ݿִ
gsoldsql = SQLsyntax
// ȡԭSQL丳ֵȫֱgsoldsql
fchangesqldate()// SQLת
End If
Return 1// ʾϵͳϢ

fchangesqldate()ǶSQLתĺű£
long lltotal, llpos
string lssql
lssql = gsoldsql
gsnewsql = ""
Do
lltotal = len(lssql)
llpos = pos(lssql,TODATE(,1)
If llpos = 0 Then
gsnewsql = gsnewsql+lssql
Return
End If
llpos = pos(lssql,-,llpos)
gsnewsql = gsnewsql+left(lssql,llpos)
gsnewsql = gsnewsql+fchangemonth
( mid(lssql,llpos+1,3) )
gsnewsql =gsnewsql+mid(lssql,llpos+4, 16 ) 
+ "DD-MM-YYYY HH24:MI:SS)"
llpos = pos(lssql,),llpos)
lssql = right(lssql, lltotal-llpos )
Loop Until llpos = 0
fchangemonth()ǽ3λĸʾ·תΪ·ݵĺűͲоˡ gsnewsqlȫֱ澭תݿɽܵSQL䡣ơ̡Ĳ˵ťĽűбִµSQL伴ɡڴڵġ̡ťнб̵ģű£
Integer rtn
rtn = dw1.Update()
If rtn = 1 Then
Commit Using sqlca;
Else
Rollback Using sqlca;
Execute immediate :gsnewsql;
Commit Using sqlca;
dw1.Retrieve()
End If

3Table֮
ݿһԶĹϵĹϵߺĹϵһԶĹϵ(һ߿д౾飩/ԱϵһԶĹϵȵȣ֮ͿԽĹϵݿıӦУӦݿԣЧʹζݿ⣨Ӧó򣩽ʹûṩЧֵάݿԡһԡò/Աϵٸӣٶ鹹һ˾10ɣÿԱһŹϽ£ݿӦԱͲűУԱempidűdeptidԱӦһdeptidУָĳԱڸòŹΪ˰ݿ⣬ԱеdeptidӦָָűӦóͲҪΪȷĳԱָĲŴЧֵдԭݿԶ⣬ΪĳԱָĲŴЧֵʱݿϵͳϢĲ
PowerBuilderΪݿϵʱһOn Delete of Primary Table Rowѡ˵ебɾʱӱжӦпܵļֲɾеļ¼ԶɾӱежӦеļ¼ӱӦеĶӦΪNullĲ/Ա˵ԱڲűһűɾˣôейԱôأѡмַ
1 ֻҪлйԱͲɾţ
2 ԶɾԱdeptidֵָòŵйԱ¼
3 ڱɾŹĹԱڹԱнӦĲΪNull
PowerBuilderؿ(Sybase SQL Anywhere 5.0)ϵʱڡOn Delete of Primary Table Rowѡѡڶебɾʱӱ֮Ӧм¼ԶɾPowerBuilder 5.0Oracle 8.0ʱʹѡѡڶȴʵֲϹܡԱԼ̣ɾдӱӦм¼ɾеС
Delete From employee// ɾӱؼ¼
Where deptid = :lsdeptid
Using sqlca 
If sqlca.sqlcode < 0 Then
MessageBox ("Database Error", sqlca.SQLErrText,
Exclamation!)
Return
End If
Delete From department// ɾ¼
Where deptid = :lsdeptid
Using sqlca 
If sqlca.sqlcode < 0 Then
MessageBox ("Database Error", sqlca.SQLErrText,
Exclamation!)
Return
End If

4
PowerBuilderĿԹڶ㹦֮һ벻ͬĺ̨ݿʱԴһЩϸ΢ĲھӦУҪעֺʹáPowerBuilder 5.0Oracle 8.0ʱгֵһЩ⣬ӵTableļ¼ɾ⣬PowerBuilderOracle 8.0İʽɵݼݴڵĲȣ֣̽˱ʽĽ⣬PowerBuilderܶȡOracle 8.0еıĿ¼Oracle 8.0еıṹ⣬ƪĲϸܡ
šʦҪоҵMISϵͳݿ⼼
ߵλšϣ٣װָӼѧԺ101416
ο
1ݡJohn Adolph PalinskiOracle8 ݿ⹹칤ʵָϣеҵ磬1998
2ݡBill HatfieldPowerBuilder 5 Ӧó򿪷ָϣ廪ѧ磬1997
ո:1999-01-08
